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
 
Back
Top