Hi Folks
I am trying to import data from multiple Excel files saved in the one folder to a single table in Access.
I found this macro on the web and I think I am nearly there but when I run the macro it returns a fault in the code here
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
full code I am using
Code:
Option Compare Database
Option Explicit
Public Function ImportExcelMultipleFiles()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\Kevin\Google Drive\ATOS\ATOS Reports\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
End Function