Excel Template To Access Conversion / Storage

Apr 30, 2013

What I have now is an excel template (with ~12 worksheets) that many regional offices use to enter in some lease data, from which the excel sheet creates a rental schedule and does a whole ton of calculations on that data. Some are NPV calculations, some are yields etc etc.Eventually, I would like to:

1) Enable users to fill out one of these templates, and save the data to the database (Just the inputs? All the data? My reading suggests just the inputs)
2) Use the database to produce one of these templates for any lease in our system (shouldn't be hard, from what I've read)
3) Sum up calculations from this template for many records (eg. if a tenant has many leases, what is the NPV of all of those leases, or what is the total NPV for all tenants)

I have the inputs (from Access) I will have no problem using them in the excel version, but does it make sense to use Access given that I may need to somehow be switching back and forth to get the info I want for my various reports? I am very comfortable writing macros in VBA for excel, so if that's the solution, that is no problem. I assume what I need for #1 and #2 is a macro to arrange the inputs from the excel sheet into a format that can easily be dropped into access tables and vice versa.

View Replies


ADVERTISEMENT

Excel Template To Import Into Access

Feb 5, 2006

Hello All,

I thought i would ask this question an how to import data from excel into access.

I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.

The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.

The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.

Any thoughts

Alastair

UPDATE
**************************************Resolved Thanks to Pat Hartman *************************

View 2 Replies View Related

Export Access To A Copy Of An Excel Template

Oct 11, 2005

Hello, I am here back in the forum because would like some help to define a code.

Meloncolly has already helped me but I think that I have mixed up two pieces of code and cannot find the solution.

I need to export my access data to a document in excel.
I will select the recorset of the data to export, using a combo box on a form named MENU.

The excel file is a template named MASTER. Before the recorset is copied, I need to make a copy of the template via code. The copy will be the document into which copy the data. Once the data is copies, I would like an input box asking users if they would like to save the new excel file and be able to name it with the name they will type in the input box.

My last problem is that the database is used by 20 users and what happens if are all exporting to the template? Will this automatically name itselft, MASTER1, MASTER 2, etc?

This is the code that I am using and tried to put together. It does copy the data into master 1 but leaves the template opened. It also tells me that there is something missing.

THanks:

Private Sub ExportToExcel_Click()
Dim myid
Dim obj As Object
Set obj = GetObject("C: estMaster.xls")
'Dim mypath

obj.Application.Visible = True
obj.Windows(1).Visible = True
obj.Application.ScreenUpdating = False


myid = Me.[MyCombo]
'grab the three field values from the table
Dim mySSN, myFirstname, myLname
mySSN = DLookup("[WESSN]", "[MASTER]", "[id]=" & myid)
myFirstname = DLookup("[WEFN]", "[MASTER]", "[ID]=" & myid)
myLname = DLookup("[WELN]", "[MASTER]", "[ID]=" & myid)

'open excel and the required file
Dim appXL3 As Excel.Application
Dim blnStartXL3 As Boolean

On Error Resume Next
' Check if Excel is already running
Set appXL3 = GetObject(, "Excel.Application")
If appXL3 Is Nothing Then
blnStartXL3 = True
'Else
' We have to start Excel ourselves
Set appXL3 = CreateObject("Excel.Application")
If appXL3 Is Nothing Then
MsgBox "Can't start Excel", vbExclamation
GoTo exit_handler
End If
End If
With appXL3
'.Visible = True
On Error GoTo Err_Handler

'open the excel file

'mypath = "C: estMaster.xls"
'.Workbooks.Open mypath
.ActiveWorkbook.SaveAs "c: estMaster1.xls"
.Sheets("Data").Select
'enter variable values into cells
.Range("B6") = mySSN
.Range("B3") = myFirstname
.Range("B5") = myLname

'do some other stuff

'save the workbook
.ActiveWorkbook.Save
'close it too
.ActiveWorkbook.Close
.ActiveWorkbook.Close

