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

Your description sounds almost like a compile error instead of a run time error. Have you included Option Explicit in the Declarations section of your code module and then run Debug->Compile from the VB Editor's main menu? — HansUp
Hi HansUp, I do have Option Explicit at the top of the module. I tried your suggestions of Debug->Compile, and it seemed to compile (no errors, and the menu choice became grayed out), but I got the same error. — Greg Lovern
without jumping deeply into your code and without testing it I would suggest using different sorting method for Range object, so, TRY THIS, without applying Autofilter. — Kazimierz Jawor
@GregLovern: Greg, can you show the complete code. Including the constant declarations and value assignment. Same goes the workbook/worksheet objects. Your code is incomplete and hence it is really difficult to tell you what could be going wrong... — Siddharth Rout

Greg Lovern Greg Lovern · Accepted Answer · 2013-12-09T17:21:27

@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

Excel VBA Sort - Error when automated from Access 2007

1 Answers