[RESOLVED] Excel (automation) from Access - Using Sort

[Ecniv]

Hi,

Seems that the old sort methods have changed from 2003 to 2010, and I've almost got the new one to work. But it stumbles on the last line..

        sht.Sort.sortfields.Clear
        'SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=         xlSortNormal
        Set rng = sht.Range(sht.cells(lngStartRow, 1), sht.cells(lngRowOp, 1))
        sht.Sort.sortfields.Add rng, SortOn:=0, Order:=1, DataOption:=0
        Set rng = sht.Range(sht.cells(lngStartRow, 2), sht.cells(lngRowOp, 2))
        sht.Sort.sortfields.Add rng, SortOn:=0, Order:=1, DataOption:=0
        
        Set rng = sht.Range(sht.cells(lngStartRow, 2), sht.cells(lngRowOp, lngMaxCol))
        With sht.Sort
            .SetRange rng
            .Header = 1 'xlYes
            .MatchCase = False
            .Orientation = 1 'xlTopToBottom
            .SortMethod = 1 'xlPinYin
            .Apply
        End With

Errors with the generic 1004 msg.

I am automating from Access to Excel.
I don't understand why it cannot apply the sort... When you go into the xlsx afterwards, the sorting is there ready to apply and works after applying it on the button.


I have stopped screenupdate and displaying errors - the worksheet/book is usually hidden until I'm ready for it to be there.


Has anyone come across this or knows how to fix it?

[Ecniv]

Forget it - seems you need two more things.


Firstly, after adding in the sort ranges (individiual ranges) before you apply the sort, you need to reselect the whole range to sort via the entered ranges.

Secondly, the range to sort on must include the ranges you added to sort on.

The display alerts displays a more useful alert to decipher this!

sht.Sort.sortfields.Clear
        'SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=         xlSortNormal
        Set rng = sht.Range(sht.cells(lngStartRow, 1), sht.cells(lngRowOp, 1))
        sht.Sort.sortfields.Add rng, SortOn:=0, Order:=1, DataOption:=0
        Set rng = sht.Range(sht.cells(lngStartRow, 2), sht.cells(lngRowOp, 2))
        sht.Sort.sortfields.Add rng, SortOn:=0, Order:=1, DataOption:=0
        
        Set rng = sht.Range(sht.cells(lngStartRow, 1), sht.cells(lngRowOp, lngMaxCol))
        rng.Select
        With sht.Sort
            .SetRange rng
            .Header = 1 'xlYes
            .MatchCase = False
            .Orientation = 1 'xlTopToBottom
            .SortMethod = 1 'xlPinYin
            .Apply
        End With