Save Excel as PDF with VBA

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 example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download the file: 0019 Save Excel as PDF with VBA.zip

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.

Save active workbook as PDF

To save all the sheets from a workbook use the following macro.

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.

Save a range as PDF

The macro below saves a specified range as a PDF.

Save a chart as PDF

The VBA code below saves a specified chart as a PDF.

Notes for saving PDF documents

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.

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.

report this ad Loop through selected sheets

The following macro loops through the selected worksheets in the active workbook and saves each as its own PDF.

Loop through charts

The following code loops through each chart on the active sheet and saves each as a separate PDF.

Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.

100 example codes to practice reading and writing macros that will embed the language into your thinking.
An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Other PDF export options

When using ExportAsFixedFormat there are other optional settings available:

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.

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.

Интересно почитать: Как в excel вести клиентов в

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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.

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.

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:

VBA Save Workbook (Excel File)

To save an Excel workbook using VBA, you need to use the SAVE method to write a macro. And in that macro, you need to specify the workbook that you want to save and then use the SAVE method. When you run this code, it works like the keyboard shortcut (Control + S).

Specify the workbook hat you want to save.
Type a dot to get the list of all the properties and methods.
Select the “Save” method out of those or type “Save”
In the end, run the code to save the workbook.

In this tutorial, we will look at different ways that we can use to save a workbook. So make sure to open the VBA editor from the developer tab to use the code you have in this tutorial.

Save the ActiveWorkbook

If you want to save the active workbook in that case you can use a code like the following code, instead of specifying the workbook by its name.

When you use the ActiveWorkbook as the workbook, VBA always refers to the workbook which is active despite in which file you are writing the code.

Save the Workbook where you are Writing Code

If you want to save the file where you are writing the code you need to use “ThisWorkbook” instead of the workbook name.

Save All the Open Workbooks

Here we can use a loop to loop through all the workbooks that are open and save them one by one. Look at the below code.

The above code uses the FOR EACH loop in each workbook it uses the SAVE method to each file one by one.

Note: If you are trying to save a workbook with the SAVE method that is not saved already, Excel will show a dialog box to ask for your permission to save that file, and then you need to choose if you want to save that file on the default location in the default format.

Now here’s the point: As you are using a macro to save the workbook, that file should be saved in the macro-enabled format and the best way to deal with this situation is to use the SAVE AS method (we’ll see in the next section of this tutorial).

Save As an Excel File

To SAVE a file that is not saved yet, using VBA, you need to use the SAVE AS method. In this method, you can define the file name and the path where you want to save the file, and apart from that, there are ten more arguments that you can define.

Интересно почитать: Excel найти дату в тексте

In the following code, you don’t have any argument with the “SAVE AS” method.

When you run this code, it asks you a few things, like, which format you want to use to save the file, do you want to replace the existing file that is already saved with the same name. So it’s better to define the use of some of the arguments.

Save As File on the Current Location

By default, VBA uses the current location to save the file. When you write code with the SAVE AS method and just specify the name that file straight goes to the current folder. You can see in the following code where you have the which saves the active workbook.

Save As File on a Specific Location

The filename argument also allows you to use the location path in case you want to use a different location to save the file.

In the above code, you have the path in the FileName argument and VBA uses that path to the file.

Note: You can also use this method to check if a workbook exists in a folder or not before you use the SAVE AS method to save it on a particular location and you can learn more about SAVE AS method from here.

VBA Save As

By Dheeraj Vaidya

VBA Save As

Excel VBA Save As

If you are a frequent user of Microsoft Excel, you must have used Save As function under it, which allows you to save the currently opened workbook with a different name or different format (Excel Macro-enabled, CSV, PDF, etc.). You can also save the file in a different folder using this method. However, is it possible to use the same function under VBA? The answer is an absolute Yes! We are having Save As function under VBA as well which helps us to do all these above-mentioned tasks along with some additional benefits (obviously automating things is one of the benefits). In this article, we are going to have a look into different examples for VBA SAVE AS function.

Formula for Save As function in Excel VBA

Let us look below the formula for Save As function in VBA.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

