Page 1 of 1

Excel Automation error. The object invoked has disconnected

Posted: Sat Aug 08, 2009 5:53 pm
by RobWEllis
Hi,

I have a routine in Excel which creates a PDF report of a few sheets within a workbook.

Most of the time it works fine but on one machine (XP SP3 and Excel 2003 SP3) it fails with "Automation error. The object invoked has disconnected " on the Sheets.PrintOut line.

I've included the code below in case it helps. Nothing special except that I use CreateObject("BullZip.PDFPrinterSettings") rather than having a reference to the BullZip dll because some of our machines don't have bullzip installed.

Thanks for looking!

Rob

[Code]
Function PrintToPDF(szSheetsToPrint() As String) As Boolean
PrintToPDF = False

Rem -- Get the full name of the printer
Dim szPDFPrinterName As String
szPDFPrinterName = GetFullNetworkPrinterName(FindPrinter("BullZip"))
If Len(szPDFPrinterName) > 0 Then
' Create the object to control the printer settings
On Error Resume Next
Dim oPrinterSettings As Object
Set oPrinterSettings = CreateObject("bullzip.PDFPrinterSettings")
If Err = 0 Then
' Write the settings to the printer. Settings are written to the runonce.ini whic is deleted immediately after being used.
szGeneratedFileName = ReplaceFileExtension(ActiveWorkbook.FullName, "pdf")
With oPrinterSettings
.setValue "output", szGeneratedFileName
.setValue "showsettings", "never"
.setValue "showpdf", "never"
.WriteSettings True
End With

' Delete file if it already exists
On Error Resume Next
Kill szGeneratedFileName
On Error Resume Next

' Change to PDF printer, print and then change back again
Dim szCurrentPrinter As String
szCurrentPrinter = ActivePrinter
ActivePrinter = szPDFPrinterName
Sheets(szSheetsToPrint).PrintOut
ActivePrinter = szCurrentPrinter
PrintToPDF = (Err <> 0)
End If
End If
End Function
[/Code]