Excel VBA Sort - Error when automated from Access 2007

Question

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"

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

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

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

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

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.

Any suggestions??

Thanks,

Greg

UPDATE:

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.

Greg

Answer 1

@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! :-)

Greg