Since Excel 2010, it has been possible to save Excel files as PDF documents. PDF was then and continues to be, a common file format for distributing documents.
The code examples below provide the VBA options to automate the creation of PDFs from Excel. They can be used on their own or as part of larger processes.
Rather than going from Excel to PDF, you might want to go the other way; from PDF to Excel. Check out this post for possible solutions for that: Get data from PDF into Excel.
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0019 Save Excel as PDF with VBA.zip.
Contents
Saving 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 Sub
Save active workbook as PDF
To save all the sheets from a workbook use the following macro.
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 Sub
Save selection as PDF
Sometimes, we only want to save a small part of a worksheet to a PDF. The following code saves 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 Sub
Save 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 rng As Range saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Set rng = Sheets("Sheet1").Range("A1:H20") 'Save a range as PDF rng.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End Sub
Save 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 cht As Chart saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Set cht = Sheets("Sheet3").ChartObjects("Chart 1").Chart 'Alternative using the active chart 'Set cht = ActiveChart 'Save a chart as PDF cht.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation End Sub
Notes for saving PDF documents
While the Filename property is optional, it is important if we want to know where the file has been saved and what it’s called.
- 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 already exists in the save location specified, it will be overwritten. It may be necessary to include file handling procedures to prevent overwriting existing documents and handle errors.
Selecting specific worksheets before saving as PDF
If more than one worksheet is active, the PDF created will include 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 Sub
Looping and saving as separate PDFs
To save multiple PDFs quickly, we can use VBA to loop through sheets or charts and save each.
Loop through sheets
The following macro loops through each worksheet in the active workbook and saves each as its own PDF.
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 Sub
Loop through selected sheets
The following macro loops through the selected worksheets in the active workbook and saves each as its own PDF.
Sub LoopSelectedSheetsSaveAsPDF() 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 Sub
Loop 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 abd 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 Sub
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
Other PDF export 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 from. If Excluded, will start from the first page From:=1 'The page to print to. If Excluded, will go to the last page To:=2
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 Sub
Other fixed formats available
The Type property can also create XPS documents when it is set to xlTypeXPS, rather than xlTypePDF. XPS is Microsoft’s own fixed file format, which 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 there own are not that impressive. However, when combined with the automated e-mail processes and document control, they are a real time saver.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Thanks for the brilliant post. I found it to be very educative.
Great, I’m pleased it helped.
Thanks, just what I was looking for. Cristal clear explanation
Thanks, I’m glad I could help 🙂
I Bought Office 365 Personal that I expected tha VBA still was in Excel, but I cannot find. If it not there(As far I can See) I must of course pay for 1 year and then STOP. It there another way to get VBA with Excel in a another product.
I am very very disappointed, because it was the only reason I bought this product.
To use VBA, you need to turn on the Developer Tab:
Thanks for the guide.
How do I get the same name on the pdf as the workbook ?
Hi Frede
ActiveWorkbook.Name would give you the name of the workbook. But you would also get the “.xlsx” on the end. So something like this would give you a PDF file name:
Cannnot find ANYWHERE that can tell me how to SELECT SPECIFIC WORKSHEETS and create MULTIPLE PDFs. Can you?
Sure – I’ve added a section in the post above: Loop through selected sheets.
Good beginner article.
It would be great to see you take this further and actually manipulate the pdf after generating it. Things like adding a menu, or bookmarks, modifying the file security, adding attachments to the pdf file, etc. are big winners.
In my own experience this can be done but it requires the use of the Microsoft_JScript library and PDF object.
Let me know if you take this on.
Thanks for the suggestion. If I ever look into it, then I’ll certainly be sharing it here.
Thank you for the very useful post! Please keep going!
Thanks this has helped me save a ton of time!
I have one question:
I want the file name of my sheet to be a cell value (the invoice number) which I can do with :
s = Range(“G6”).Value
‘
‘
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=s, …..etc….
BUT…
The pdf goes to the location where the spreadsheet is. I would like it to go to a different folder. Is this possible?
Read the section in the article entitled “Notes for saving PDF documents”, this explains how to save in different locations.
For your scenario:
You either need to add the full file path into G6 our you could concatenate together such as (assuming the folder path is in G7):
s = Range("G7").Value & Range("G6").ValueBrilliant; that was exactly the fix. I put the file path in another cell and used the concatenate you suggested.
THANK YOU SO MUCH!!
i am using win10 64 bit
but error on
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
on
I don’t believe there is any reason why Windows 10 64 bit would behave like that. Is the saveLocation valid? That’s the most likely cause.
Thanks for the article, I do have one question. When I convert the spreadsheet to a PDF some of the sheets are oversized and are stretched across multiple pages. Is there a way to adjust the zoom before converting? Thank you.
I think it’s based upon how the print settings have been applied, adjust the settings in the Page Layout menu, Page Setup and Scale to Fit sections.
Hello Sir,
I found this article very usefull, it really helped me a lot.
Peoples Like you are giving opportunity to beginners like us to learn more things & make us more productive.
Great article as always. I have a project with multiple named ranges that I want to export to PDF. they are on various sheets in the workbook. I’m able to export them to different PDF files but I’d like to put them all in the same file. Can it be done?
Hi Ted – interesting question:
It’s not something I’ve tried, but I’ve got two ideas which may work:
Idea #1
You could try this:
– Create named ranges called “Print_Area” to be the same range as the named ranges.
– Select the worksheets
– Export the selected worksheets as PDF
I’ve not tried it, but that should be possible.
Idea #2
Here is a harder option, but should also work.
– Copy and past the named ranges to images all on the same worksheet.
– Position the images so that they each exist on one worksheet with page breaks in the right places
– Export the worksheet as a PDF.
Let me know if you get any success with them.
thank you for this help!
I made an invoce wich complete automatically to a register then to a pdf, even if it is on onedrive (it upload it perfecly, then open online)
PERFECT!
Hello. So, if i were to loop through all worksheets in a single workbook to save each worksheet as a separate PDF file, how would i make sure that each PDF file has a separate/ distinct name that I need? Is it possible?
The SaveName property determines the name of the file. Just create a string which includes use the worksheet name or a counter to create a unique name.
Information provided is very educative. Thank you. How can I save data from excel into individual fillable pdf files? Thanks.
I’m not sure that’s possible directly in Excel. I think you would need to use a specific PDF tool to achieve that.
Hi everyone,
I am using the looping through sheets example to output several sheets as 1 PDF per sheet and love that I found this macro. However, I wanted to include a value from cell A11 in the filename of each sheet but found that only the value from the first sheet is being used. This means that the Macro outputs the several sheets, each overwriting the the last as the macro goes along. Any idea how to get it working using the value in A11 (A text string) for each consecutive sheet?
Here is my code so far:
Sub Export_PDF()
‘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 & “\my path\” & Range(“A11”).Value & “.pdf”
Next
End Sub
I’ve not had change to test this, but if you change this:
...& Range("A11").Value & ".pdf"To this:
...& ws.Range("A11").Value & ".pdf"it should work.
Looking to automate the process of filling customer info and printing contracts (~15,000). To do this I have set up the contract template in a worksheet and have lookups to fill in the customer info.
Where I am stuck is saving the range (of the contract) as pdf and naming it the unique customer ID (to prevent overwriting).
Is is possible to loop this process of filling the customer info, saving as pdf and printing while moving down a list of customer IDs?
This is a loaded question, but your insights would be greatly appreciated!
Yes this is possible
– Loop through the cells containing the customer list. Within the loop include:
– Calculate the worksheet so that it refreshes values in the contract template
– Save the template as PDF using the customer name within the file name.
This will be a great way to learn how to use VBA to automate your processes.
Hello. This was very helpful, thank you! I wondered if you could help me with a problem I am having. I have a macro-enabled Excel template that multiple users will be accessing from a shared network drive. When they run the macro to save the Excel file as a PDF, I want it to save to their own desktop, so using a file path such as “C:\Users\TeriVavricek\Desktop\File.pdf” (which works if iIwant to save to MY desktop) in the file name will not work. Is there a way to code the save location to be that of the desktop of the user currently using the file? Thank you!
The following should give you the file path to the Desktop folder
Dim FileName As String FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\File.pdf"You can then use the FileName variable within the macro.
Thank you for the quick response! I still seem to be a little stuck. Below is my original code. What it accomplished was exporting 3 sheets to PDF on my (TeriVavricek) desktop with the file name set as the value of cell N2 on the Auto UW Documentation sheet. I’m trying to figure out how to incorporate the code you provided into what I have here. I would appreciate any additional assistance you can provide. Thank you!
Sub ExportAsPDFAUTO()
Dim strFilename As String
Dim rngRange As Range
Set rngRange = Worksheets(“Auto UW Documentation”).Range(“n2”)
strFilename = rngRange.Value & Format(Now(), “yyyymmdd”)
Sheets(Array(“Account UW Documentation”, “Loss Analysis”, “Auto UW Documentation”)).Select
Sheets(“Account UW Documentation”).Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
“C:\Users\TeriVavricek\Desktop\” & strFilename & “.pdf” _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub