I have the following Module in Access:
On Error Resume Next Dim strDir As String Dim strFile As String Dim I As Long I = 0 strDir = "C:excelTest" strFile = Dir(strDir & "*.xlsx") While strFile <> "" I = I 1 strFile = strDir & strFile Debug.Print "importing " & strFile DoCmd.TransferSpreadsheet acImport, , "mainTable", strFile, False 'has columnheaders strFile = Dir() Wend MsgBox "Load Finished" importExcelSheets = I End Function
This imports the data from the xlsx files within the directory (C:excelTest). This all works fine, but how can I add an additional field which stores the directory and file?
ie. If I have a file test.xlsx during the import a field is created and the path C:excelTesttest.xlsx is stored.