dastrw
asked on
Import Data from Unsaved Excel Spreadsheet to Access Table
I have an Access 2000 database application that creates and populates an Excel spreadsheet.
The spreadsheet then does some calculations and generates some new data. I would like to import this data back into one of my Access tables.
How do I do this?
I looked at the TransferSpreadsheet method, but it has a couple of problems:
1) It requires a filename, but I have not yet stored my spreadsheet so it doesn't have a name
2) It doesn't let you specify a worksheet name and my workbook has multiple worksheets
I am just using the spreadsheet because it has functionality that I can't replicate in Access (e.g. Solver, Normsinv() function, etc.) so I don't want to have to save it. And I need the multiple worksheets because of the shear quantity and complexity of the data I am working with.
Any ideas?
For now I am just going to use a brute force technique of creating INSERT statements data field by data field as I need to get something working this weekend. But I would appreciate something quicker and more elegant.
Thanks.
The spreadsheet then does some calculations and generates some new data. I would like to import this data back into one of my Access tables.
How do I do this?
I looked at the TransferSpreadsheet method, but it has a couple of problems:
1) It requires a filename, but I have not yet stored my spreadsheet so it doesn't have a name
2) It doesn't let you specify a worksheet name and my workbook has multiple worksheets
I am just using the spreadsheet because it has functionality that I can't replicate in Access (e.g. Solver, Normsinv() function, etc.) so I don't want to have to save it. And I need the multiple worksheets because of the shear quantity and complexity of the data I am working with.
Any ideas?
For now I am just going to use a brute force technique of creating INSERT statements data field by data field as I need to get something working this weekend. But I would appreciate something quicker and more elegant.
Thanks.
If you're writing data out to one sheet, and you wish to bring data back in from another simultaneously, you can save the spreadsheet temporarily, link to the spreadsheet to grab the data, do whatever with the data, then delete the link and spreadsheet...all within a function.
I know my explan is rather cryptic, but if you provide a few details, this is doable.
J
I know my explan is rather cryptic, but if you provide a few details, this is doable.
J
ASKER
GRayL,
I am doing this programmatically. So I need a solution that will work in VBA. The last time I tried to pass an object like a worksheet in a place where it expected a string, I got an unrecoverable error.
jefftwilley,
Yes I know I can do what you want, but the sheet I create is going to be kept for debugging purposes only. Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user. He can choose to ignore it, look at it and delete it, or save it off. But I won't know which, and I need to save the data to the database before he makes his decision.
Anyone will be able to use the application from any PC. I don't want to have my application write files to other people's PCs without them knowing about it. Especially if I don't need to do it.
I am doing this programmatically. So I need a solution that will work in VBA. The last time I tried to pass an object like a worksheet in a place where it expected a string, I got an unrecoverable error.
jefftwilley,
Yes I know I can do what you want, but the sheet I create is going to be kept for debugging purposes only. Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user. He can choose to ignore it, look at it and delete it, or save it off. But I won't know which, and I need to save the data to the database before he makes his decision.
Anyone will be able to use the application from any PC. I don't want to have my application write files to other people's PCs without them knowing about it. Especially if I don't need to do it.
How are you creating the spreadsheet now? Are the "results" on the same sheet as the "Input Data"?
ASKER
jefftwilley,
I added a Reference to the MS Excel library to my Access database. Then in the button click code on a form I have this code:
'Create Excel Workbook with Worksheets
Set excelApp = CreateObject("Excel.applic
Set wb = excelApp.Workbooks.Add
The cool thing about this is that when it is easier to do things in Access I can do them in access and then write the results to cells in Excel When it is easier to do things in Excel, then I can use the library code to get Excel to do its thing.
Getting things from Access to Excel is easy using QueryTables.
What I need is something like a reverse QueryTable.
Any ideas?
I added a Reference to the MS Excel library to my Access database. Then in the button click code on a form I have this code:
'Create Excel Workbook with Worksheets
Set excelApp = CreateObject("Excel.applic
Set wb = excelApp.Workbooks.Add
The cool thing about this is that when it is easier to do things in Access I can do them in access and then write the results to cells in Excel When it is easier to do things in Excel, then I can use the library code to get Excel to do its thing.
Getting things from Access to Excel is easy using QueryTables.
What I need is something like a reverse QueryTable.
Any ideas?
You mentioned that you were running calculations to get results.
<<<The spreadsheet then does some calculations and generates some new data. I would like to import this data back into one of my Access tables.>>>
at the same time, you
How are you getting the formulas into the spreadsheet? are you opening a template that already has them in it?
<<<Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user.>>>
Easy enough to open a new worksheet, but unless you're copying formulas to the spreadsheet, there won't be much calculating going on.
<<<The spreadsheet then does some calculations and generates some new data. I would like to import this data back into one of my Access tables.>>>
at the same time, you
How are you getting the formulas into the spreadsheet? are you opening a template that already has them in it?
<<<Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user.>>>
Easy enough to open a new worksheet, but unless you're copying formulas to the spreadsheet, there won't be much calculating going on.
ASKER
How do I get the formulas into the spreadsheet?
I created a query in Access that outputs the formulas rather than the values. When I execute the QueryTable, the formulas are written to the cells.
The only problem is that Excel thinks these are strings so it prepends them with a single quote.
All I need to do is a global search and replace to remove all of the single quotes and then everything works fine.
Also, Excel has things like Solver which Access doesn't. Anything you can do in Excel directly you can pretty much get Access to do. With the Excel library I can run Excel commands against the Excel workbook with not too many problems.
I created a query in Access that outputs the formulas rather than the values. When I execute the QueryTable, the formulas are written to the cells.
The only problem is that Excel thinks these are strings so it prepends them with a single quote.
All I need to do is a global search and replace to remove all of the single quotes and then everything works fine.
Also, Excel has things like Solver which Access doesn't. Anything you can do in Excel directly you can pretty much get Access to do. With the Excel library I can run Excel commands against the Excel workbook with not too many problems.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
my last comment regarding DoCmd.OutputTo, the alternative is to use Excel auto, like u have done to create a unsaved Excel document but based on the new table
In this case, u can just do CopyFromRecordset
In this case, u can just do CopyFromRecordset
I see that you have started with Excel automation I.e.
Set excelApp = CreateObject("Excel.applic
Set wb = excelApp.Workbooks.Add
What you need to do is continue with automation to populate the input cells, calculate, run solver or do whatever you do and then read the output cell results back in to Access with VBA.
A few tips:
1) Create the Excel VBA code in Excel first. An easy way to get started is to step through the process manually with the macro recorder switeched on. Now edit the Excel VBA code removing extranuos commands eg navigation and absolute cell references of these are not appropriate.
Also consider giving meaningful range names to your your input and output cell ranges. You will find the Excel code easier to write, debug and maintain.
2) Once you have the procedure working in Excel then add the automation steps in Access I.e.
Dim strOutputResult
Set excelApp = CreateObject("Excel.applic
With excelApp
'==>> Now copy and paste the Excel VBA code here
'==>> Prefix all of the Excel commands with a '.'
'E.g.
.Workbooks.Add
.Range("C5").Value = 'Your input data'
.calculate
strOutputResult = .Range("B5").Value
etc
End with
'Now append the result data to your Access table
Set excelApp = CreateObject("Excel.applic
Set wb = excelApp.Workbooks.Add
What you need to do is continue with automation to populate the input cells, calculate, run solver or do whatever you do and then read the output cell results back in to Access with VBA.
A few tips:
1) Create the Excel VBA code in Excel first. An easy way to get started is to step through the process manually with the macro recorder switeched on. Now edit the Excel VBA code removing extranuos commands eg navigation and absolute cell references of these are not appropriate.
Also consider giving meaningful range names to your your input and output cell ranges. You will find the Excel code easier to write, debug and maintain.
2) Once you have the procedure working in Excel then add the automation steps in Access I.e.
Dim strOutputResult
Set excelApp = CreateObject("Excel.applic
With excelApp
'==>> Now copy and paste the Excel VBA code here
'==>> Prefix all of the Excel commands with a '.'
'E.g.
.Workbooks.Add
.Range("C5").Value = 'Your input data'
.calculate
strOutputResult = .Range("B5").Value
etc
End with
'Now append the result data to your Access table
ASKER
rockiroads,
>> But if u are saving to DB, why bother? U can always query the new table and dump in Excel format anyway
I need Excel to do some of the work. I need Excel to display the results, especially the formulas so that others can verify my results. The problem is not going from the DB to Excel, but from Excel back to the DB. If I could do all of the work in Access then I would do it all there and just push the results at the end to Excel, but I can't do that. Especially since I need Solver to generate some of the results for me.
>> DoCmd.TransferSpreadsheet acImport, , "mylocaltable", "C:\MyExcel\" & sFile, True, "George$A1:Z100"
This might work. I didn't know you could ad the sheet name this way. I will try it when I get a chance. I still don't like the idea of creating a temp file, but I guess that's not so bad especially if I create it in the C:\Temp directory.
millsco:
>> 'Now append the result data to your Access table
This is what I don't know how to do. Actually I do now either using rockiroads suggestion or what I am currently doing which is brute force creation of INSERT statements that I execute row-by-row:
For irow = 1 to nrow
dbIns = "insert into table values ("
for icol = 1 to ncol
dbins = dbins & ws.cells(irow,nrow).value & ","
next icol
querydef1.sql = dbins
querydef1.execute
next irow
(The above is a simplification. there are a few other commands to trim off the last comma, etc. but you get the idea.)
>> But if u are saving to DB, why bother? U can always query the new table and dump in Excel format anyway
I need Excel to do some of the work. I need Excel to display the results, especially the formulas so that others can verify my results. The problem is not going from the DB to Excel, but from Excel back to the DB. If I could do all of the work in Access then I would do it all there and just push the results at the end to Excel, but I can't do that. Especially since I need Solver to generate some of the results for me.
>> DoCmd.TransferSpreadsheet acImport, , "mylocaltable", "C:\MyExcel\" & sFile, True, "George$A1:Z100"
This might work. I didn't know you could ad the sheet name this way. I will try it when I get a chance. I still don't like the idea of creating a temp file, but I guess that's not so bad especially if I create it in the C:\Temp directory.
millsco:
>> 'Now append the result data to your Access table
This is what I don't know how to do. Actually I do now either using rockiroads suggestion or what I am currently doing which is brute force creation of INSERT statements that I execute row-by-row:
For irow = 1 to nrow
dbIns = "insert into table values ("
for icol = 1 to ncol
dbins = dbins & ws.cells(irow,nrow).value & ","
next icol
querydef1.sql = dbins
querydef1.execute
next irow
(The above is a simplification. there are a few other commands to trim off the last comma, etc. but you get the idea.)
If you are changing your mind and are creating a temp file then it is advisable to save as a .csv . You can build an import specification using this file format and use transfer spreadsheet to import the data back into your table. This of course triggered from the user's acceptance of the data as presented to him by Excel.
<off topic>
How was the weekend Rocki? Busy?
J
<off topic>
How was the weekend Rocki? Busy?
J
ASKER
jefftwilley,
I really don't want to change my mind.
Here is the problem with saving the spreadsheet:
What I want to happen is:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Insert final data back into database for archival purposes
4) Show Excel sheet to user
5) User can choose to Save sheet or delete it by pressing Save button
What will happen if I use a saved spreadsheet:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Save Excel sheet to C:\Temp
4) Import data back into Access for archival purposes
5) Show Excel sheet to user
6) If user wants to keep Excel sheet he presses Save button. But what happens is that spreadsheet gets saved in C:\Temp as some generic name. The next time he runs the program, if he clicks Save it will overwrite the previous file. So I have to depend on the person clicking 'Save As' rather than 'Save'. This shouldn't be too much of a stretch, but it is. Expecting people to press 'Save As' when they press 'Save' 99% of the time is just too much to expect.
So then I would have to do something like trap the 'Save' function and make it behave as a 'Save As' function and then put some code in 'On Close' to reverse the changes I made so that 'Save' behaves like a 'Save'.
Very messy!
I really don't want to change my mind.
Here is the problem with saving the spreadsheet:
What I want to happen is:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Insert final data back into database for archival purposes
4) Show Excel sheet to user
5) User can choose to Save sheet or delete it by pressing Save button
What will happen if I use a saved spreadsheet:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Save Excel sheet to C:\Temp
4) Import data back into Access for archival purposes
5) Show Excel sheet to user
6) If user wants to keep Excel sheet he presses Save button. But what happens is that spreadsheet gets saved in C:\Temp as some generic name. The next time he runs the program, if he clicks Save it will overwrite the previous file. So I have to depend on the person clicking 'Save As' rather than 'Save'. This shouldn't be too much of a stretch, but it is. Expecting people to press 'Save As' when they press 'Save' 99% of the time is just too much to expect.
So then I would have to do something like trap the 'Save' function and make it behave as a 'Save As' function and then put some code in 'On Close' to reverse the changes I made so that 'Save' behaves like a 'Save'.
Very messy!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
millsco,
>> docmd.transferspreadsheet acimport option
This would be OK if I could run it against the currently open workbook, but it seems that I can't. When I get a chance I'm thinking about asking for the path of the currently opened and never saved workbook. Maybe it has a name like 'C:\Temp\OKIFhje2.wls' or some other random name that MS gave it. Then maybe I can specify that name in the TransferSpreadsheet function and it will work. We'll see.
>> looping through a recordset in ADO or DAO to do the appends
Do you have any sample code? I've never used that method before.
>> If you have to use a generic file name to create the Excel file why not create the Excel file as read only.
Great idea! Also, I hadn't anticipated gathering feedback from users, but from what you say it may be possible to implement. So I'll think about it.
>> docmd.transferspreadsheet acimport option
This would be OK if I could run it against the currently open workbook, but it seems that I can't. When I get a chance I'm thinking about asking for the path of the currently opened and never saved workbook. Maybe it has a name like 'C:\Temp\OKIFhje2.wls' or some other random name that MS gave it. Then maybe I can specify that name in the TransferSpreadsheet function and it will work. We'll see.
>> looping through a recordset in ADO or DAO to do the appends
Do you have any sample code? I've never used that method before.
>> If you have to use a generic file name to create the Excel file why not create the Excel file as read only.
Great idea! Also, I hadn't anticipated gathering feedback from users, but from what you say it may be possible to implement. So I'll think about it.
ASKER
jjafferr,
Thanks for the code on reading individual cells, but I already know how to do that and unfortunately I have over a hundred columns and dozens of rows of data to transfer back to Access.
Your copy template code doesn't appear like it will work for me.
However, the links you provided to including Excel functions in queries is very nice. I can't use it in this application because I need the functions to be stored in the Excel spreadsheet for viewing by the code reviewers. However, there are a number of occasions where I would like to include a call to an Excel function in a query. What I have done in the past is created a table of values in Excel and then copied that to a table in my database and then joined to it for the values. With the code from the link you sent I can skip the table and join and just execute the function directly.
Thanks for the code on reading individual cells, but I already know how to do that and unfortunately I have over a hundred columns and dozens of rows of data to transfer back to Access.
Your copy template code doesn't appear like it will work for me.
However, the links you provided to including Excel functions in queries is very nice. I can't use it in this application because I need the functions to be stored in the Excel spreadsheet for viewing by the code reviewers. However, there are a number of occasions where I would like to include a call to an Excel function in a query. What I have done in the past is created a table of values in Excel and then copied that to a table in my database and then joined to it for the values. With the code from the link you sent I can skip the table and join and just execute the function directly.
Sample ADO recordset code
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable
Sorry above is incomplete, too enthusiastic with tab key
Sample ADO recordset code
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset 'various options available here use intellisense to choose appropriate
rs.LockType = adLockOptimistic 'various options available here use intellisense to choose appropriate
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable
For
rs.add
rs("Field1") = xlCell1
rs.update 'only necessary for DAO code but I find maintenance easier if I know where the recordset has been updated
Loop
Sample ADO recordset code
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset 'various options available here use intellisense to choose appropriate
rs.LockType = adLockOptimistic 'various options available here use intellisense to choose appropriate
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable
For
rs.add
rs("Field1") = xlCell1
rs.update 'only necessary for DAO code but I find maintenance easier if I know where the recordset has been updated
Loop
ASKER
millsco,
Looks ok if I just need to add a value or two, but what if I have close to two hundred columns and dozens of rows?
What I'm currently doing doesn't seem much less efficient than this. I imagine some sort of syntax supports updating or appending multi-column records on one line. But then that's not much different than what I am doing with creating one insert statement for each row.
Looks ok if I just need to add a value or two, but what if I have close to two hundred columns and dozens of rows?
What I'm currently doing doesn't seem much less efficient than this. I imagine some sort of syntax supports updating or appending multi-column records on one line. But then that's not much different than what I am doing with creating one insert statement for each row.
docmd.transferspreadsheet acimport
If you enter this part of the method from the Immediate Pane (get there by typing Ctl-G) you will be prompted for the rest of the input, including the 'range' which can be expressed as a WorkBookName!WorkSheetName