Convert Excel To PDF Using VBA - The Only Guide You Will Need

*This is a guest post by Ryan Wells, a fellow blogger, and a brilliant VBA developer.*

If you work with Excel, you undoubtedly have been asked to create summaries of your work.

Sales reports, invoices, forecasts, schedules, you name it.

You know what all these documents have in common? They look great in PDF form. You know what else? Management loves PDFs!

This Tutorial Covers:

Toggle

• Convert Excel to PDF
  • Print Selection To PDF
  • Print One Table To PDF
  • Print All Tables To Separate PDFs
  • Print All Sheets To One PDF
  • Print Chart Sheets To PDF
  • Print Chart Objects To PDF

Convert Excel to PDF

In this tutorial, I’ll show you how to use Excel VBA to convert all kinds of Excel objects to PDFs:

I’m going to present each of the macros with some commentary. That way, you’ll be able to quickly find them, copy them to your VBA editor and use them.

When you run any of these macros, a Save As dialog box will appear asking you where you want to save your PDF. The default name includes the date and timestamp when you executed the macro in yyyymmdd_hhmmss format.

Let’s get right to it.

Here are the macros:

Print Selection To PDF

This one is my personal favorite. This macro will convert the cells you actively have selected into a PDF.

If you only have one cell selected, the VBA macro is smart enough to realize that you probably don’t want to convert just one cell so it asks you to select the range you want to convert:

Sub PrintSelectionToPDF() 'SUBROUTINE: PrintSelectionToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Print your currently selected range to a PDF Dim ThisRng As Range Dim strfile As String Dim myfile As Variant If Selection.Count = 1 Then Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8) Else Set ThisRng = Selection End If 'Prompt for save location strfile = "Selection" & "_" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" strfile = ThisWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> "False" Then 'save as PDF ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True Else MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If End Sub

Once you select the range and click OK, it will show a dialog box where you can select where you want to save the PDF.

It will automatically pick the date and time from your system’s clock and add it to the file name.

Print One Table To PDF

A lot of Excel power users store their data into organized tables. As a matter of fact, Sumit Bansal himself likes Excel tables so much he calls them a hidden treasure in Excel.

This macro prints a table of your choosing to a PDF. When you run the macro, it will prompt you for the name of the table you want to save.

Sub PrintTableToPDF() 'SUBROUTINE: PrintTableToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Print a table of your choosing to a PDF Dim strfile As String Dim myfile As Variant Dim strTable As String, r As Range Application.ScreenUpdating = False 'Enter the table name you want to save strTable = InputBox("What's the name of the table you want to save?", "Enter Table Name") 'Table you want to save If Trim(strTable) = "" Then Exit Sub 'Prompt for save location strfile = strTable & "_" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" strfile = ThisWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> "False" Then 'save as PDF Range(strTable).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True Else MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If Application.DisplayAlerts = False LetsContinue: With Application .ScreenUpdating = True .DisplayAlerts = True End With Exit Sub End Sub

Once you enter the name of the table and click OK, it will show a dialog box where you can select where you want to save the PDF.

It will automatically pick the date and time from your system’s clock and add it to the file name.

Print All Tables To Separate PDFs

If your spreadsheet has several tables and you need to save each one to a separate PDF, you can run this VBA code.

When you run this macro, a dialog box will appear asking you to select the folder where you want to save your PDFs.

Once you pick your folder, the macro will save each table to a PDF with the table name conveniently appearing in the title of the PDF.

Sub PrintAllTablesToPDFs() 'SUBROUTINE: PrintAllTablesToPDFs 'DEVELOPER: Ryan Wells 'DESCRIPTION: Print each table in your spreadsheet to a different PDF Dim strTables() As String Dim strfile As String Dim ch As Object, sh As Worksheet Dim icount As Integer Dim myfile As Variant Dim tbl As ListObject Dim sht As Worksheet With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Where do you want to save your PDF?" .ButtonName = "Save Here" .InitialFileName = ThisWorkbook.Path If .Show = -1 Then ' if OK is pressed sfolder = .SelectedItems(1) Else End End If End With For Each sht In ThisWorkbook.Worksheets For Each tbl In sht.ListObjects myfile = ThisWorkbook.Name & "" & tbl.Name & "" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" myfile = sfolder & "\" & myfile sht.Range(tbl.Name).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True Next tbl Next sht End Sub

Print All Sheets To One PDF

I don’t know about you, but in my line of work, we have to retain PDF copies of almost all our spreadsheets. We append the PDF copies of our spreadsheets to our design calculations. These PDFs used to be converted to microfiche and vaulted for long-term retention. You know, in case the apocalypse happened.

