PREREQUISITES
You must have Excel 2007 or higher.
INTRODUCTION
We receive this question fairly often because people create Excel spreadsheets using Office Buddy and then want to publish them as PDF reports. Fortunately as of Excel 2007, it's actually fairly easy to do with a macro.
STEPS
You'll need to do 3 things.
- Save your Excel template workbook as a Macro-Enabled Workbook with an extension of .xlsm. This is done in the File > Save As menu. Choose the Macro Enabled type in the Save As Type drop down list.
- Create the macro called CreatePDF using the following code:
Sub CreatePDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Prolink\SPC Office Buddy 3.4\Report\Book1.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True
End Sub
The code above will save only the Active Sheet to PDF. If you have a multiple worksheet workbook and want to save the entire thing to a multipage PDF, use the following code to do so. Notice the only difference is the first word in the code (ActiveSheet vs. ActiveWorkbook). The other option you will notice in the code is the
OpenAfterPublish=True. If you leave this as True, Windows will launch the PDF in your default PDF viewer (likely Adobe Acrobat). If you change this value to False, it will not be automatically opened.
Sub CreatePDF()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Prolink\SPC Office Buddy 3.4\Report\Book1.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True
End Sub
- In the Excel Job, check the Show Advanced checkbox on the Excel Options screen. Then type the name of the macro into the Run Macro After field on the Advanced screen. Choose Finish to save your changes.
CONCLUSION
That should be all that is necessary to automatically export your Excel Jobs to PDF from Office Buddy.