Import Mutiple Excel Files with Multiple Sheets to Access

[kczinc]

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!

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

 

[idaho_fish]

Are you properly referencing the Excel Object Library?

From your code window, click on Tools | References and search for Microsoft Excel xx.x Object Library and make sure it is checked.

You may need to set up variables for ws in your code and set it to the proper object type, something like:

Dim ws as worksheet

HTH

 

[kczinc]

Thanks Idaho_fish. I didn't have the references set, but now I've done that and set up the variable (see code below) and when I run I'm getting the following error message:

"Method 'Worksheets' of object '_Global' failed" on the 'for Each ws In Worksheets' code. Is it not understanding Worksheets?

Also, I forgot to mention that I'm using Access 2003 in my original post.

Function Import_multi_SKfile()


Dim fs, fldr, fls, fl
Dim myPath As String
Dim ws As Worksheet


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
            DoCmd.TransferSpreadsheet acImport, , "tblSmartKeys", myPath & "UploadSKData\" & fl.Name, True
    Next ws
End If
 
Next fl

End Function