Import multiple Excel Sheets into Access Table

G

gmarmar

The goal is an Access VBA code that opens an Excel workbook and appends ALL
the sheets ono by one into an Access table. Every Sheet has the same
structure (columns) and the columns are identical to the Table fields.
BUT, I dont know the number of the sheets (could be more than 100) on every
workbook, as well as their names.

Therefore i was looking for a code similar to the following:
For i = 1 To MyBook.Sheets.Count (as in Excel VBA)
Call ModifyMBMTable(MyBook, SelectedSheet, MyTable)
Next i

where,
Call ModifyMBMTable(MyBook, SelectedSheet, MyTable) could be:
MySheet = SelectedSheet & "!A2:K1441"
DoCmd.TransferSpreadsheet acImport, , "MBMDATA", MyBook, True, MySheet

I would appreciate if you could help me.
Thanks in advance,
gm
 
Top