If you find yourself in a similar situation, it’s nice to be able to automatically convert all the sheets in your spreadsheet into one PDF. Here’s a VBA macro that will do just that:

Sub PrintAllSheetsToPDF() 'SUBROUTINE: PrintAllSheetsToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Combine all your worksheets into one PDF Dim strSheets() As String Dim strfile As String Dim sh As Worksheet Dim icount As Integer Dim myfile As Variant 'Save Chart Sheet names to an Array For Each sh In ActiveWorkbook.Worksheets If sh.Visible = xlSheetVisible Then ReDim Preserve strSheets(icount) strSheets(icount) = sh.Name icount = icount + 1 End If Next sh If icount = 0 Then 'No charts found. Punch error MsgBox "A PDF cannot be created because no sheets were found.", , "No Sheets Found" Exit Sub End If 'Prompt for save location strfile = "Sheets" & "_" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" strfile = ThisWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> "False" Then 'save as PDF ThisWorkbook.Sheets(strSheets).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True Else MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If End Sub

Print Chart Sheets To PDF

This macro converts all your Chart Sheets into one PDF – but not your chart objects! By chart sheets, I mean the charts that have their own tab in your list of spreadsheet tabs:

Sub PrintChartSheetsToPDF() 'SUBROUTINE: PrintChartSheetsToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Combine all chart sheets into one PDF Dim strSheets() As String Dim strfile As String Dim ch As Object, sh As Worksheet Dim icount As Integer Dim myfile As Variant 'Save Chart Sheet names to an Array For Each ch In ActiveWorkbook.Charts ReDim Preserve strSheets(icount) strSheets(icount) = ch.Name icount = icount + 1 Next ch If icount = 0 Then 'No charts found. Punch error MsgBox "A PDF cannot be created because no Chart Sheets were found.", , "No Chart Sheets Found" Exit Sub End If 'Prompt for save location strfile = "Charts" & "_" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" strfile = ThisWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> "False" Then 'save as PDF ThisWorkbook.Sheets(strSheets).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True Else MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If End Sub

Print Chart Objects To PDF

This macro saves all your normal charts – chart objects – into a single PDF. Regardless of which worksheet your chart is on, it will be grabbed and saved to a PDF.

There will only be one chart per page in the final PDF.

Sub PrintChartsObjectsToPDF() 'SUBROUTINE: PrintChartsObjectsToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Combine all chart objects into one PDF Dim ws As Worksheet, wsTemp As Worksheet Dim chrt As ChartObject Dim tp As Long Dim strfile As String Dim myfile As Variant Application.ScreenUpdating = False Set wsTemp = Sheets.Add tp = 10 With wsTemp For Each ws In ActiveWorkbook.Worksheets If ws.Name = wsTemp.Name Then GoTo nextws: For Each chrt In ws.ChartObjects chrt.Copy wsTemp.Range("A1").PasteSpecial Selection.Top = tp Selection.Left = 5 If Selection.TopLeftCell.Row > 1 Then ActiveSheet.Rows(Selection.TopLeftCell.Row).PageBreak = xlPageBreakManual End If tp = tp + Selection.Height + 50 Next nextws: Next ws End With 'Prompt for save location strfile = "Charts" & "_" _ & Format(Now(), "yyyymmdd\_hhmmss") _ & ".pdf" strfile = ActiveWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> False Then 'save as PDF wsTemp.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'Else ' MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If Application.DisplayAlerts = False wsTemp.Delete With Application .ScreenUpdating = True .DisplayAlerts = True End With Exit Sub End Sub

If you have any questions, drop them in the comments section, where Ryan and I will be waiting there for you.

You May Also Like the Following Excel Tutorials:

• How to Embed a PDF File in an Excel Worksheet.
• Excel VBA Loops: For Next, Do While, Do Until, For Each (with Examples).
• How to Record a Macro in Excel
• How to Combine Multiple Workbooks into One Excel Workbook.
• How to Run a Macro in Excel.
• How to Create and Use an Excel Add-in.
• Excel VBA Error Handling
• Split Each Excel Sheet Into Separate Files
• How to Import XML File into Excel | Convert XML to Excel

About the author: Ryan Wells is a Nuclear Engineer and professional VBA Developer. He publishes his easy to understand Excel VBA tutorials to help others write better macros. In addition to teaching VBA, Ryan is the lead developer of several Excel add-ins. You can find his tutorials on WellsR.com.