Need help w/ function that opens Excel.

[ssmith001]

I am using this subroutine which uses the function shown below. What I have found in my testing is that when I do not already have Excel open and I try to run the code, I get an ActiveX error, but when I simply open Excel before running this, I don't. Can someone tell me what the cause of this is? I don't want to have to open Excel before running, I want the Access app to open the appropriate spreadsheet and do its work.

Sub cmdRunWeeklyDealer_Click()
On Error GoTo Err_cmdRunWeeklyDealer_Click
    Dim XL As Object
    Dim XLOpen As Boolean
        
    XLOpen = False
    
    If OpenExcelOK(XL, "C:\Documents and Settings\steve r smith\Desktop\WORK FILES\SWR 1124 - DAF Global Reporting\Dealer Report Macro DAF.xls") Then
        XLOpen = True
        XL.Run ("CreateReports_DealerWeekly")
        XL.Quit
    End If
    
    
Exit_cmdRunWeeklyDealer_Click:
    Exit Sub
    
    
Err_cmdRunWeeklyDealer_Click:
    MsgBox Err.Description
    If XLOpen Then XL.Quit
    Resume Exit_cmdRunWeeklyDealer_Click
    
End Sub

Function OpenExcelOK(ByRef oExcel As Object, Optional ByVal FileToOpen As String = "") As Boolean
    
    OpenExcelOK = False
    On Error Resume Next
    
    Set oExcel = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Err.Clear
        Set oExcel = CreateObject("Excel.application")
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Could not open Excel!", vbCritical
        Exit Function
    End If
    
    If FileToOpen = "" Then
        oExcel.Workbooks.Add
    Else
        oExcel.Workbooks.Open (FileToOpen)
        If Err.Number <> 0 Then
            MsgBox "Could not open workbook '" & FileToOpen & "' !", vbCritical
            If oExcel.Workbooks.Count = 0 Then oExcel.Quit
            Exit Function
        End If
    End If
    
    
    On Error GoTo 0
    OpenExcelOK = True
End Function

 

[boblarson]

Is there a reason why you can't just use a new Excel object and not worry about whether Excel is open or not?

 

[pdx_man]

Exactly what I was thinking, Bob.