Import Sheet2, Sheet3, and Sheet4 into Existing Access Tables

[Healey33]

As the title states, I am trying to import and append multiple sheets from a single excel file into tables in my Access DB. The excel Sheet2 will always be appended to Access table1, Sheet3 will always be appended to Table2 and Sheet4 will always be appended to Table3. The sheets in excel already contain the exact column headers in the top row.
The goal is to use the file dialog from Access and navigate to the excel file, select it, and have the data in the 3 sheets appended to the tables in the database. I am an intermediate VBA programmer and have been unable to find a solution to this online. Any help would be greatly appreciated!
I am using MS Office 2010

Thanks

 

[Healey33]

Here is what I have so far. Just trying with one sheet for now.

Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant


    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = varItem
            MsgBox "File: " & strFile
        Next
    End If
    Set f = Nothing






DoCmd.TransferSpreadsheet acImport, 9, "Cost_Breakdown", strFile, True, , "Sheet2"

When I manually import the sheet into the table "Cost_Breakdown", there are no issues. when running this code, I get Runtime Error '2498' (type mismatch for one of he fields). The only difference I see is that I have an autonumber field in the database that is not populated in the excel table. Is this what is causing me problems?

Edit: It appears the AutoNumber was not the root cause. Tried with another table that does not have an autonumber field and received the same error message.

 

[Healey33]

I solved this. Syntax error with the DoCmd.TransferSpreadsheet command. It should have looked like this:

DoCmd.TransferSpreadsheet acImport, 10, "Cost_Breakdown", strFile, True, "Sheet2$"

I had the wrong spreadsheet type (10 is needed for Excel 2010) and I had an extra comma before the "Sheet2$". I also missed the "$" after the sheetname.
Hope this helps anyone who runs into the same problem!

 

[ranman256]

Save the excel file to the same place & name everytime.
Link in the 3 sheets.
build 3 queries , each 1 imports 1 sheet.
put in macro.

So you would then
1. Save the new file and overwrite the generic import name.
2. Run import macro. done.