(Solved) – Add field filepath for multiple Excel file import within Access

(solved)-–-add-field-filepath-for-multiple-excel-file-import-within-access

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.

Leave a Reply

Your email address will not be published. Required fields are marked *