Import Data from Unsaved Excel Spreadsheet to Access Table
Posted on 2006-07-08
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.
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.