Link to home
Start Free TrialLog in
Avatar of Jeff McClellan
Jeff McClellanFlag for United States of America

asked on

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
Avatar of byundt
byundt
Flag of United States of America image

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.
Avatar of Jeff McClellan

ASKER

Nope.  Did not save on mine either.  Office 365
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.
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)User generated image
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
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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