CODEWITHSUNDEEP
excelvbams-access
Cagg
Cagg

Reputation: 159

transferspreadsheet range error:3011 stuck at column "IV"

I am using the below function to export columns to access DB. It works perfectly well for all the columns before "IV" but gives an error for all the ranges in and after column "IV". My sheet name is "Perm-1" and range is IV3:IV4 in below example. Could not find anything on the google or Microsoft documentation. Anyone faced similar error please let me know if you were able to resolve it.

Public Sub Excel_Access_Export(accDB As Access.Application, tblName As String)

accDB.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadSheetType:=acSpreadsheetTypeExcel12, _
        TableName:=tblName, _
        Filename:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, _
        Range:="Perm-1$IV3:IV4"
End Sub

Edit:: Error Message: "MS Access cannot find the object 'Perm-1$IV3:IV4'. Make sure the object exists and spell its name correctly. If this is not a local object, check your network connection or contact server administration"

Upvotes: 1

Views: 306

Answers (1)

Winterknell
Winterknell

Reputation: 585

Column IV is column 256. Access 2007-2010 has a limit of 255 columns. Transferspreadsheet shares these column limits, but (perhaps more to the point, as it turns out) is also restricted by an outdated excel object model (256 columns and 65536 rows).

Here is some sample code that works around the columns issue. (You can work around the row limit in the same way - copy it to rows 1 to 65536 and import in chunks of up to 65535 rows.) I added a new sheet, "Staging" that is simply an empty sheet to temporarily hold the stuff you want to transfer. For this example I'm transferring just one column.

There may be better workarounds out there but for current purposes this is a POC. And yeah, I'm using "thisworkbook" and the transfer is using "activeworkbook".

Sub test()
    Dim accDB As Access.Application

    Set accDB = New Access.Application
    accDB.Application.Visible = True
    accDB.OpenCurrentDatabase "C:\Bench\Database11.accdb", True
    Call Excel_Access_Export(accDB, "Table1", 257)
End Sub

Public Sub Excel_Access_Export(accDB As Access.Application, tblName As String, col As Long)
    With ThisWorkbook.Sheets("Staging").Columns(1)
        .ClearContents
        .Value = thisWorkbook.Sheets("Perm-1").Columns(col).Value
    End With
    accDB.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, SpreadSheetType:=acSpreadsheetTypeExcel12, _
        TableName:=tblName, Filename:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, Range:="Staging$A3:A4"
End Sub

Upvotes: 4

Related Questions