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

DavidH56

New Member
Joined
Jul 29, 2011
Messages
33
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
Someone else asked this recently. I suggested this VBA to print the selected sheets:

Code:
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

New Member
Joined
Jul 29, 2011
Messages
33
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

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Jul 29, 2011
Messages
33
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,666
Members
415,917
Latest member
kungsleden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top