Hi all,
I've got this code (see below) to import a designated worksheet from multiple Excel workbooks into Access (using Office 2003 for both), which works a treat. My issue however is that I can't figure out how to make it now check if a certain worksheet exists (in this case "Detail_1") in a file, then execute the transferspreadsheet command if it does, but move to the next file if it doesn't. Currently all I get when I run the routine is the '"Detail_1$" is not a valid name' error.
Any help appreciated!
Code below (I've highlighted the row that identifies my worksheet I want to check for):
'Import from multiple sheets in multiple files, in a set folder.
'You will need to set a reference to the 'Microsoft Office x.0 Object Library'
'where x is your version number.
'This can be found under Tools > References in the VBA editor screen.
'You may also need to set a reference to Microsoft Scripting Runtime.
Function ImportExcelFiles()
Dim Counter As Integer
With Application.FileSearch
.NewSearch
.LookIn = "J:\Public\Teams\Business Projects\One-Off Projects\Bad Debt Collect Rate\F&A Payment Backfill Project" 'change this to your actual directory
.SearchSubFolders = False 'set to True if you want to search subfolders too
.FileName = "IEX*.xls" 'changed HERE
If .Execute() > 0 Then 'files found
For Counter = 1 To .FoundFiles.Count 'loop through files
.FileName = .FoundFiles(Counter) 'set / get the file name
'Change the "ImportFile" part in the line below if you are using a different table name
'Note: 1 command for each worksheet. I have assumed they are Sheet1 etc.
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "2012_13 Payments MasterList", .FileName, False, "Detail!" 'Changed HERE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "2012_13 Payments Masterlist 2", .FileName, False, "Detail_1!" 'Changed HERE
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, True, "Sheet1!"'Changed HERE
DoEvents 'don't take over all of the PC resources
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else 'files not found
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With
End Function
I've got this code (see below) to import a designated worksheet from multiple Excel workbooks into Access (using Office 2003 for both), which works a treat. My issue however is that I can't figure out how to make it now check if a certain worksheet exists (in this case "Detail_1") in a file, then execute the transferspreadsheet command if it does, but move to the next file if it doesn't. Currently all I get when I run the routine is the '"Detail_1$" is not a valid name' error.
Any help appreciated!
Code below (I've highlighted the row that identifies my worksheet I want to check for):
'Import from multiple sheets in multiple files, in a set folder.
'You will need to set a reference to the 'Microsoft Office x.0 Object Library'
'where x is your version number.
'This can be found under Tools > References in the VBA editor screen.
'You may also need to set a reference to Microsoft Scripting Runtime.
Function ImportExcelFiles()
Dim Counter As Integer
With Application.FileSearch
.NewSearch
.LookIn = "J:\Public\Teams\Business Projects\One-Off Projects\Bad Debt Collect Rate\F&A Payment Backfill Project" 'change this to your actual directory
.SearchSubFolders = False 'set to True if you want to search subfolders too
.FileName = "IEX*.xls" 'changed HERE
If .Execute() > 0 Then 'files found
For Counter = 1 To .FoundFiles.Count 'loop through files
.FileName = .FoundFiles(Counter) 'set / get the file name
'Change the "ImportFile" part in the line below if you are using a different table name
'Note: 1 command for each worksheet. I have assumed they are Sheet1 etc.
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "2012_13 Payments MasterList", .FileName, False, "Detail!" 'Changed HERE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "2012_13 Payments Masterlist 2", .FileName, False, "Detail_1!" 'Changed HERE
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, True, "Sheet1!"'Changed HERE
DoEvents 'don't take over all of the PC resources
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else 'files not found
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With
End Function
