Results 1 to 2 of 2
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84

    VBA Import excel >255 columns with Transferspreadsheet

    Hi All.

    I have an excel file with 458 columns. I am trying to import thru VBA with transferspreadsheet using a range
    Code:
    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-02-2016, 11:01 AM
  2. Import Excel Worksheets and Skip Certain Columns
    By vcs1161 in forum Import/Export Data
    Replies: 5
    Last Post: 07-20-2015, 04:36 PM
  3. Replies: 12
    Last Post: 10-08-2014, 02:35 PM
  4. Import from Excel using TransferSpreadsheet
    By Sephaerius in forum Import/Export Data
    Replies: 6
    Last Post: 08-07-2013, 04:55 AM
  5. Import from Excel Using TransferSpreadsheet
    By P5C768 in forum Import/Export Data
    Replies: 10
    Last Post: 05-01-2013, 01:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums