Excel VBA - Import range of data from Excel to Access table

[Minnesota Mike]

I am creating a macro in Excel 2010 that selects a range of data (which changes each month) and copies that range into a table in Access 2010. I am getting the error message "Run-time Error 2498: An expression you entered is the wrong data type for one of the arguments." The macro stops at the DoCmd.TransferSpreadsheet line. I think I have everything defined appropriately but something is a miss. The macro code is shown below. Thanks for input.

Sub Access_Export_Table()

    Dim app_Access As Access.Application
    Dim wb As Workbook
    Dim wb_Path As String
    Dim wb_Name As String
    Dim ws As Worksheet
    Dim int_Count_Rows As Integer
    Dim int_Count_Columns As Integer
    Dim rng_Export As Range
    
    Set wb = ActiveWorkbook
    wb_Path = wb.Path
    wb_Name = wb.Name
    
    Worksheets("Export").Activate
    Set ws = ActiveSheet
    Range("A1").Select
    int_Count_Rows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    int_Count_Columns = Selection.End(xlToRight).Column
    Set rng_Export = Range(Cells(1, 1), Cells(int_Count_Rows, int_Count_Columns))
    rng_Export.Select
    
    Set app_Access = Access.Application
 
    Application.ActivateMicrosoftApp xlMicrosoftAccess
    
    app_Access.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Imported Forecast", _
        wb_Path & "\" & wb_Name, _
        True, _
        rng_Export
        
End Sub

 

[AlphaFrog]

The Range argument is expected to be in the form of a string. Try this...

    app_Access.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Imported Forecast", _
        wb_Path & "\" & wb_Name, _
        True, _
        rng_Export[COLOR=#FF0000].Address[/COLOR]

DoCmd.TransferSpreadsheet Method

Sorry for your loss (Go Packers).

 

[Minnesota Mike]

Thanks for the reply AlphaFrog. I applied your suggestion but that yielded another error.

The error now is "The Microsoft Access database engine could not find the object '$A$1:$N$4369'. Make sure the object exists and that you spell its name and the path name correctly. If '$A$1:$N$4369' is not a local object, check your network connection or contact the server administrator." The range referenced in the error message is the address of the range defined in macro as "rng_Export". I see in the Locals Window that the macro understands that is the address of the range.

The macro has already defined the path name and file name for the Excel workbook; do I also need to define the sheet name somewhere in the code? From the examples I have seen, it does not appear that is a requirement.

Thanks again for your assistance.

P.S.
And thanks for the condolences but I have mixed feelings. My heart was for the Vikings but my money was on the Packers.

 

[AlphaFrog]

The macro has already defined the path name and file name for the Excel workbook; do I also need to define the sheet name somewhere in the code? From the examples I have seen, it does not appear that is a requirement.

I don't know if the sheet name is required. This reference suggest you do need it.
General: Importing Excel spreadsheets from code

Give it a shot.

    app_Access.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Imported Forecast", _
        wb.FullName, _
        True, _
        "Export!" & rng_Export.Address

 

[Minnesota Mike]

Finally got it solved!!!

The sheet name is required. However, the trick is that the address of the range can not be an absolute address; that is with "$" signs in the address.

I added the following statements:
Dim ws_Name As String
Dim str_Export_Address As String
Dim str_Export_SheetAddress As String
ws_Name = ws.Name
str_Export_Address = rng_Export.Address
str_Export_Address = WorksheetFunction.Substitute(str_Export_Address, "$", "")
str_Export_SheetAddress = ws_Name & "!" & str_Export_Address

I then changed the DoCmd.TransferSpreadsheet statement to be:
app_Access.DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"Imported Forecast", _
wb_Path & "\" & wb_Name, _
True, _
str_Export_SheetAddress

 

[AlphaFrog]

Glad you figured it out.

The range.Address property has arguments to exclude the $. In the vba Editor, highlight the Address keyword and press F1 for syntax help.

Also, you could use the workbook.FullName property to get both the path and filename of the workbook.

    app_Access.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Imported Forecast", _
        [COLOR="#FF0000"]wb.FullName[/COLOR], _
        True, _
        [COLOR="#FF0000"]"Export!" & rng_Export.Address(0, 0)[/COLOR]