Excel VBA - Repeated sorting causes Runtime Error '1004'

[KeithRoberts]

I am using MS Project VBA to create a status spreadsheet in Excel. I am receiving a runtime error '1004' Method 'Range' of object '_Global' failed the second time I run the status report.

I have created a form that creates the spreadsheet. If I comment out the sort code, I can run the application multiple times with no problems. However, whenever I try to sort, it abends with that error. I have tried two different sort syntaxes with the same result:

'xlApp.Selection.Sort Key1:=Range("H7"), _
' Order1:=xlDescending, _
' Key2:=Range("A7"), _
' Order2:=xlAscending, _
' Header:=xlNo, _
' OrderCustom:=1, _
' MatchCase:=False, _
' Orientation:=xlTopToBottom, _
' DataOption1:=xlSortNormal, _
' DataOption2:=xlSortNormal

xlApp.Worksheets(sSheetName).Range(MyRange).Sort
Key1:=Range("H7"), _
Order1:=xlDescending, _
Key2:=Range("A7"), _
Order2:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

It should be noted that I am using late binding (Dim xlApp As Object) and then use the CreateObject("excel.application"). I changed to this strategy as Excel will not quit after the form is closed, but this is an issue for a later time.

Any information would be greatly appreciated.

 

[KeithRoberts]

I finally figured it out. During the process of a late bind and the sort statement, the enumerated values used in the sort statement prevented Excel from closing and instead, hanging out as an orphan process. The original sort statement was:

xlApp.Worksheets(sSheetName).Range(MyRange).Sort
Key1:=Range("H7"), _
Order1:=xlDescending, _
Key2:=Range("A7"), _
Order2:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

I changed it to:
xlApp.Worksheets(sSheetName).Range(MyRange).Sort _
Key1:=xlApp.Worksheets(sSheetName).Range("H7"), _
Key2:=xlApp.Worksheets(sSheetName).Range("A7"), _
Order1:=2, _
Order2:=1

As can be seen, I had to remove the header and all keywords below in order to allow Excel to close and quit without leaving a hanging process.

I hope that this may help someone else in the future.

 

[RoryA]

The constants would not be the problem - it was the unqualified Range statements that were the issue. You could define those constants and put back the additional arguments if required.

 

[KeithRoberts]

Thank you for your response. However, the code with the constants would leave Excel running after a quit was performed. When I commented out the sort command, Excel quit gracefully. When I changed the constants to 1 or 2, Excel quit gracefully. Please note that I did try using the constants with the fully qualified range statements and Excel would still be left as a running process after performing am xlApp.Quit.

However, it is now working as expected and that is what is important.

 

[RoryA]

Did you have the Excel application visible?

 

[KeithRoberts]

No, it is not visible.

 

[billuk]

Thanks for raising this - I had same problem and it was down to not qualifying the key used in the sort range. I was just changing the sort range itself.