'exit and tidy up
exit_handler:
On Error Resume Next

If blnStartXL3 = True Then
'We must quit Excel
appXL3.Quit
End If
Set appXL3 = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation
DoCmd.SetWarnings True
Resume exit_handler
MsgBox "The tables have been successfully exported to " & txtExportFile & "."

Exit Sub
End With

End Sub

View 3 Replies View Related

Having Trouble Woth Type Conversion When Importing From Excel To Access

Feb 16, 2005

Hello,
I have getting type conversion errors for numeric and currency fields when importing an Ecxel spreadsheet into a Table in Access. I have tried changing the data type to text in Excel, and that is what it says I have done when I examine the field formats in question. How can I force this to be text, it keeps reverting,
i.e. the fields in question, to numeric and currency. Can anyone help? Thank you.
Joe

View 4 Replies View Related

Reports :: Excel Template File - Direct Access What To Put In Which Cell?

Nov 24, 2014

How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.

Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?

View 14 Replies View Related

Modules & VBA :: Copy Access Data Into Excel Template In Read And Write Mode

Jun 20, 2015

How to open a pre filled excel template in read and write mode from msaccess vba and insert data from msaccess tables into specific columns in excel.

Rename the tabs in excel sheet based on a specific column in the access data.

Each row in the table will go to a seperate tab in excel.

Save the excel template after populating the necessary data into different tabs.

View 3 Replies View Related

Push Data From Current Record In Form Into A New Excel File, Using Excel Template

Sep 10, 2007

I searched the archive and didn't find quite what I was looking for, so..

I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.

I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.

Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!

A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.

I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.

Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject

View 1 Replies View Related

What Is Access's Storage Capacity?

Mar 2, 2005

I'm new to Access and was wondering what the storage capacity was for a table.

I am presently working with a table that's approx. 70 columns and about 6,000 rows (and growing). Does anyone know at what point I'll be hitting a wall? It's already too big for me to import into Excel (which is another problem).

Thanks

View 2 Replies View Related

Access Storage Capacity

Oct 26, 2005

hi,

What's the total storage capacity of Access Database

how many records can be saved at Max

how many Tables Can be Created at Max

any idea abt This

thanks for helping

View 3 Replies View Related

Looking For Example Of Exporting Query Results To Excel Template

Jan 27, 2008

Hi all,

I'm not very experienced with Access. I'm looking for a very simple sample of how to export Query results to an Excel Template document. Especially to specific cell references. Would anyone have one to share. I'm trying to get my head around the concept first.

I may have to use Access 2000 and later versions to run the database, so do I have to cater for both DAO and ADO?

Thanks in advance for any help
rgs
Ginny

View 2 Replies View Related

Modules & VBA :: How To Auto Fill Excel Template

Jun 19, 2013

I have info on my access form which is located here....

Code : Forms![Front Page]![Site 2 Owner]

and here

Code : Forms![Front Page]![Postcode S2]

I would like to export this information into cells B2 & C2( individually and respectively) on the excel spreadsheet which I have saved as a template here...

Code : C:UsersmedesktopAutoExcel Auto

Is this easily achievable? To be honest I will be using it to fill in about 12 cells but how it would be done for the first two i can just modify it as necessary.

View 6 Replies View Related

Modules & VBA :: Paste Query To Excel Template

Jul 20, 2014

I created a form with lots of conditional formatting that did pretty much everything I wanted it to do. The only problem is that it takes about 4 full minutes for the form to open.

As an experiment I am reluctantly now trying to display the results in Excel. I have created a template xls sheet and all I want to do is, on the press of a button, copy the results from my query and paste them in to cell a1 of my spreadsheet.

I found the following code online which I am trying to adapt.

Code:
Private Sub update_tracker_Click()
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim qdfResults As QueryDef
Dim rsResults As Recordset
'Set up refernce to the query to export

[Code] ....

My limited knowledge however results in a couple of errors.

