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
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