Import Mutiple Excel Files with Multiple Sheets to Access

kczinc

New Member
Joined
Dec 14, 2007
Messages
2
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!

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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

idaho_fish

New Member
Joined
Dec 16, 2007
Messages
7
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

New Member
Joined
Dec 14, 2007
Messages
2
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.

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,197
Messages
5,857,884
Members
431,905
Latest member
RW11700

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top