Excel中VBA排序 - 错误从Access 2007的自动化，当 [英] Excel VBA Sort - Error when automated from Access 2007

问题描述

我写了一些code在Excel 2007中的VBA，其中包括一些排序，并能正常工作在Excel中。现在，我试图把它从Access 2007年的工作，自动化Excel。

I wrote some code in Excel 2007 VBA, which includes some sorting, and it works fine in Excel. Now I'm trying to get it to work from Access 2007, automating Excel.

当我得到的应用方法，我得到这个错误：

When I get to the Apply method, I get this error:

排序引用无效。请确保它是你要排序的数据中，和第一排序中是不一样的或空白

"The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank"

但两者的排序键是我要排序的数据中。此外Add方法的关键参数是完全合格的。

But the two sort keys are within the data I want to sort. Also the Key parameters of the Add method are fully qualified.

下面是我的code。首先，这是在Excel中什么工作： （data_sheet是一个工作表参考）

Here's my code. First, this is what works in Excel: (data_sheet is a worksheet reference)

With data_sheet.AutoFilter.Sort
    With .SortFields
        .Clear
        .Add Key:=data_sheet.Cells(2, iColTicker), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=data_sheet.Cells(2, iColTempfieldDate), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
End With

这在Excel工作正常。但我得到这个code从Access 2007运行错误： （data_sheet又是一个工作表的参考，和内置在Excel常量现在明确的常数在Access，返回的值相同）

That works fine in Excel. But I get the error on this code running from Access 2007: (data_sheet is again a worksheet reference, and the built-in Excel constants are now explicit constants in Access, returning the same values)

With data_sheet.Sort
    With .SortFields
        .Clear
        .Add Key:=data_sheet.Cells(1, iColTicker), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
        .Add Key:=data_sheet.Cells(1, iColTempfieldDate), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
    End With
    .Header = XL_YES
    .MatchCase = False
    .Orientation = XL_TOP_TO_BOTTOM
    .Apply
End With

我试过完全限定内有了说法，但得到了同样的错误：

I tried fully qualifying the inner With statement, but got the same error:

With data_sheet.Sort
    With data_sheet.Sort.SortFields
        .Clear
        .Add Key:=data_sheet.Cells(1, iColTicker), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
        .Add Key:=data_sheet.Cells(1, iColTempfieldDate), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
    End With
    .Header = XL_YES
    .MatchCase = False
    .Orientation = XL_TOP_TO_BOTTOM
    .Apply
End With

我也试图消除with语句完全：

I also tried removing the With statements entirely:

data_sheet.Sort.SortFields.Clear
data_sheet.Sort.SortFields.Add Key:=data_sheet.Cells(2, iColTicker), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
data_sheet.Sort.SortFields.Add Key:=data_sheet.Cells(2, iColTempfieldDate), SortOn:=XL_SORT_ON_VALUES, Order:=XL_ASCENDING, DataOption:=XL_SORT_NORMAL
data_sheet.Sort.Header = XL_YES
data_sheet.Sort.MatchCase = False
data_sheet.Sort.Orientation = XL_TOP_TO_BOTTOM
data_sheet.Sort.Apply

在所有情况下，我上线应用的错误。它不是一个VBA误差;这是一个弹出对话框。然后它忽略了过程和错误我的错误处理出来的调用过程中

In all cases I get the error on the Apply line. It isn't a VBA error; it's a pop-up dialog. And then it ignores my error handler in that procedure and errors out in the calling procedure.

任何建议？

谢谢

格雷格

更新：

我试图关闭工作簿，退出Excel中，实例的Excel的新实例，并重新打开工作簿，排序前右，像这样的：

I tried closing the workbook, quitting Excel, instantiating a new instance of Excel, and reopening the workbook, right before the sorting, like this:

Dim xlApp As Excel.Application, wbDashboard As Workbook
Dim strDatasheetName As String, strDatasheetWorkbookName As String, strDatasheetWorkbookPath As String
strDatasheetName = data_sheet.Name
strDatasheetWorkbookName = data_sheet.Parent.Name
strDatasheetWorkbookPath = data_sheet.Parent.Path
Set xlApp = data_sheet.Parent.Parent

data_sheet.Parent.Close SaveChanges:=True
    Set data_sheet = Nothing: DoEvents
xlApp.Quit
    Set xlApp = Nothing: DoEvents
Set xlApp = CreateObject("Excel.Application")
Set wbDashboard = xlApp.Workbooks.Open(strDatasheetWorkbookPath & "\" & strDatasheetWorkbookName)
wbDashboard.Activate: DoEvents
DoEvents
Set data_sheet = wbDashboard.Worksheets(strDatasheetName)
data_sheet.Activate: DoEvents
DoEvents

不过，我这样做后，得到了同样的错误（在排序的同一应用线）。

But I got the same error (on the same Apply line of the sorting) after doing that.

格雷格

推荐答案

@KazJaw：谢谢，使用旧式排序（Range.Sort）工作。这是我的code现在：

@KazJaw: Thanks, using the old style sorting (Range.Sort) worked. Here's my code now:

data_sheet.Cells.Sort _
    Key1:=data_sheet.Cells(2, iColTicker), Order1:=XL_ASCENDING, _
    Key2:=data_sheet.Cells(2, iColTempfieldDate), Order2:=XL_ASCENDING, _
    Header:=XL_YES, MatchCase:=False, Orientation:=XL_TOP_TO_BOTTOM, _
    DataOption1:=xlSortNormal, DataOption2:=XL_SORT_NORMAL

现在我必须去就打我的头侧没有想到的是自己！ ： - ）

Now I must go slap the side of my head for not thinking of that myself! :-)

格雷格

这篇关于Excel中VBA排序 - 错误从Access 2007的自动化，当的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！