TransferSpreadsheet Non-continuous ranges (1 Viewer)

[ianverrier]

I am trying to import a large spreadsheet into access with the following code:

DoCmd.TransferSpreadsheet acImport, , "import_" & worksheet_list!worksheet, path & "\input_files\" & rnc_list!RNC & ".xls", True, worksheet_list!worksheet & "!A:HA"

But I get this error: search key not found


But it works with the range: A:GV and A:HA so there is nothing wrong with the field names. So I assume it is over the max number of fields.

I need field A and HA in the same table since A is used to identify the record.

To solve this problem I want to import 2 ranges into the same table but I can't figure out the syntax. (eg. A:AG and CA:HA)

I tried
!A:AG,CA:HA
!(A:AG,CA:HA)

If this is not posiible is there another way to import tables with too many field names when the first field need to be present to identy which record the rest of the fields are attached to.

Thanks
Ian

 

[boblarson]

You can't use Transferspreadsheet like that. You would need to use Excel Automation to do it.

But why not just import the whole thing to a transitional table and then just append the columns you want to the live table. Then clear the transitional table for the next use.

 

[ianverrier]

Thanks for the response. I can't import the whole spreadsheet to a transitional table beacause it has too many fields.

 

[boblarson]

Thanks for the response. I can't import the whole spreadsheet to a transitional table beacause it has too many fields.

Then Excel Automation code is the only way for you.

Function TestExceladfd()
    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim strTempPathAndFileName As String
    Dim strPathAndFileName As String
    Const xlToLeft As Long = -4159
 
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True

    strPathAndFileName = "C:\Somefolder\Somefile.xls"
    strTempPathAndFileName = "C:\SomeOtherFolder\SomeOtherFileName.xls"
 
    Set xlWB = objXL.Workbooks.Open(strPathAndFileName)
   ' saves the workbook as another file so we can delete unwanted columns
    xlWB.SaveAs strTempPathAndFileName

    xlWB.Close

    Set xlWB = objXL.Workbooks.Open(strTempPathAndFileName)

    Set xlWS = xlWB.Worksheets("SheetNameHere")
 
    ' selects and removes the extraneous columns
    xlWS.Columns("AH:BZ,").Select
    objXL.Selection.Delete Shift:=xlToLeft
 
    xlWB.Save
    xlWB.Close
    objXL.Quit
 
    Set objXL = Nothing

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", strTempPathAndFileName, True
 
    If Dir(strTempPathAndFileName) <> "" Then
        Kill (strTempPathAndFileName)
    End If
 

End Function

So this opens the workbook, then copies it, by using a save as, to another location and then deletes the unwanted columns (I didn't include any that might be after column CH so if so you will need to modify).

Then it will save the temp file, do the import, and then deletes the temp file.

Hopefully this will help.