Access 2007 importing multiple spreadsheets into table

[Enchantix]

Hello,

I have access 2007 and im looking to import a folder (colour) containing multiple xlsm spreadsheets into a table in my access database.

ive used the following module code and upon some research I found that application.filesearch has been removed from access 2007.

is there an alternative code I can use for what I want to do?



Function ImportExcelFiles()
Dim Counter As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\Account\Falldown\Colour"
.SearchSubFolders = False
FileName = "*.xlsm"
.MatchTextExactly = False

If .Execute() > 0 Then
For Counter = 1 To .FoundFiles.Count
.FileName = .FoundFiles(Counter)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Shift falldown data", .FileName, True, "Sheet1!"

DoEvents
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With
End Function



kind regards

 

[Joe4]

Here are a few links that contain other solutions:
https://www.access-programmers.co.uk/forums/showthread.php?t=116652
https://support.microsoft.com/en-us...-invalid-reference-to-the-property-filesearch
(check out the links under the "Resolution" section)