Importing Multiple Excel Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several excel files (100+) with data I need to import into Access.

Each excel file has multiple sheets (4 to 6 sheets). I need to import data
from each sheet.
No blank sheets are included, and all sheets are formated the same.

Thanks in advance for any assistance.

TB
 
Look in Access Help for the FileSearch object. You can use it to get an
array of the Excel files you want to import. You can then use the
TransferSpreadsheet method to import data from the files. The tricky part
will be knowing which sheets you want to import. You may have to create an
Excel object to open each file and get the count of sheets in the file so you
know how many TransferSpreadsheet calls you need to do for the sheet:

Do a FileSearch to get an array of Excel files to import.
Do a Loop through all the files in the array
Open the file
Get a count of sheets in the file
Close the file
Do a Loop through the sheets
Do a TransferSpreadsheet for a sheet
End of Loop through sheets
End of Loop through files
 
Back
Top