CODEWITHSUNDEEP
excelms-accessvbams-access-2007
Zaider
Zaider

Reputation: 2013

Automating the sort of an excel column with formulas from access

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.

Upvotes: 1

Views: 1414

Answers (2)

Zaider
Zaider

Reputation: 2013

When automating MS Excel from MS Access and dealing with formulas such as sorting on a column that has values created from a formula or copying the results of sums from formulas then calculations must be set to automatic to observe the correct results appExcel.Application.Calculation = -4105.

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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).

Upvotes: 1

Related Questions