The first error User defined type not defined error appears at the very first line of my code

Code:
Dim XL As Excel.Application

The next error occurs as I am not sure how to reference the query to export. The query is called 2014 Resources and outputs also to a form called 2014 Resources.

View 11 Replies View Related

General :: Copy Query To Excel Template?

Jun 18, 2014

i have an excel spreadsheet in my documents (H:My Documentsbreakdown.xls)

i have a query called Qry_Breakdown, a form with a button named "Update Breakdown"

How do i get the details created in the query, to appear in my breakdown sheet, starting from "A2" as i have several headers matching the query

View 9 Replies View Related

Queries :: Creating Access Storage Database

Feb 5, 2014

i am busy with creating a access storage database and need to calculate the number of days a vehicle is in storage, i have a [date in] field and a [date out] field. i need to calculate if [date out] is empty to use today otherwise [date out] - [date in]

View 5 Replies View Related

How To Prevent Datatype Conversion When Importing Tables From Excel????

Feb 23, 2005

I'm building a database at work to see if we can replace some of the "buhzillion" spreadsheets we currently use to track data on sites and employees across the country. I've set up the primary excel tables the way I want to import them, but Access keeps converting my Employee Numbers (mostly 7 digit numbers, "text" format in excel) to scientific notation during the import process and then giving me errors because my primary key "EmployeeNumber" has duplicates. How do I tell Access to import these as text instead of numbers? I'd really like to not have to type in data for 100+ employees and over 1000 sites, you know?

Thanks in advance.

View 10 Replies View Related

Modules & VBA :: Exporting Data To Excel Template But Saves Under Different Name

Apr 29, 2014

I am able to use DoCmd.TransferSpreadsheet to export data from Access to Excel, however, I want to be able transfer data into a specific sheet within an Excel template (e.g. Tasking.xls), that will then save under a different name (e.g. Tasking 20140429.xls). The other sheets within the Excel template contain pivots etc. so they will need updating during this process. The Excel template should just close down and remain in its original format.

This process will occur once a week so the dates will have to change accordingly.

View 7 Replies View Related

Modules & VBA :: Exporting To Excel Template And Saving As New File

Jun 25, 2014

Any definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.

I've tried to remove any confidential info from the code below so it's not exactly the same.

Code:
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
Dim strFolder as String
strFolder = ("C:Profiles"& [Name] & "")

[Code] ....

The error seems to be with the SQL statement although that may just be the first error that it got to. I read that you can't refer to a Query if it has a criteria and that you have to write the SQL directly into the code.

View 13 Replies View Related

Modules & VBA :: UDF In Excel Template - Turning Letters Into Numbers

Jun 12, 2015

I have this UDF in my excel template that changes a set of numbers in to letters corresponding a code.

For example the code is "EUCHARISTO" it would simply mean

E=1, U=2, C=3, H=4, A=5, R=6, I=7, S=8, T=9, O=0 and by default the tenths position (".0")=X the hundredths position (.0"0") = Y and If a number repeats it becomes G.

Examples
12.50 = EUAY
123.00 = EUCXY
12.25 = EUGA
99.00 = TGXY
99.50 = TGAY
999.00 = TGTXY
999.99= TGTGT

Here's the UDF:

Code:

Function LetterCode(ByVal Numbers As String, Letters As String) As String
Dim X As Long
Numbers = Format(Numbers, "0.00") * 100
Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1))
If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"

[Code] ...

Also, where best to put this code, In a query or in a module.

View 6 Replies View Related

Modules & VBA :: Export And Import Particular Cells From Excel Template

Jul 20, 2015

I have a few problems and I want them to be able to be done from switchboard:

1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?

2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.

View 1 Replies View Related

Modules & VBA :: Export To Specific Sheet In Template Excel File

Feb 11, 2015

So I press a button on my Form1 and my tbl_customers table is exported onto a specific sheet in a templated Excel file "customer-template" that I have created.

