Greetings. I am getting a peculiar error (in my simple mind) sporatically with some code. I am attempting to take an Excel report, via Access VBA and format it according to a customer's specifications. The code seems to work fine when Excel is not open previously, but if the user has an Excel document open and then runs the Access process, I get the error in the subject line. Here is my code:
There is more code following this but the error occurs in the line "obj_XL_Sheet.Range(Selection, Selection.End(xlDown)).Select" However it runs successfully all the way through if Excel is no opened before. Arg. Any ideas why this might be happening and how to fix it?
Kelly
Code:
Public Sub FormatDailyShipmentReport(strPath As String)
Dim obj_XL_App As Excel.Application
Dim obj_XL_Wkbk As Excel.Workbook
Dim obj_XL_Sheet As Excel.Worksheet
Dim blnSpawned As Boolean
On Error Resume Next
Set obj_XL_App = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set obj_XL_App = CreateObject("Excel.Application")
blnSpawned = True
End If
On Error GoTo 0
'Hide the opening workbook if grabbing an open instance of Excel
If blnSpawned Then
obj_XL_App.ScreenUpdating = False
obj_XL_App.DisplayAlerts = False
End If
Set obj_XL_Wkbk = obj_XL_App.Workbooks.Open(strPath)
Set obj_XL_Sheet = obj_XL_Wkbk.Sheets(1)
'Format LATE rows in red colored text
obj_XL_Sheet.Range("$A:$Z").AutoFilter
obj_XL_Sheet.Range("$A:$Z").AutoFilter Field:=21, Criteria1:="LATE"
obj_XL_Sheet.Range("$A2:$Z2").Select
obj_XL_Sheet.Range(Selection, Selection.End(xlDown)).Select
With obj_XL_App.Selection.Font
.Color = -16776961
.TintAndShade = 0
End WithThere is more code following this but the error occurs in the line "obj_XL_Sheet.Range(Selection, Selection.End(xlDown)).Select" However it runs successfully all the way through if Excel is no opened before. Arg. Any ideas why this might be happening and how to fix it?
Kelly
