Importing data from multiple spreadsheets contained in one xls fil

[Guest]

Here is my case:
I need to import data from Excel files to an Access table. Each Excel file
may contain multiple spreadsheets (supposing all spreadsheets have same
columns). I tried to use DoCmd.TransferSpreadsheet. I could import data from
the first spreadsheet. It said we could use "Range" to specify multiple
spreadsheets. But I don't know exactly how many spreadsheets the current xls
file may have (different xls files have different numbers of spreadsheets).
Of course, I don't know those spreadsheet names either.

How can I complish those tasks in Access (VBA)?

 

[John Nurick]

Hi,

If you go to
http://groups.google.com and search for

"drink soup audibly" group:microsoft.public.excel.programming

you'll find a message from onedaywhen that includes a GetWSNames()
function. This returns an array containing the names of all the
worksheets in the workbook. You can then do something like this air code
(assuming that all worksheets have the same column headings and data
types):

Dim strWBKName As String
Dim arWBKNames As Variant
Dim strSheetName As String
Dim j as Long

strWBKName = "C:\Folder\File.xls"
arWBKNames = GetWSNames(strWBKName)
For j = 0 to UBound(arWBKNames)
strSheetName = arWBKNames(j) & "$"
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel8, _
"MyTable", strWBKName, False, _
strSheetName
Next 'j

 

[Guest]

Thanks, John. I've already solved the problem. The solution is kind of
similar to your solution. I created an Excel.Appliction object to open those
Excel files and then go through each spreadsheet and import data...

Thank you any way.