Excel spreadsheet imported out of sort order

[goodfu]

I am importing an Excel spreadsheet into Access using the following, which works, but the resulting table doesn't have the records in the same order as the original spreadsheet and there is no field to sort on.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblImportmain", _
        FilePath, _
        True, SheetPath

So, is there any way to make it so the rows stay in the same order in the Access table as they were in the spreadsheet?

 

[boblarson]

Access does not store records in any particular order in the table. So, no - unless you include an order for it, it will not be viewable in any order. And it won't be stored in the table in any particular order.

So, you may want to add a field in the Excel file to add a numbered order first so you can import and have that field as the order you want. Then a query can sort it for you and you can use that query in place of the table where you wanted to use the table.

 

[goodfu]

Thanks.

So if I were to put an autonumber on the Access table, that wouldn't help any? I heard changing it to acLink will preserve the order.

 

[boblarson]

Thanks.

So if I were to put an autonumber on the Access table, that wouldn't help any? I heard changing it to acLink will preserve the order.

It will not work for autonumber as it isn't necessarily going to import in the same order. As for linking, it might work for you. But if you want to import, you can add the column to sort on by using this code (put it into a standard module - not form, report or class module).

Function AddColOrder2XL(strPath As String, blnHasHeaders As Boolean)
    Dim objXL As Object
    Dim varSplit As Variant
 
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
 
    With objXL
        .Workbooks.Open (strPath)
        varSplit = Split(.ActiveSheet.UsedRange.Address, "$")
        .ActiveSheet.Range(varSplit(3) & "1").Offset(0, 1).Select
        If blnHasHeaders Then
            .ActiveCell.Offset(1, 0).Select
        End If
        Do Until .ActiveCell.Row = CLng(varSplit(4)) + 1
            .ActiveCell.Value = .ActiveCell.Row + blnHasHeaders
            .ActiveCell.Offset(1, 0).Select
        Loop
 
       .ActiveWorkbook.Close True
       .Quit
       
    End With
 
Set objXL = Nothing
End Function

 

[goodfu]

So linking just points at a spreadsheet?

 

[boblarson]

So linking just points at a spreadsheet?

Essentially, yes.

 

[goodfu]

Thanks for the help.