Jeff McClellan

asked on 4/20/2020

Force Excel worksheet to save as PDF prior to printing

I have a price list on an Excel worksheet. It has a defined print area, custom Header and Footer.

How can I automate this sequence when someone prints the worksheet?

1. Auto-save a copy of the active worksheet print area as a PDF in the folder the parent Excel file resides. Prefix the PDF file name with YYYYMMDD

2. Open it in Acrobat

Adobe AcrobatPDFMicrosoft Excel

Last Comment

Jeff McClellan

8/22/2022 - Mon

byundt

4/20/2020

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim flPath As String, flName As String, flRoot
flName = ThisWorkbook.Name
flPath = ThisWorkbook.FullName
flPath = Left(flPath, Len(flPath) - Len(flName))
flRoot = Left(flName, InStrRev(flName, ".")) & "pdf"
flRoot = Format(Date, "yyyymmdd") & " " & flRoot
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=flPath & flRoot, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'Cancel = True      'This statement stops the worksheet from printing
End Sub

Open in new window

Put the above code in ThisWorkbook code pane.

I believe it will do what you requested. It did not do so in my testing, however. I suspect a problem with my Office Insider version of Excel, as my file folder was littered with about 30 .tmp files and the row height increased on the display.

Jeff McClellan

4/20/2020

ASKER

Nope. Did not save on mine either. Office 365

byundt

4/20/2020

Where did you put the code? It must go in ThisWorkbook code pane, and will not work at all if you put it anywhere else.

Also, you must save the file with .xlsm file extension and enable macros when you open the workbook.

FWIW, I had no trouble with the file saving as .pdf. My problem was when the macro tried to print afterwards.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Jeff McClellan

4/20/2020

ASKER

Put the code in ThisWorkbook.
Saved the Excel file with the xlsm extension.
File prints, but no PDF generated.
Macro generates 57 tmp files of 0 KB.
Macro throws the error below. When I click Debug or End, Excel closes and restarts.
(see attached screenshot)

Robberbaron (robr)

4/23/2020

needs testing.
calling the ExportAsFixedFormat actually triggers the Workbook_BeforePrint, causing an infinite loop.

Triggered my interest as well so will try to resolve.

SOLUTION

Robberbaron (robr)

4/23/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

byundt

4/23/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Jeff McClellan

4/23/2020

ASKER

Thank you both for the excellent and skilled advice. Works perfectly. I'm very grateful to have found you through Experts Exchange! I hope I can have access to your input on future projects. Sincerely, Jeff

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.