Oject does not support automation.

[Jables]

I am getting the error in the subject line when I execute the following function. I marked the line the debugger is stopping on.

Public Sub ExportToExcel(rptType As Integer, _
    JobID As String, _
    startdate As String, _
    enddate As String)

On Error Resume Next 'Turn off error handling.

    Dim objXL As Excel.Application
    Dim objWKB As Excel.Workbook
    Dim objSHT As Excel.Worksheet
    Dim rs As ADODB.Recordset
    Dim strFileName As String
    Const conSHEET1 = "Sheet1"  'name of your first worksheet
    Const ConWKBK = "\\Server\Projects_2\Office Docs\Template - Summary Reports.xlt" 'full path to my template file
    
    Set objXL = New Excel.Application
    
    With objXL
        .Visible = True
        Set objWKB = .Workbooks.Open(ConWKBK)
    End With
    
    'Populate worksheet
    Set rs = DefineRecordset(rptType, JobID, startdate, enddate)
    If Not rs.EOF Then 'Make sure the recordset isn't empty
        Set objSHT = objWKB.Worksheets(conSHEET1)
        
           
        objSHT.Range("A6").CopyFromRecordset rs '***DEBUGGER STOPS HERE***
        objSHT.Range("A1").value = GetJobName(JobID)
        
        'Format the date column if rpttype = 5
        If rptType = 5 Then
            objSHT.Range("B:B").Cells.NumberFormat = "mm/dd/yy"
        End If
        
        'Make sure range that appears in spreadsheet isn't goofy.
        If startdate = "01/01/1900" Then
            objSHT.Range("A2").value = "Time and mileage from beginning of project until " & Date
        Else
            objSHT.Range("A2").value = "Time and Mileage From " & startdate & " to " & enddate
        End If
        
        objSHT.Range("A3").value = "Summary created on: " & Now()
        
        strFileName = RptFileName(rptType, JobID)
        objSHT.SaveAs strFileName
        'objXL.Application.Quit
    Else
        objXL.Visible = False
        MsgBox "There is no data for " & GetJobName(JobID) & " for the selected dates." & vbCrLf & _
            "A report has not been created."
    End If
    
    Set objSHT = Nothing
    Set objWKB = Nothing
    Set objXL = Nothing
    rs.Close

End Sub

This function runs fine on every computer in my office except my boss's laptop. He is running the same version and build of Windows and MS Office (Win2k and MSOffice2kPro) as everyone else. His computer has all the same references in the VBA IDE. I can't figure out what is causing the error. Any ideas.

 

[Jables]

I was in a hurry earlier, and didn't have enough time to go into any detail about what this function does. After reading my post, I realized it didn't really offer any information conducive to troubleshooting.

So here goes:

I use the function to open a recordset of data from my Access database, open an instance of Excel, and copy the data into the Excel spreadsheet.

Things happen in the following order:

1. Open an instance of Excel.
2. Excel opens a .xlt file that is the template for my spreadsheet.
3. Define ADO recordset based on some variables.
4. Make sure the recordset isn't empty.
5. Copy the data from the recordset into the spreadsheet using the CopyFromRecordset method.
6. Generate a Filename for the spreadsheet.
7. Save the spreadsheet.

This has been working for months without a hitch. Then my boss got a new laptop and when I try to run the function from his computer, it crashes on the line that is marked in my first post with the error, "Object does not support Automation."

His computer is running the same build and version of Windows and Office as everyone elses computer in the office and all the references are the same. I don't understand what is going on.