How to Import multiple excel spreadsheats?

[Arthur Cheung]

Dear all,

I have an excel workbook which contains over 200 spreadsheets with identical
data structure. How can I automatically import them into one access table?

I am using Office 2K.

Thanks and regards,
Arthur Cheung

[Johnny Meredith]

Without writing code, you might want to paste all the data to one sheet
(if possible), or export all data to *.CSV files and put those
together.

If that would take too long, do something like this:

Go into the excel spreadsheet and name the ranges that include the data
on each sheet. Name them "GetMe1", "GetMe2", etc. Also, make sure you
have field names that are identical to the field names in the
destination table.

Code would look something like this (air):

Sub foo()
Dim i As Integer
Dim strNamedRange As String
Dim strDestinationTable As String
Dim strFileName As String
strDestinationTable = "MyTable" 'Input table name here
strFileName = "c:\MyFile.xls" 'Input filename here
For i = 1 To 200
strNamedRange = ""
strNamedRange = "GetMe" & CStr(i)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strDestinationTable, strFileName, True, strNamedRange
Next
End Sub

Johnny
j[deleteme}meredith AT Google's Mail