Introduction:
This article contains code that can be used to print worksheets or entire workbooks to a PDF file, using Microsoft's free Save as PDF or XPS add-in for Office 2007. These routines will NOT work in versions of Office prior to 2007. If you are running an earlier version of Office, and are running on Windows XP, then have a look at my PDF Creator articles.
Versions Tested:
These routines have been tested successfully using the following versions of the software:
Operating Systems:
- Windows Vista Ultimate 64 bit edition. (Should work fine in 32 bit edition as well.)
Excel versions tested include:
- Excel 2007
Code Samples
The first code sample will print the active sheet(s) to a single PDF file. (To select multiple sheets, hold down the CTRL key and click each sheet tab. Running this code will then print all selected sheets into a single PDF file.)
NOTE: In any of the routines below, to have Adobe Acrobat automatically open after printing to file, change the value of "OpenAfterPublish" to True. This is a handy way to review the output of the PDF, allowing you to make sure it was created correctly.
Sub SaveToPDF()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print active sheet(s) to PDF file using Microsoft's 2007 Add-in
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:TestTest.pdf", _
OpenAfterPublish:=False
End With
End Sub
The next sample uses code to specifically print two worksheets to a single PDF file.
Sub SaveSpecificToPDF()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print specific sheets to PDF file using Microsoft's 2007 Add-in
Sheets(Array("Sheet1", "Sheet3")).Select
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:TestTest.pdf", _
OpenAfterPublish:=False
End With
End Sub
This code sample will save the entire workbook to a PDF.
Sub SaveFileToPDF()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print entire workbook to PDF file using Microsoft's 2007 Add-in
ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:TestTest.pdf", _
OpenAfterPublish:=False
End Sub
This sample will prompt the user for a location to save the active worksheet as a PDF.
Sub SaveFileToPDF_Prompt()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Save the file as a PDF to a location provided by the user at runtime
' using Microsoft's 2007 Add-in
Dim vPDFPath As Variant
Do
'Collect output file name and test if valid
bRestart = False
vPDFPath = Application.GetSaveAsFilename(, "PDF Files (*.pdf), *.pdf")
'Check if user cancelled
If CStr(vPDFPath) = "False" Then
Exit Sub
Else
lAppSep = InStrRev(vPDFPath, Application.PathSeparator)
End If
'Check if file exists, as PDFCreator will not overwrite
If UCase(Dir(vPDFPath)) = UCase(Right(vPDFPath, Len(vPDFPath) - lAppSep)) Then
Select Case MsgBox("File already exists. Would you like to overwrite it?", _
vbYesNoCancel, "Destination file exists!")
Case vbYes
Kill vPDFPath
Case vbNo
bRestart = True
Case vbCancel
Exit Sub
End Select
End If
Loop Until bRestart = False
'Save the file as a PDF
ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=vPDFPath, _
OpenAfterPublish:=False
End Sub
