Save Excel As PDF With VBA
Có thể bạn quan tâm
Since Excel 2010, it has been possible to save Excel as PDF. The PDF format was then and continues to be, one of the most common file formats for distributing documents.
The code examples below provide the VBA macros to automate the creation of PDFs from Excel using the ExportAsFixedFormat method. This means you do not require a PDF printer installed, as Excel can print directly to a PDF document.
Table of Contents- Saving Excel workbooks, sheets, charts, and ranges as PDF
- Save active sheet as PDF
- Save active workbook as PDF
- Save selection as PDF
- Save a range as PDF
- Save a chart as PDF
- Adapting the code to your scenario
- Notes for saving PDF documents
- Selecting specific worksheets before saving as PDF
- Looping and saving as separate PDFs
- Loop through sheets
- Loop through selected sheets
- Loop through charts
- Other PDF print options
- VBA Save to PDF Example using all the options
- Other fixed formats available (xlTypeXPS)
- Conclusion
The example codes can be used independently or as part of a larger automation process. For example, check out this post to see an example of how to loop through a list and print a PDF for each item: Create multiple PDFs based on a list
Rather than going from Excel to PDF, you might want to go the other way; from PDF to Excel. Check out these posts for possible solutions for that scenario:
- How to Import PDF Files into Excel with Power Query
- Get data from PDF into Excel
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0019 Save Excel as PDF with VBA.zip
Get accessSaving Excel workbooks, sheets, charts, and ranges as PDF
This section contains the base code to save Excel as PDF from different objects (workbooks, worksheets, ranges, and charts). From a VBA perspective, it is the ExportAsFilxedFormat method combined with the Type property set to xlTypePDF that creates a PDF.
Save active sheet as PDF
The following code saves the selected sheets as a single PDF.
Sub SaveActiveSheetsAsPDF() 'Create and assign variables Dim saveLocation As String saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'Save Active Sheet(s) as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubSave active workbook as PDF
Use the following macro to save all the visible sheets from a workbook.
Sub SaveActiveWorkbookAsPDF() 'Create and assign variables Dim saveLocation As String saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'Save active workbook as PDF ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubSave selection as PDF
Sometimes, we only want to save a small part of a worksheet to a PDF. The following code prints only the selected cells.
Sub SaveSelectionAsPDF() 'Create and assign variables Dim saveLocation As String saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'Save selection as PDF Selection.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubSave a range as PDF
The macro below saves a specified range as a PDF.
Sub SaveRangeAsPDF() 'Create and assign variables Dim saveLocation As String Dim ws as Worksheet Dim rng As Range saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Set ws = Sheets("Sheet1") Set rng = ws.Range("A1:H20") 'Save a range as PDF rng.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubSave a chart as PDF
The VBA code below saves a specified chart as a PDF.
Sub SaveChartAsPDF() 'Create and assign variables Dim saveLocation As String Dim ws As Worksheet Dim cht As Chart saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Set ws = Sheets("Sheet1") Set cht = ws.ChartObjects("Chart 1").Chart 'Save a chart as PDF cht.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubRather than naming a specific chart, the macro could run based on the active chart. Change this:
Set cht = ws.ChartObjects("Chart 1").ChartTo this:
Set cht = ActiveChartAdapting the code to your scenario
To adapt the code examples to your specific needs, you should adjust certain lines of code.
Change the save location
To save the file in the correct location, change this list of code:
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"If you would prefer the save location to be included in a cell, change the code to reference the sheet and cell containing the file path.
saveLocation = Sheets("Sheet1").Range("B2").ValueChange the worksheet
In this line of code, change the text “Sheet1” to the sheet name in your workbook.
Set ws = Sheets("Sheet1")Change the range
The following line of codes references the range to be printed to PDF.
Set rng = ws.Range("A1:H20")Change the chart
To print a chart to PDF, change the chart’s name in the following line of code.
Set cht = ws.ChartObjects("Chart 1").ChartIf you are unsure of the chart name, it is shown in the Name box when the chart is selected.
Notes for saving PDF documents
While the Filename property is optional, it is important to know where the file is saved.
- If the Filename property is not provided, the PDF saves in your default folder location using the Excel workbook’s name with the .pdf file extension.
- Where a file name is provided, but not a file path, the document saves in your default folder location with the name provided.
- When the .pdf file extension is not provided, the suffix is added automatically.
- If a PDF exists in the specified save location, the existing file is overwritten. Therefore, it may be necessary to include file handling procedures to prevent overwriting existing documents and handling errors.
- To save as an XPS document format, change xlTypePDF for xlTypeXPS.
Selecting specific worksheets before saving as PDF
If more than one worksheet is active, the PDF created includes all the active sheets. The following code selects multiple worksheets from an array before saving the PDF.
Sub SelectSheetsAndSaveAsPDF() 'Create and assign variables Dim saveLocation As String Dim sheetArray As Variant saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" sheetArray = Array("Sheet1", "Sheet2") 'Select specific sheets from workbook, the save all as PDF Sheets(sheetArray).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End SubIn the code above, an array is used to select the specific sheets. Alternatively, the Split array function with a text string could provide a more dynamic solution. This method is covered here: VBA Arrays.
Looping and saving as separate PDFs
To save multiple PDFs quickly, we can use VBA to loop through sheets or charts and save each individually.
Loop through sheets
The following macro loops through each worksheet in the active workbook and saves each as its own PDF. Each PDF is saved in the same folder as the workbook, where each PDF’s name is based on the worksheet’s name.
Sub LoopSheetsSaveAsPDF() 'Create variables Dim ws As Worksheet 'Loop through all worksheets and save as individual PDF in same folder 'as the Excel file For Each ws In ActiveWorkbook.Worksheets ws.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf" Next End SubLoop through selected sheets
The following macro loops through only the selected worksheets in the active workbook and saves each as its own PDF.
Sub LoopSelectedSheetsSaveAsPDF() 'Create variables Dim ws As Worksheet Dim sheetArray As Variant 'Capture the selected sheets Set sheetArray = ActiveWindow.SelectedSheets 'Loop through each selected worksheet For Each ws In sheetArray ws.Select ws.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf" Next ws 'Reselect the selected sheets sheetArray.Select End SubLoop through charts
The following code loops through each chart on the active sheet and saves each as a separate PDF.
Sub LoopChartsSaveAsPDF() 'Create and assign variables Dim chtObj As ChartObject Dim ws As Worksheet Set ws = ActiveSheet 'Loop through all charts and save as individual PDF in same folder 'as the Excel file For Each chtObj In ws.ChartObjects chtObj.Chart.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=ThisWorkbook.Path & "/" & chtObj.Name & ".pdf" Next chtObj End SubOther PDF print options
When using ExportAsFixedFormat, there are other optional settings available:
'Open the document after it is saved - options are True / False OpenAfterPublish:=False 'Include the Excel document properties into the PDF - options are True / False IncludeDocProperties:=True 'Does the created PDF adhere to the Print Areas already set in the 'worksheet - options are True / False IgnorePrintAreas:=False 'Set the output quality of the created document - options are 'xlQualityMinimum / xlQualityStandard Quality:=xlQualityStandard 'The page to start printing. If excluded, will start from the first page From:=1 'The page to print to. If excluded, will go to the last page To:=2VBA Save to PDF Example using all the options
The code below demonstrates how to use all the options within a single macro. These options can be flexed to meet your requirements.
Sub SaveAsPDFOptions() Dim saveLocation As String saveLocation = "C:\Users\marks\Documents\myPDFFile.pdf" 'Example using all the options ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=saveLocation, _ OpenAfterPublish:=False, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ Quality:=xlQualityStandard, _ From:=1, To:=2 End SubOther fixed formats available (xlTypeXPS)
The Type property can also create XPS documents when it is set to xlTypeXPS rather than xlTypePDF. XPS is Microsoft’s fixed file format; it is similar to PDF but based on the XML language. It is rarely used in the real world but is an option if required.
Conclusion
Learning how to save Excel as PDF is a good time investment. Each of these code snippets on its own is useful. However, the code examples above can be used in other automation to create even more time-saving.
Related posts:
- Excel – Create multiple PDFs based on a list
- Loop through selected sheets with VBA
- How to loop through each item in Data Validation list with VBA
What next?
Discover how you can automate your work with our Excel courses and tools.
Excel AcademyThe complete program for saving time by automating Excel.
Find out moreExcel Automation SecretsDiscover the 7-step framework for automating Excel.
Find out moreOffice Scripts: Automate Excel EverywhereStart using Office Scripts and Power Automate to automate Excel in new ways.
Find out moreTừ khóa » Visual Basic Excel Print To Pdf
-
Excel VBA Save As (Print) To PDF
-
How To Print To PDF In Excel VBA : With Examples And Illustrations
-
Print To PDF Using Macro Button In Excel (5 Macro Variants)
-
VBA Print To PDF And Save With Automatic File Name - Stack Overflow
-
How To Print A PDF In Excel VBA? | MrExcel Message Board
-
Excel VBA Save As PDF: Step-By-Step Guide And 10 Examples
-
Excel Macro To Save Sheets As PDF - Contextures
-
VBA For Excel (365), How To Print To PDF, While Scaling To Fit All ...
-
Excel Print To PDF Vba | PDF | Visual Basic For Applications - Scribd
-
VBA Macro One Click Print To PDF | Strength Coach Tutorials
-
How To Print To PDF With Excel VBA? - Super User
-
Solved: Print To Pdf From An Excel - Autodesk Community - AutoCAD
-
Cách Chuyển File Excel Sang PDF Sử Dụng VBA - Thủ Thuật
-
Excel Vba Open And Print PDF File - PDFCOFFEE.COM