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.

---

xlSortOnValues在所有Excel版本中的处理方式不同，因此请确保您拥有正确的库（VBA编辑器中的对象引用）。

---

当从MS Access中自动化MS Excel并处理公式时，例如对具有从公式创建的值的列进行排序或复制公式中的和值结果时，必须将计算设置为自动以观察正确的结果appExcel.Application.Calculation = -4105 。

链接地址: http://www.djcxy.com/p/60784.html

上一篇: 在Excel中排序公式

下一篇: 使用来自访问的公式自动化Excel列