FileName – Name of the workbook to be saved.
FileFormat – File format in which the file needs to be saved (Ex. Pdf, CSV, etc.)
Password – Password to protect the workbook (The workbook can’t be accessible without a password)
WriteResPassword – Write reservation password for the workbook.
ReadOnlyRecommended – Recognizes whether the workbook is saved in Read-Only format or not.
CreateBackup – Determines whether a backup file for the workbook is created or not.
AccessMode – Recognizes the access mode for the workbook.
ConflictResolution – Recognizes the conflicts that pop-up when the workbook is shared and is used by more than one user.
AddToMru – Checks if the workbook is added under recently used file or not.
Local – Checks if the workbook is saved with the laws of Excel (local language) or with VBA laws (US – English).

Hush! Lots of arguments right? But what if I tell you, all these arguments are optional and can be skipped while using VBA SAVE AS function. However, it is true that these are the arguments that make VBA SaveAs more flexible function to use. “Expression” at the start of the syntax is nothing but an expression against which this function can be used. Like Workbook is the expression against which SaveAs can be used.

Examples to Save Excel File using VBA Save As Function

Below are the different examples to save excel file using VBA Save As function.

Example #1 – How to Save a Copy of the Workbook with a Different Name?

Let’s see how we can save the current workbook with a different name.

Follow the below steps to use Save As Function in Excel VBA:

Step 1: Add a new module under Visual Basic Editor (VBE). Go to Insert and then select Module.

Step 2: Define a new sub-procedure which can store a macro.

Code:

Step 3: Define a new variable which can hold the name by which the file to be saved as.

Интересно почитать: Как скопировать лист в excel на другой лист

Code:

Step 4: Now use the assignment operator to assign a name to this variable using which current file can be saved as.

Code:

Step 5: Now, use SaveAs function with FileName argument in order to save the file as with name “Example1”.

Code:

Step 6: This is it, now run this code by hitting F5 or manually using the Run button and see the output.

You can see that a file with the name “Example1” is being saved on Documents.

If you could have noted down, the file is being saved as Macro-Enabled File, because the original file which I have used SaveAs function on is a file with Macro-Enabled. It means that this function in VBA automatically checks the file format of the previous file and saves it in the same format. Also, by default, the file will be saved in Documents under This PC. This default location can be provided explicitly at the time of defining sheet name.

Example #2 – Saving Workbook with User Provided Name

Instead of defining name initially, is it possible to write a code which allows a user to save the worksheet by the name of his choice same as Excel Save As function?

Follow the below steps to use Save As Function in Excel VBA.

Step 1: Define a new sub-procedure under newly inserted module which can store the macro.

Code:

Step 2: Define a new variable which can hold the value of the user-defined name.

Code:

The reason for this variable being defined as Variant is, this data type makes Naming conventions versatile. For Example, a user may add some extra special character (which are allowed in naming conventions) or can add dates as well under the file name.

Step 3: Now, with the help of an assignment operator and function combination called application.GetSaveAsFilename, make a statement that allows the system to take a user-defined name. See how it has been achieved in the screenshot below.

Code:

Step 4: Use conditional IF to make sure the name user enters is valid as per the naming conventions.

Code:

This condition checks if the name given by the user to save the worksheet is properly satisfying the naming conventions set for naming a file or not.

Step 5: Write down a statement which gets evaluated for the given IF condition.

Code:

This piece of code gets evaluated once the IF condition is true. If so, the active workbook will get saved under the name define in variable Spreadsheet_Name (Which will be user-defined)

Step 6: End the IF-loop and run this code to see the output.

Code:

Step 7: As soon as you run this code, you’ll get Save As dialogue box which will allow you to type in the name of your choice and save the file.

Example #3 – How to Save as a File into PDF using Excel VBA SaveAs function?

Suppose you have a data as given below in your excel sheet and you need to convert it into PDF.

Follow the below steps to convert this file into a PDF Using VBA Save As function:

Step 1: Define a new sub-procedure to store a macro.

Code:

Step 2: Now, use the following code to save this file as a PDF file.

Code:

Step 3: Run this code and you’ll see a pdf file generated under This PC > Documents.

In this code, ActiveSheet.SaveAs allows the file to be saved with the same name. As we have added the extension as .pdf at the end of the file, it gets exported into PDF file. You can see the image above for your reference.

Things to Remember

The by default save location for the file used under VBA SaveAs will be This PC > Documents. However, you can specify the directory manually at the time of defining file name.
By default, the file saved using VBA SaveAs will be saved under the format same as that of the original file. However, it can also be defined as per user requirement at the time you define the variable.