I've been using this site for ages, but this is my first post. I found several solutions that come close, but none that precisely answer my question below.
I am using the following code to import multiple files to Access using the TransferSpreadsheet code in a vba module. However, several of the files to import have multiple sheets (2, 3, or 8), and my code only imports the first worksheet in the workbook. I'd like to import all the data on each sheet into the same table in access, and all the worksheets have exactly the same format and column headers. I am thinking there must be a way to loop through the sheets, but it seems as though Access doesn't understand the same ws code that Excel does? Any advice is greatly appreciated!
I am using the following code to import multiple files to Access using the TransferSpreadsheet code in a vba module. However, several of the files to import have multiple sheets (2, 3, or 8), and my code only imports the first worksheet in the workbook. I'd like to import all the data on each sheet into the same table in access, and all the worksheets have exactly the same format and column headers. I am thinking there must be a way to loop through the sheets, but it seems as though Access doesn't understand the same ws code that Excel does? Any advice is greatly appreciated!
Code:
Function Import_multi_SKfile()
Dim fs, fldr, fls, fl
Dim myPath As String
myPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.getfolder(myPath & "UploadSKData\")
Set fls = fldr.files
For Each fl In fls
If Right(fl.Name, 4) = ".xls" Then
'For Each ws In Worksheets - this is the code that creates the error
DoCmd.TransferSpreadsheet acImport, , "tblSmartKeys", myPath & "UploadSKData\" & fl.Name, True
'Next ws
End If
Next fl
End Function