Multiple excel files

[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.

 

[Ken Snell \(MVP\)]

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

 

[dchristo]

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.

 

[dchristo]

I got it - thanks for all your help.

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