Run-time error '1004'

  • Thread starter Thread starter Shoe
  • Start date Start date
S

Shoe

I am using Access to sort rows in an Excel spreadsheet.
The code works sometimes and others the same code gives me the error 1004:
Method 'Range' of object '_Global' failed.

Below is the code I am using.
I do not understand why sometimes the code works and other times it does not.

Sub sort_ytd()
Dim bookExcel As Excel.Workbook
Dim sheetExcel As Excel.Worksheet


Dim strNewBookName As String
strNewBookName = "C:\Temp\SBB_Sales_and_Svc_Scorecard_200806.xls"

Set bookExcel = GetObject(strNewBookName)
Set sheetExcel = bookExcel.Worksheets(1)
With sheetExcel
.Range("C12:H20").Sort Key1:=Range("D12"), Order1:=xlDescending,
Header:=xlYes _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End With

Set bookExcel = Nothing
Set sheetExcel = Nothing

End Sub
 
I found the issue.
The following portion of the code:
Range("D12"),
Should be
sheetExcel.Range("D12"),
 
Back
Top