VBA to create PDF, getting error "automation error the object invoked has disconnected from its client"

[DavidH56]

I'm writing a macro to print several worksheets in the same workbook to a single PDF file. I get the "automation error the object invoked has disconnected from its client" error message when I run the code below. The codes works until the Activesheet line then errors out (the message boxes are just for validation and will be deleted).

I originally had the OpenAfterPublish parameter set to "true" but changing it to "false" had no impact--still get the error.

Is there a way around this error or another way to create the pdf? I'm using Excel 2016 on Windows 10; to manually print to a PDF I use Microsoft Print to PDF.

Thanks in advance for the help!

Sub PrintToPDF()

Path = CreateObject("WScript.Shell").specialfolders("MyDocuments")
MsgBox Path

Filename = Path & "\Fuel_Report_" & Format(Date, "yyyymmdd") & ".pdf"
MsgBox Filename

Worksheets(Array("FormA_CA", "FormB_CA", "FormC_CA", "FormA_RE", "FormB_RE", "FormC_RE")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

 

[kweaver]

Someone else asked this recently. I suggested this VBA to print the selected sheets:

Sub SaveActiveSheetsAsPDF()

Dim saveLocation As String
saveLocation = "C:\Users\etc\FileName.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

 

[DavidH56]

Thanks. I modified my code as you suggested but still get the same error. Here's my revised code:

Sub PrintToPDF()
Path = CreateObject("WScript.Shell").specialfolders("MyDocuments")
MsgBox Path
saveLocation = Path & "\Fuel_Report_" & Format(Date, "yyyymmdd") & ".pdf"
MsgBox saveLocation

Worksheets(Array("FormA_CA", "FormB_CA", "FormC_CA", "FormA_RE", "FormB_RE", "FormC_RE")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

End Sub

 

[kweaver]

I ran this on a sample workbook of my own with two sheets and there was no problem/error creating the PDF.
I did write: DIM saveLocation As String, Path As String however.
Where was your error and what was the error?

 

[DavidH56]

The error I get is "automation error the object invoked has disconnected from its client" and I get it on the last line. Good idea for the DIM, let me try that and see if it helps.

Thanks,

David