Automating the sort of an excel column with formulas from access

Question

I have an excel spreadsheet that has a column with the formula =IF(L2=N2, IF(N2>0,TRUE, FALSE), FALSE) that gives me a True or False value. If I open this spreadsheet in excel and sort the column ascending it sorts correctly. The macro returned from recording is as follows.

ActiveWorkbook.Worksheets("Incorrect Items").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Incorrect Items").AutoFilter.Sort.SortFields.Add _
    Key:=Range("X1:X2188"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Incorrect Items").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

When I transfer this to access I have.

With WorkSheet
    .AutoFilter.Sort.SortFields.Clear
    .AutoFilter.Sort.SortFields.add _
        Key:=.range("X1:X2188"), SortOn:=0, Order:=1, DataOption:=0   
    With .AutoFilter.Sort
        .Header = 1
        .MatchCase = False
        .Orientation = 1
        .SortMethod = 1
        .Apply
    End With
End With

The code runs fine with no error returned, but it seems to be ignoring the xlSortOnValues constant and does not sort the column. Copying and pasting the values before sorting works but I would like to know why this works in excel and not from access.

I was not saving the workbook after my sort so I tried adding Workbook.Save after the sort, but without results. Your question did prompt me to try something else though so thank you. I was turning calculations to manual as there is more than this small section being run. Putting it back to automatic before the sort fixes the problem, yet I can set calculation to manual and still sort if I open the spreadsheet and do it on my own. — Zaider
Does this relate to solving the question you posted? Sorry, I'm confused. — Smandoli
It does. By turning calculations back to automatic before running the sort code the spreadsheet updates correctly. Interestingly this only seems to be required for sorting a column that contains formulas and columns containing just values work fine. — Zaider
Ah! You may present that as an answer (or HansUp could, of course) and then accept the answer. This would close out the post properly, even perhaps helpfully to someone else. — Smandoli

Smandoli Smandoli · Accepted Answer · 2013-07-04T20:44:36

xlSortOnValues is not handled the same in all Excel versions, so make sure you have the right library active (object references in the VBA editor).

Automating the sort of an excel column with formulas from access

2 Answers