Multiple excel files

  • Thread starter Thread starter dchristo
  • Start date Start date
D

dchristo

I have multiple excel files in the same format, just different names that I
need to import into an Access table? How can accomplish this?

All the files are in the same directory.
 
Same ACCESS table? If yes, this generic code can be modified to work for
you.

Dim strFile As String
Const strPath As String = "PathToTheEXCELFilesFolder\"
Const blnHasFieldNames As Boolean = True ' Or set to False
Const strTablename As String = "NameOfACCESSTable"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
strFile = Dir()
Loop
 
Thanks for the reply.

I tried this as a public function, I am getting the following error message
at this line:

strFile = Dir(strPath & "*.xls")

Error: Constant Expression Required.
 
I got it - thanks for all your help.

Ken Snell (MVP) said:
Same ACCESS table? If yes, this generic code can be modified to work for
you.

Dim strFile As String
Const strPath As String = "PathToTheEXCELFilesFolder\"
Const blnHasFieldNames As Boolean = True ' Or set to False
Const strTablename As String = "NameOfACCESSTable"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
strFile = Dir()
Loop
 
Back
Top