Access to Excel: VBA code to sort columns from Access Database

Change to this:

With xlsws
.Sort Key:=.Range("N2:N2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort Key:=.Range("O2:O2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With

Select allOpen in new window

You did not actually tell Excel to sort the range.

With xlswb.Worksheets("Received").Sort
    .SortFields.Add Key:=xlsws.Range("N2:N2000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .SortFields.Add Key:=xlsws.Range("O2:O2000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Apply
End With

Select allOpen in new window

can you try this?xlswb.Worksheets("Received").Sort Key1:=xlsws.Range("N2:N2000"), Order1:=xlAscending, Key2:=xlsws.Range("O2:O2000"), Order2:=xlAscendingno need to specify the data option as the default value is normal.hope this helps.

None of the above worked :
broomee9: Errors out  - Named argument not found in Line 2

matthewspatrick: Errors out - method 'apply' of object 'sort' failed in Line 6

spattewar:Errors out  - Named argument not found  

Try this:

With xlsws.Cells
.Sort Key:=xlsws.Range("N2:N2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort Key:=xlsws.Range("O2:O2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With

Select allOpen in new window

Also add 1 to the end of each of the sort parameters.

With xlsws.Cells
.Sort Key1:=xlsws.Range("N2:N2000"), SortOn1:=xlSortOnValues, Order1:=xlAscending, DataOption1:=xlSortNormal
.Sort Key1:=xlsws.Range("O2:O2000"), SortOn1:=xlSortOnValues, Order1:=xlAscending, DataOption1:=xlSortNormal
End With

Select allOpen in new window

Error Named argument not found  on  --  Key:=

I have tried that , Apply is the error

This works.  See attached files.

Sub test()

    Dim xlsApp As Excel.Application
    Dim xlsWbk As Excel.Workbook
    Dim xlsSht As Excel.Worksheet
    
    Set xlsApp = Excel.Application
    Set xlsWbk = xlsApp.Workbooks.Open("C:\test.xls")
    Set xlsSht = xlsWbk.ActiveSheet
    
    xlsApp.Visible = True
    
    With xlsSht.Cells
        .Sort Key1:=.Range("N1"), Order1:=xlAscending, Key2:=.Range("O1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    End With
    
End Sub

Select allOpen in new window

test.xls
db1.mdb

Of course set the variables to nothing at the end.  And copy the Excel file to C:\.  Also, you would need to reference the Excel object in the VBE editor (Tools --> References --> Microsoft Excel 11.0 Object) if you're going to add the code to another database.

Set xlsSht = Nothing
Set xlsWbk = Nothing
Set xlsApp = Nothing

How many columns can i  sort in excel from access in vba?
When i added coloum 4 with Order4:=xlAscending , gives me error

>>How many columns can i  sort in excel from access in vba
You can only sort up to 3 columns in Excel 2003 and prior.  For Excel 2007 you can sort up to 64 levels.

>>When i added coloum 4 with Order4:=xlAscending , gives me error
You would also need to add Key4, in order to add Order4.  Also, you can't skip Key3 and Order 3 (you would need to add another additional fields for all the ones that end in a number, so also DataOption4).

What is wrong with this code , using Access 2007 and output to Excel 2007

Set xlsws = xlswb.Worksheets("Received")
    xlsapp.Visible = True
    
    With xlsws.Cells
        .Sort Key1:=.Range("A1"), Order1:=xlAscending _
        , Key2:=.Range("B1"), Order2:=xlAscending _
        , Key3:=.Range("C1"), Order3:=xlAscending _
        , Key4:=.Range("D1"), Order4:=xlAscending _
        , Key5:=.Range("E1"), Order5:=xlAscending _
        , Key6:=.Range("F1"), Order6:=xlAscending _
        , Key7:=.Range("G1"), Order7:=xlAscending _
        , Key8:=.Range("H1"), Order8:=xlAscending _
        , Key9:=.Range("I1"), Order9:=xlAscending _
        , Key10:=.Range("J1"), Order10:=xlAscending _
        , Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
        , Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal _
        , DataOption2:=xlSortNormal _
        , DataOption3:=xlSortNormal _
        , DataOption4:=xlSortNormal _
        , DataOption5:=xlSortNormal _
        , DataOption6:=xlSortNormal _
        , DataOption7:=xlSortNormal _
        , DataOption8:=xlSortNormal _
        , DataOption9:=xlSortNormal _
        , DataOption10:=xlSortNormal
        
        
    End With

Select allOpen in new window

getting error at Line 8

Have a look at Patrick's comment (33265277), the key1 - key3 only works, but once you get past that (key4 and on), then you have to switch to the SortFields.Add  structure in his example.

So just modify his code to add in the additional levels of sorting.  If that's not what you want to do, then I would suggest closing this question, as it has already been answered and asking a new question about sort levels in Excel 2007.

mercybthomas74,As broomee9 notes, the approach I outlined in http:#a33265277 is extensible to any number of columns.  It uses the new sort syntax introduced in Excel 2007.The older sort syntax that uses Key1, Key2, Key3, etc is limited to a maximum of three sort keys; to sort on >3 keys using the old syntax requires a little trickery :)For example, to use the old syntax to sort on four columns, you have to do one sort where you use only column 4, and then another sort on the same range using the first three columns.Patrick

tried matthewspatrick : Errors out - method 'apply' of object 'sort' failed in Line 6
IS there any other command to use instead of apply in access 2007 vba code

Please post exactly what you tried.

Here is the code i finally used:

With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B2:B16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("C2:C16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("D2:D16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("A:O")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

Select allOpen in new window