VBA Import excel >255 columns with Transferspreadsheet

[SteveApa]

Hi All.



I have an excel file with 458 columns. I am trying to import thru VBA with transferspreadsheet using a range

If PROJECTDATA_COLUMNS_COUNT > 255 Then        For i = 1 To Int(PROJECTDATA_COLUMNS_COUNT / 255) + 1
            import_to_table_name = import_to_table_name & i
            top_left_cell_address = xlapp.Cells(1, (255 * (i - 1)) + 1).Address(rowabsolute:=False, columnabsolute:=False)
            bottom_right_cell_address = xlapp.Cells(PROJECTDATA_ROWS_COUNT, (255 * i)).Address(rowabsolute:=False, columnabsolute:=False)
            full_range = top_left_cell_address & ":" & bottom_right_cell_address
            range_to_import = xlsheet.Name & "!" & full_range
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, import_to_table_name, PATH_AND_FILENAME, True, range_to_import
        Next i
    
    Else
.....
endif

it works for the first 255 columns, but then fails when I loop to do columns 256 to 485??
Should work, right?

Thanks.
Steve

[ranman256]

access doesn't work that way. You don't need 255 columns.
Redesign for a relational database.
The keys fields are in 1 table, then all attributes belonging to that master record are put into a sub table.