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