This file has formulas on another sheet that based on the imported data.

The file is then saved to a specific location C:AccessCustomersHistory with the file name based on a date that was criteria from my original form E.g. "customers 11-02-15"

View 3 Replies View Related

Modules & VBA :: Filling Excel Template Cells / Save Then Send On Outlook

Jul 13, 2013

I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.

The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(

The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.

Code:

Private Sub Command154_Click()
On Error Resume Next
Dim appExcel As Excel.Application
Dim wbook As Excel.Workbook
Dim wsheet As Excel.Worksheet
Set appExcel = New Excel.Application

[Code] .....

View 4 Replies View Related

Modules & VBA :: Modify Working Code - Export Query And Update Worksheets In Excel Template

Mar 12, 2014

What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.

I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"

What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:

Code:

Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String

[code]...

View 3 Replies View Related

Access To VB Conversion

Mar 24, 2006

Hi,

I have been tasked with updating an Access 97 database with several forms that would take me a good while to recreate.

I wondering if there is a way to convert them from 97 to either VB6 of VB.NET that is free of charge to me. All the solutions I have come across thus far cost money, and it is not possible for me to do this.

I have tried exporting the forms to an external file but it won't let me save them as a file type that my VB6 installation can open.

Cheers
Murray

View 3 Replies View Related

Access To VB Conversion

Feb 11, 2007

Considering the difficulties in packaging Access software, I am wondering about the feasibility of converting my Access project to VB and distributing that instead. Has anyone done this and if so how difficult would it be. Does it mean a complete rewrite or can I use existing forms, reports and coding etc.

View 12 Replies View Related

Access 97 To XP Conversion

Oct 14, 2004

I have an Access 97 database that I’ve converted to Access XP. The font end database is on each user’s computer and the backend is on a network server. The database is updated with data from a mainframe db2 databases. I have 87 queries that pull the data from the db2 data base and import the data into an Access database that resides on a net work server. All these 87 queries are append and update queries that clean up the data and set it up in Access.



In the Access 97 database it takes 9 minutes to run all these updates but in the Access XP database is takes about 32 minutes. Both the front end database and network database are converted to XP. I have found that if you link a 97 table to an Access XP database, you slow everything down in the XP database ???, so I’ve made sure that all linked tables are XP. I’ve looked at my queries and tried to optimize as much as I can but nothing has worked.



I have several parameter queries that really seems to run extremely slow. I’m updating fields in on table with data from a field in another table. I using the query execute method with a loop to set the parameter each time and then execute the query This works well in 97 but in XP it’s extremely slow. I’ve changes indexes in the tables and at one point and at one point removed all indexes except for the index on the joining fields in the table.



All of the forms and reports in the database also run much slower.



My company has also upgraded. When I was using the 97 database I was using windows NT. With the XP database I’m using Windows XP.



If anyone has any advice or ideas, I would appreciate hearing from you.

View 4 Replies View Related

Access 2000 Db Conversion To SQL

Nov 10, 2006

Hi,

I've got a fairly complex access 2000 database that's split into front and back ends.

There's 32 back end tables and a whole host of complex front end queries and forms. The whole thing's around 25,000 lines of code so not a 5 minute job to rebuild! (sorry but i just can't stop tweaking it!!)

I hate the idea of all that data moving across the network, and having to make the front end machines work so hard so I'd like to get the server doing it's fair share of work because it's got dual 3Ghz Xeon processors which aren't earning their keep! I was thinking about converting the mdb to SQL and although i could pay someone to do it for me, i do like to learn! (so when it breaks i'm not at anyone's mercy!!)

I assume there isn't an upsizing tool on the planet that will let me do this by clicking a button so can anyone point me in the right direction with regard to something decent that can automate as much as possible. PLUS a good source of information on the differentces between access and SQL so that i can start seeing how to accomplish with SQL what i'd do in Access?

Thanks,
Vince

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved