Since Excel 2010 it has been possible to save Excel files as PDF documents. The code below provides the VBA options to do achieve this. These VBA Code Snippets can be used in much larger processes to automate the distribution of hundreds of document in just a few seconds.
Saving Excel workbooks, sheets, charts and ranges as PDF documents
'Save Active Sheet(s) as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf" 'Save active workbook as PDF ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf" 'Save selection as PDF Selection.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf" 'Save a chart as PDF ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf" 'Save a range as PDF Sheets("Sheet1").Range("A1:H20").ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf"
Basic notes for saving PDF documents
- If the Filename property is not provided, the PDF document is saved with the PDF file extension in the same folder and with the same name as the Excel document.
- If the Filename property excludes the file path, but includes a file name, the document is saved into the same folder as the Excel document, but with the specified filename.
- If the Filename property does not include .pdf at the end, it will use the last section of text after the final “\” as the file name and add the .pdf suffix automatically.
- If a PDF already exists in the location with that file name, it will be overwritten. It may be necessary 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 created PDF will include all the active sheets. The following code selects multiple worksheets before saving the PDF.
'Select specific sheets from workbook, the save all as PDF
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\marks\Documents\Saved PDF.pdf"
Loop through each worksheet and save as separate documents
It is possible to loop through each worksheet in the active workbook and save each into its own PDF.
'Loop through all worksheets and save as individual PDF in same folder 'as the Excel file Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name Next
Other PDF Export Options
When using ExportAsFixedFormat there are other optional arguments 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
'Example using all the options ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="C:\Users\marks\Downloads\testpage2.pdf", _ OpenAfterPublish:=False, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ Quality:=xlQualityStandard, _ From:=1, To:=2
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, but is rarely used.
sir where is save pdf print? no location found
‘Loop through all worksheets and save as individual PDF in same folder
‘as the Excel file
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
Next
It will save all the PDF’s in the same folder as the Exce file is stored.
If you wanted to use a specific file path then it can be defined as follows
Filename:=”C:\Users\marks\Documents\Saved PDF.pdf”
sir that’s not working, please send any example file
i have 5 sheet on excel, but this coding (Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
Next) working only last sheet,
please fix this problem and if u have any example file , share with me.
Hi guys,
Can someone please help me with a VBA I’m struggling with.
I have a button on my spreadsheet (template) that I want to use to do the following:
-Save first page only as PDF (to specified folder in local drive C)
-Name PDF content of Cell B7
-Attach PDF file to Outlook mail
-Send mail to set recipient and to recipient email address in cell B9 (this is actually what I’m struggling with).
I would be forever grateful if someone can help me out here.
Hi Stefan – The code on here should help you.
In terms of sending e-mails, this post should help: https://exceloffthegrid.com/automatic-email-template/
Don’t know if you can help with VBA code for a macro but I am using Excel for MAC . Any help, suggestions appreciated. Do I need any special add-ins to make this work?
This is what I want to do:-
Select RangeA2 to I44
Use the filename that’s in Cell H10
Save as PDF to Path DATA1\DATA\DROPBOX\\BUSINESS\PDFINVOICE Filename as in Cell H10)
Much apprecited
Hi,
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim FilePath As String
Dim filename As String
FilePath = “D:\fileName ”
filename = FilePath & Format(Date, “DD.MM.YY”)
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=filename
End Sub
The above code worked fine for me while I was working in Excel 2013 in Windows 7.
Last month I upgraded my Windows from 7 to 10 last month and since then I am encountering – Run-time error ‘1004’
I am still using Excel 2013.
How do I solve this ?
Why am I getting this error now ?
Thanks in advance guys.
I have specific requirement.
In a Form, i need to import a PDF and save the same PDF to another location.
Hi Sai,
First, you’ll need to open a file selection dialog box to get the file path (this post should help):
https://exceloffthegrid.com/selecting-file-using-filedialog/
Then move the file to your preferred location (The Move section of this post should help):
https://exceloffthegrid.com/vba-code-to-copy-move-delete-and-manage-files/
Thanks
Mark
Sir,
I need to print multiple file into a single PDF file. Is this possible?
What I am doing is that I fill in a worksheet, print as pdf and then fill the sheet again with different information and append it to the same pdf file. In this way, I will have only one pdf file for all my sheets. Is this something I can do with this function? any other suggestion please?
Thanks,
Atheer
This method will not provide exactly what you need.
You’ll need to write a macro that hard-codes the result to a new worksheet (or pastes as picture) for each parameter. Once all that has looped through, you can then save all the new worksheets as a single PDF. Finally, delete the new worksheets created.
Alternatively, if you have full Adobe Acrobat, you can merge PDFs using the Adobe reference library. Just check on Google, there are codes on various other sites.
Hi,
Would you be able to help me with this ?
I am trying to :
– save files in a selected location by user ( now the code contains desktop location)
– save all tabs as separate pdfs ( it is working properly now)
– name all pdf files the same as a tab name without any user interaction ( it is working properly now)
Would be perfect that user needs to select location only once and all pdf files with the proper names are saved there automatically.
I would really appreciate for help
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
nm = ws.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=CreateObject(“WScript.Shell”).specialfolders(“Desktop”) & “\” & nm & “.pdf”, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next ws
Hi Karolina – the best way to store the selected location in a cell.
The following code will allow a user to select a folder and store the file path in cell A1 of Sheet1.
Dim dialogBox As FileDialog Set dialogBox = Application.FileDialog(msoFileDialogFolderPicker) If dialogBox.Show = -1 Then Sheets("Sheet1").Range("A1").Value = dialogBox.SelectedItems(1) End If