Importing multiple Excel sheets into Access

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

Guest

My goal is to import data from multiple (several hundred) Excel files into 1
Access database. All Excel files would have the exact same format; only the
values would differ. Is this possible?

Let me know if you require additional explanation. Thanks in advance.
 
Yes, it is possible. The easiest way is to put all the excel files in the
same directory, then you can use the Dir() function to loop through the files
and use the TransferSpreadsheet method to import them:

Dim strDirectory As String
Dim strFileName As String

strDirectory = "e:\directory where files are\"
strFileName = Dir(strDirectory & "*.xls", vbDirectory)

Do While strFileName <> ""
Docmd.TranferSpreadsheet acImport, , strDirectory & strFileName,
"TargetTableName", True
strFileName = Dir()
Loop
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Several excel files into Several Access Tables 6
Import Excel 2010 table into Access 2010 - not listed as range 1
Importing excel into access 2
importing MULTIPLE excel files simultaneously 2
Importing Multiple Excel Files 1
Multiple table import? 3
Importing spreadsheet cells that have multiple values 1
Is it possible to import Excel sheet into multiple Access tables 6

Back
Top