Power Spreadsheets focuses on Microsoft Excel.
However, I've written several tutorials (such as here and here) that have to do with PDF and, more particularly, with the topic of converting PDF files to Excel.
The reason why I write about PDF is relatively straightforward: PDF is one of the most widely used file formats.
This particular Excel tutorial also focuses on the topic of working with Excel and PDF files. More precisely, I explain in detail how you can save Excel files as PDF using VBA, and provide 10 examples of VBA code that you can start using immediately.
In fact, in this blog post, I go much further than simply showing you how to simply convert Excel files to PDF. Among others, I also:
The following table of contents lists the different sections of this blog post.
Table of Contents
This Excel VBA Save as PDF Tutorial is accompanied by files containing the data and macros I use in the examples below. You can get immediate free access to these example files by clicking the button below.
The basic VBA method you use for converting Excel files to PDF is ExportAsFixedFormat. Therefore, let's start this blog post by taking a look at it:
The main purpose of the ExportAsFixedFormat method is to export a particular Excel object (such as a file, worksheet, cell range or chart) to another file format, usually PDF.
You can apply the ExportAsFixedFormat method to several Excel objects. To be more precise, you'll generally be working with 1 of the following versions of this method, depending on which particular object you want to save as PDF.
The basic syntax of the ExportAsFixedFormat method is generally as follows:
expression.ExportAsFixedFormat (Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish)
For these purposes, “expression” is a variable representing a Workbook, Worksheet, Chart or Range object, depending on the particular version of the method you're working with. In other words, “expression” is the placeholder for the object that you want to actually save as PDF.
This particular VBA method has 9 different parameters. Let's take a look at each of them:
You use the Type parameter to specify the type of file format to which the Excel file must be exported to. In other words, you determine in which file format the new (resulting) file is saved by specifying it in the Type parameter.
In order to specify the type of file you want to convert the Excel file to, you use the xlFixedFormatType enumeration. There are 2 types of file formats you can choose from:
The purpose of this Excel tutorial is to explain how you can use Visual Basic for Applications to save an Excel file as PDF. Therefore, whenever using the Type parameter in this blog post, I only use xlTypePDF.
Furthermore, XPS never seemed to gain much traction. Therefore, you're unlikely to encounter (or have to work with) many XPS files.
Type is the only required parameter of the ExportAsFixedFormat method.
As implied by its name, you use the Filename parameter of the ExportAsFixedFormat method for purposes of specifying the filename of the new (converted) file.
When specifying the Filename parameter, you have 2 broad options:
Filename is a string.
You have the option of choosing the quality of the resulting PDF file (standard or minimum).
For these purposes, you use the Quality parameter of the ExportAsFixedFormat method. More precisely, you can choose 1 of the 2 xlFixedFormatQuality options:
The IncludeDocProperties parameter of the ExportAsFixedFormat method allows you to determine whether the document properties are included in the converted PDF file or not.
To make this choice, you simply need to set the IncludeDocProperties parameter to True or False as follows:
By using the IgnorePrintAreas parameter of the ExportAsFixedFormat method, you can specify whether Visual Basic for Applications should ignore (or not) the print areas that have been set for the relevant Excel file.
To specify whether the print areas should (or shouldn't) be ignored, set the IgnorePrintAreas parameter to True or False as follows:
The From and To parameters of the ExportAsFixedFormat method allow you to specify the pages at which the publishing to PDF should begin and end. More precisely:
By using the OpenAfterPublish parameter of the ExportAsFixedFormat method, you can determine whether the converted PDF file should be displayed in the PDF viewer as soon as the export process is completed.
You specify whether the file is displayed or not by setting the OpenAfterPublish parameter to True or False, as follows:
The FixedFormatExtClassPtr parameter of the ExportAsFixedFormat method is a pointer to the FixedFormatExt class.
Now that you are familiar with the ExportAsFixedFormat VBA method, let's take a look at some code examples.
For purposes of this example, I have prepared a sample Excel workbook. This workbook contains 2 worksheets. Each worksheet includes a table that lists 100 persons and their contact details, along with (i) their food preferences, and (ii) their favorite animal and the name of their pet.
As I explain above when introducing the ExportAsFixedFormat VBA method, the only required parameter is Type. Therefore, strictly speaking, you can create a very simple macro for purposes of saving an Excel file as a PDF.
The following sample macro (named Save_Excel_As_PDF_1) is, probably, one of the simplest way to save an Excel worksheet as PDF using VBA.
This macro consists of a single simple statement:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
This statement, can be separated in the following 3 items:
Let's take a closer look at each of these elements:
As I explain when introducing the Worksheet.ExportAsFixedFormat method above, the reference to the method must be preceded by a variable representing a Worksheet object.
In the case of the sample Save_Excel_As_PDF_1 macro above, the Application.ActiveSheet property is used for these purposes. More precisely, ActiveSheet return an object representing the active sheet within the active (or specified) workbook.
You can, as a general matter, also for any of the following:
The ExportAsFixedFormat method is the main subject of this Excel tutorial. The purpose of this method is to save the relevant object (a worksheet returned by ActiveSheet in the example above) as a PDF file.
In this particular case, ExportAsFixedFormat uses a single parameter, which is…
Type is the only required parameter of the ExportAsFixedFormat method. Its purpose is to specify the type of file format to which the relevant worksheet should be exported to.
You specify the relevant file type by choosing the appropriate value from the XLFixedFormatType enumeration. xlTypePDF is 1 of the 2 values in such enumeration.
For the reasons that I explain above, most (if not all) of your macros whose purpose is to save Excel files as PDF will set the Type parameter to be xlTypePDF (or 0). Therefore, you're likely to use this particular line of VBA code often when creating such VBA Sub procedures.
The sample Save_Excel_As_PDF_1 macro is very simple and relatively easy to follow. However, this doesn't mean that you should be using this macro to convert all of your Excel files to PDF.
The reason for this is that, as I show below, the macro (literally) just saves the active worksheet to a PDF file. Since it doesn't make any adjustment prior to the conversion, the results aren't necessarily the best.
For purposes of this example, I have executed the Save_Excel_As_PDF_1 macro to save 1 of the worksheets within the sample Excel file that accompanies this tutorial as PDF. The following image shows the resulting PDF file:
As first glance, the resulting PDF files looks OK. However, there are several things that can be improved. The following are 2 examples:
Notice how there's 1 column (corresponding to the Favorite Food Ingredient) missing in the image above. Within the resulting PDF, this column appears by itself in separate pages. The following screenshot shows how this looks like:
I show you how to solve several of these issues in the other VBA code examples within this Excel tutorial.
Let's start to tackle some of these problems by using the additional arguments of the ExportAsFixedFormat method:
The sample Save_Excel_As_PDF_1 macro above used the Worksheet.ExportAsFixedFormat method with its only required parameter (Type). However, as I explain further above, the ExportAsFixedFormat method has 9 parameters that allow you to further specify how Visual Basic for Applications carries out the conversion from Excel to PDF.
Therefore, in this second example, I include most of the parameters that you can use when working with the ExportAsFixedFormat method.
Let's start by taking a look at the VBA code behind the macro (which is called Save_Excel_As_PDF_2):
The statements within the body of this new macro are as follows:
ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="Sample Excel File Saved As PDF", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=False, _ IgnorePrintAreas:=False, _ From:=1, _ To:=5, _ OpenAfterPublish:=True
Let's take a closer look at each of these lines of VBA code:
This new macro (Save_Excel_As_PDF_2) is simply an extension of the first sample macro I provide above (Save_Excel_As_PDF_1).
These first 2 lines are the whole body of that previous macro. In this particular case, they have the same purpose as that which I explain above. Namely:
This line uses the Filename parameter of the ExportAsFixedFormat method for purposes of specifying the name of the final PDF file.
As I explain above, the previous sample macro simply used the name of the active Excel workbook to name the PDF file that was created after executing the ExportAsFixedFormat method. By using the Filename parameter, you can choose a different filename.
In this particular case, the filename that I'm using is quite simple and only includes the actual filename. Therefore, the PDF file is saved in the default file location.
However, as I explain above, you can use this same parameter for purposes of determining the full path of the converted PDF file.
This line of VBA code determines the quality of the published PDF file. The Quality parameter of the ExportAsFixedFormat can be set to either of 2 values:
You can use the IncludeDocProperties parameter of the ExportAsFixedFormat VBA method for purposes of determining whether the document properties are included or not. IncludeDocProperties can be set to either of the following 2 values:
The purpose of the IgnorePrintAreas parameter of the ExportAsFixedFormat method is to determine whether any print areas that are set when publishing should be ignored or not. Just as the previous parameter (IncludeDocProperties), IgnorePrintAreas can be set to either True or False.
You can use the From and To parameters of the ExportAsFixedFormat VBA method for purposes of setting the first and last pages that are published in the converted file. More precisely:
In the case of the sample Save_Excel_As_PDF_2 macro, I use the From and To parameters for establishing that only pages 1 (From) through 5 (To) should be published. Had I omitted any of these parameters, the effect would have been as follows:
Notice how, in the case we're analyzing, I've set the value of From to 1 (the beginning). Therefore, I can omit the From parameter from the Save_Excel_As_PDF_2 macro while continuing to obtain the same results.
In the Save_Excel_As_PDF_2 macro, I've set the To parameter to 5. This isn't the last page.
The purpose of this line of VBA code is to display the resulting PDF file in the PDF viewer once the conversion process has been completed.
You can also set OpenAfterPublish to False. In this case, the published PDF file isn't displayed.
For purposes of comparing the results obtained by the first 2 macro examples (this and example #1 above), I convert the same Excel worksheet of the sample workbook using the Save_Excel_As_PDF_2 macro.
The following image shows how the results look like.
At first glance, this looks very similar to the results obtained when executing the Save_Excel_As_PDF_1 macro above. However, notice some of the effects of the additional parameters that I included in this second macro:
to PDF conversion" width="737" height="450" />
Overall, the explanations and examples in the sections above show most of what you need to know about the ExportAsFixedFormat VBA method.
You may notice, however, that there are a few things that you can't fix with the ExportAsFixedFormat method alone. This includes, among others, one that I mention above:
The page layout isn't properly adjusted. Therefore, the resulting PDF file only shows the first 4 columns of the table within the original Excel source file. The last column (which corresponds to Favorite Food Ingredient in the example above) is missing.
There are many ways in which you can use other VBA constructs to create more complex and sophisticated macros to save Excel files as PDF. I show you some of the constructs you can use in the following sections, along with some further examples.
Let's start by taking a look at how you can solve the issue I mention above: How to adjust the page layout of the Excel file you're going to convert to PDF using VBA.
To do this, you must understand…
Within Excel, whenever you need to manually adjust the page setup before printing an Excel file or saving it as PDF, you go to the Page Setup dialog box.
Whenever you're working with Visual Basic for Applications, you can use the Page Setup object for purposes of modifying any page setup attributes. The reason for this is that, as explained at the Microsoft Dev Center (in the webpage I link to above), the Page Setup object:
As a consequence of this, the list of properties that are members of the Page Setup object is quite extensive. Let's take a look at it:
By my count, the PageSetup object has the 48 properties that I introduce in the table below.
The purpose of this table is simply to introduce these properties and their main purpose. Some of these properties aren't relevant for the purpose of this Excel tutorial on how to save an Excel file as PDF using VBA. However, I explain some of the properties you may want to explore when working with the ExportAsFixedFormat method below.
| Property | Purpose |
| CenterHorizontally | Specifies whether the sheet is centered horizontally when printed. |
| CenterVertically | Specifies whether the sheet is centered vertically when printed. |
| DifferentFirstPageHeaderFooter | Determines whether the header or footer on the first page is different. |
| OddAndEvenPagesHeaderFooter | Specifies whether there are different headers and footers for odd and even pages. |
| CenterFooter | Returns or sets the center section of the footer. |
| CenterHeader | Returns or sets the center section of the header. |
| LeftFooter | Returns or sets the left section of the footer. |
| LeftHeader | Returns or sets the left section of the header. |
| RightFooter | Returns or sets the right section of the footer. |
| RightHeader | Returns or sets the right section of the header. |
| CenterFooterPicture | Returns a Graphic object representing the picture in the center section of the footer. Use the properties of CenterFooterPicture to set the attributes of the picture. |
| CenterHeaderPicture | Returns a Graphic object representing the picture in the center section of the header. Use the properties of CenterHeaderPicture to set the attributes of the picture. |
| LeftFooterPicture | Returns a Graphic object representing the picture in the left section of the footer. Use the properties of LeftFooterPicture to set the attributes of the picture. |
| LeftHeaderPicture | Returns a Graphic object representing the picture in the left section of the header. Use the properties of LeftHeaderPicture to set the attributes of the picture. |
| RightFooterPicture | Returns a Graphic object representing the picture in the right section of the footer. Use the properties of RightFooterPicture to set the attributes of the picture. |
| RightHeaderPicture | Returns a Graphic object representing the picture in the right section of the header. Use the properties of RightHeaderPicture to set the attributes of the picture. |
| BottomMargin | Returns or sets the bottom margin. |
| FooterMargin | Returns or sets the margin between the bottom of the page and the footer. |
| HeaderMargin | Returns or sets the margin between the top of the page and the header. |
| LeftMargin | Returns or sets the left margin. |
| RightMargin | Returns or sets the right margin. |
| TopMargin | Returns or sets the top margin. |
| AlignMarginsHeaderFooter | Determines whether to align the header and the footer with the margins. |
| Application | When you use it with an object qualifier, returns an Application object representing the creator of that object. |
| BlackAndWhite | Specifies whether the document is printed in black and white. |
| Creator | Returns a 32-bit integer indicating the application in which the relevant object was created. |
| Draft | Specifies whether a sheet is printed without graphics. |
| EvenPage | Returns or sets the text alignment for the even pages of an Excel workbook (or a section). |
| FirstPage | Returns or sets the text alignment for the first page of an Excel workbook (or a section). |
| FirstPageNumber | Returns or sets the first page number to be used when a sheet is printed. |
| FitToPagesTall | Returns or sets the number of pages (tall) to which an Excel worksheet is scaled to when printed. |
| FitToPagesWide | Returns or sets the number of pages (wide) to which an Excel worksheet is scaled to when printed. |
| Order | Returns or sets the value representing the order that Excel uses to number pages when printing large worksheets. |
| Orientation | Returns or sets the value representing portrait or landscape printing mode. |
| Pages | Returns or sets the count or item number of the pages within the Pages collection. |
| PaperSize | Returns or sets the size of the paper. |
| Parent | Returns the parent object for the relevant object. |
| PrintArea | Returns or sets the range to be printed. |
| PrintComments | Returns or sets the way comments are printed. |
| PrintErrors | Returns or sets the constant specifying the type of print error displayed. |
| PrintGridlines | Determines whether cell gridlines are printed. |
| PrintHeadings | Determines whether row and column headings are printed. |
| PrintNotes | Determines whether cell notes are printed as end notes. |
| PrintQuality | Returns or sets the print quality. |
| PrintTitleColumns | Returns or sets the columns to be repeated on the left side of each page. |
| PrintTitleRows | Returns or sets the rows to be repeated at the top of each page. |
| ScaleWithDocHeaderFooter | Returns or sets whether the header and footer should (also) be scaled with the rest of the Excel file when the size changes. |
| Zoom | Returns or sets a value representing the percentage by which Excel scales the Excel worksheet for printing. |
Let's take a look at how you can use the PageSetup VBA object to improve the results obtained when saving an Excel file as PDF with the ExportAsFixedFormat method.
The previous 2 macro examples within this Excel tutorial focus on how you can use the ExportAsFixedFormat method for purposes of saving an Excel file as PDF using VBA.
This third example builds on those 2 examples (in particular example #2 above) to show how you can improve the results you obtain when carrying out an Excel to PDF conversion using VBA.
Let's start by taking a look at the VBA code behind the sample macro which, in this case, is called Save_Excel_As_PDF_3:
The statements in the body of this macro are the following:
With ActiveSheet.PageSetup .CenterHeader = "Sample Excel File Saved As PDF" .Orientation = xlPortrait .PrintArea = "$B$5:$F$105" .PrintTitleRows = ActiveSheet.Rows(5).Address .Zoom = False .FitToPagesTall = False .FitToPagesWide = 1 End With ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="Sample Excel File Saved As PDF 2", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=False, _ IgnorePrintAreas:=False, _ From:=1, _ To:=5, _ OpenAfterPublish:=True
Notice that the second part of the VBA Sub procedure (the block that starts with “ActiveSheet.ExportAsFixedFormat”) is exactly the same as the body of macro example #2 above (Save_Excel_As_PDF_2), which I have explain in detail there.
Therefore, in this particular section, I only explain (line-by-line) the first section of the macro. In other words, the With… End With block.
Let's take a look at each of the rows in this With… End With block:
The With statement allows you to execute a series of statements on a particular object (ActiveSheet.PageSetup in this case) without having to repeat the object qualification every single time.
In other words, all of the statements within the With… End With block (rows #2 to #8 below) make reference to the object that appears on the opening statement (ActiveSheet.PageSetup). The With… End With block ends on row #9, with the End With statement.
The object that appears on the opening statement is the page setup description of the active sheet. The object is obtained by using the Worksheet.PageSetup property. In this particular situation, the Worksheet object is the active sheet (ActiveSheet).
The PageSetup.CenterHeader property allows you to set what appears in the center section of the page header.
You specify the text that you want to appear in this section as a string. In the example above, the text that I specify as header is “Sample Excel File Saved As PDF”.
You can generally apply the syntax and explanation I provide here for the CenterHeader property to the following (roughly equivalent) properties:
You can use the PageSetup.Orientation property for purposes of setting the xlPageOrientation value that specifies the page orientation. The Orientation property can take the following 2 values:
By using the PageSetup.PrintArea property, you can set the print area.
You generally specify the relevant range using A1-style references and as a string. Under the A1-style cell referencing style, you use letters for column headings and numbers for rows.
In the case of the sample macro Save_Excel_As_PDF_3, I use the PrintArea property to specify the range to be converted to PDF. This range is from cells B5 to F105.
The PageSetup.PrintTitleRows property allows you to specify that a particular row (or set of rows) should be repeated at the top of each page.
Generally, you specify the rows to be repeated as a string using A1-style references. In this particular example, I don't explicitly type A1-style references in the code. Let's take a look at what “ActiveSheet.Rows(5).Address” does:
In other words, “ActiveSheet.Rows(5).Address” also returns an A1-style address as a string, which is precisely what the PrintTitleRows property requires.
You can generally use the same principles and syntax that I explain here in connection with the (roughly equivalent) PageSetup.PrintTitleColumns property.
By using the PageSetup.Zoom property, you can determine how Excel scales the relevant Excel worksheet for printing or, in this case, conversion to PDF.
As a general rule, you specify the zoom as a Variant value between 10 and 400 percent.
However, notice that in the sample Save_Excel_As_PDF_3 macro, I set the Zoom property to False. Whenever PageSetup.Zoom is set to False, the properties that determine how Excel scales the Excel worksheet are PageSetup.FitToPagesWide and PageSetup.FitToPagesTall. At the same time, if the PageSetup.Zoom property is True, both the FitToPagesWide and FitToPagesTall properties are ignored.
This leads us to…
You can use the PageSetup.FitToPagesTall property for purposes of setting the number of pages tall to which the relevant Excel worksheet is scaled to when printing or, in the case of the example in this tutorial, converted to PDF.
In the example we're taking a look at, I set the FitToPagesTall property to False. In such cases, Excel scales the worksheet in accordance with the FitToPagesWide property. This is done in…
The syntax and purpose of the PageSetup.FitToPagesWide property is roughly the same as that of the FitToPagesTall property above.
In other words, you use the FitToPagesWide property to specify the number of pages wide the relevant Excel worksheet is scaled to when printing or converting to PDF (as in this example). In the sample Save_Excel_As_PDF_3 macro, I set this value to 1 (page wide).
As anticipated above, this End With statement simply ends the With… End With block that we've just analyzed.
The Save_Excel_As_PDF_3 uses the ExportAsFixedFormat method in exactly the same way as the previous example #2 (Save_Excel_As_PDF_2), to the point that the relevant block of VBA code is exactly the same.
As I show in this example, you can make several changes or adjustments that influence the resulting PDF file by using the PageSetup object. Let's take a look at the results of executing the Save_Excel_As_PDF_3 macro on the same Excel worksheet and note down the main differences:
Note the effects of each of the properties of the PageSetup object that the sample Save_Excel_As_PDF_3 macro uses:
If you go back up, you'll notice that both previous examples (macro #1 and macro #2) didn't fit the print area appropriately to the page. The fifth column (Favorite Food Ingredient) was left out (in macro example #2) or published by itself in the last pages of the PDF file (in macro example #1). This problem is solved by using the Zoom, FitToPagesTall and (particularly) FitToPagesWide properties of the PageSetup object. You can go back to rows #6, #7 and #8 above to see how the VBA code looks like.
Parameter #2 of the ExportAsFixedFormat method that I explain above is Filename. You can use this parameter to set the filename of the resulting PDF file. Visual Basic for Applications allows you to do either of the following:
In the previous macro examples, I've not done much with the Filename parameter. More precisely:
This resulted in the converted PDF files being saved in the default folder (the Documents folder, in my case).
The use of the Filename that I make in these first 3 examples may work for you in certain circumstances. After all, the material structure of those macros is enough to help you save Excel worksheets, workbooks, ranges or charts as PDF files.
However, in several situations, you'll want to use the Filename parameter in a slightly different way for purposes of creating more sophisticated macros that save Excel files as PDF. Let's take a look at some different ways you can use the Filename parameter:
If you're creating a macro to save Excel files as PDF, and those PDF files must always be saved in the same folder, you can simply hard-code the relevant file path and name using the Filename parameter of ExportAsFixedFormat.
Take a look, for example, at the VBA Sub procedure, named Save_Excel_As_PDF_4, which appears below.
This is, substantially, the same as example #3 above (Save_Excel_As_PDF_3). Notice, however, how I've specified the full path of the file using the Filename argument.
The consequence of this change is that the resulting PDF file is no longer saved in the default folder. Instead, the PDF is saved in the folder that you specify with the Filename argument. In the example above, the file is saved to a OneDrive folder called Power Spreadsheets.
You don't necessarily need to hard-code all of the details in the Filename parameter. You can, for example, concatenate different items for purposes of building a more flexible filename structure.
The following macro (named Save_Excel_As_PDF_5) example shows the possibilities you have when doing this. Notice how the only difference between this macro and the previous example (Save_Excel_As_PDF_4) is in the Filename parameter.
The consequence of including the statement “ActiveWorkbook.Path &” prior to the actual filename (“\Sample Excel File Saved As PDF 4”) is that, now, Visual Basic for Applications saves the resulting PDF file in the same folder as the active workbook. In other words, if you use this Filename structure, the new PDF file is saved in the same folder as the source Excel workbook.
The reason for this is that the Workbook.Path property returns a string with the complete path to the relevant workbook. In the case of the example above, the relevant workbook is the active Excel workbook (returned by the Application.ActiveWorkbook property).
I use the ampersand operator (&) to concatenate the file path to the active workbook and the filename, which is surrounded by quotations (“”).
You can take this flexibility even further.
For example, the macro below (Save_Excel_As_PDF_6) uses the name of the active worksheet as the name of the converted PDF file.
Notice how the only difference with the previous macro (Save_Excel_As_PDF_5) is the concatenation of the Worksheet.Name property (using the Application.ActiveSheet property to return the name of the active worksheet) as the actual filename within the Filename parameter. The file path continues to be provided by the Workbook.Path property.
As a final example for this particular section, let's take a look at the following macro (Save_Excel_As_PDF_7), which saves the active Excel worksheet as a PDF named after the value in cell F5 of the active sheet.
In this particular case, the macro uses (i) the Application.ActiveSheet and Worksheet.Range properties to return cell F5, and (ii) the Range.Value property to return the value in that cell.
You can achieve similar results using slightly different structures. This includes, for example, naming the (converted) PDF file by concatenating several strings of text.
In some cases (such as when concatenating several strings of text to specify the PDF filename), it may be more appropriate to declare a variable at the beginning of the procedure to store the applicable string.
Whenever you use a worksheet cell as the source for the PDF filename (as in the example above), make sure that the cell contains no forbidden characters. If the cell from which you take the filename contains characters such as \, /, :, ^, ?, “, or |, the macro may not work properly.
The VBA code samples that I provide in this section are just some of the examples of the different ways you can work with (and generate) the Filename argument of the ExportAsFixedFormat method. As you can see, Visual Basic for Applications provides significant flexibility in this regard.
In this blog post, I explain how to save an Excel workbook using VBA. Even though the topic isn't precisely the same as that of this blog post, some of the principles and examples I provide there may be helpful to you when working with the Filename parameter of the ExportAsFixedFormat VBA method.
You can use the Application.GetSaveAsFilename method to allow your PDF-saving macro to get a filename from the user. This allows the user of your VBA application to specify the full path and filename.
I cover the Application.GetSaveAsFilename method in detail in my Excel tutorial about the topic (I link to it above). For purposes of this macro example, it's enough to know that the GetSaveAsFilename method:
The following sample macro (Save_Excel_As_PDF_8) shows how you can use the Application.GetSaveAsFilename method to provide the Filename parameter when using the ExportAsFixedFormat method:
Let's take a closer look at the VBA code behind the Save_Excel_As_PDF_8 macro to understand what's going on:
First, notice that 2 blocks of VBA code look are quite familiar:
The 2 blocks of code I highlight in the image above are substantially the same as those that appear in other examples of VBA code within this Excel tutorial. More precisely:
The only difference is in the Filename parameter, which is the topic we're focusing on in this section.
Considering that I explain each of these 2 blocks of VBA code in detail in the previous sections of this Excel tutorial, we can now go through each of the blocks of code in the Save_Excel_As_PDF_8 macro:
The purpose of this line is to declare a variable called PdfFilename.
I cover the topic of VBA variables in more detail here and here. A variable is simply a storage location that you pair with a name. You use the variable to represent a certain value.
In this example, I use the PdfFilename variable for purposes of storing the filename that the user enters into the Save As dialog box (displayed by the Application.GetSaveAsFilename method in block #3 below). The variable is later used in the Filename parameter of the ExportAsFixedFormat in block #3 below.
The PdfFilename variable is of the Variant data type. Variant:
Even though Variant isn't the most efficient data type, it's probably the most desirable for this particular case. The reason for this is that the GetSaveAsFilename method can return different types. For example:
Variant is able to handle both of these situations and allows your macro to be more flexible.
This statement assigns a value to the PdfFilename variable declared in block #1 above.
The value that is assigned to PdfFilename is the file name provided by the user through the Save As dialog. This Save As dialog box is displayed by the Application.GetSaveAsFilename method.
In this particular example, the GetSaveAsFilename method uses the following 3 parameters:
The following screenshot shows the Save As dialog box that is displayed by the GetSaveAsFilename method that uses the parameters above. Notice the effect of each of the arguments that I list above:
The third main block of VBA code within the sample Save_Excel_As_PDF_8 macro is an If… Then statement.
From a broad perspective, an If… Then statement does 2 things:
The If… Then statement within the example macro above can be broken down into the 3 following sections:
Section #1: If PdfFilename <> False Then.
The condition tested by this particular statement is whether the variable PdfFilename is different from False.
If the condition is met (PdfFilename isn't False), the statements within the If… Then block are executed. These statements are the ones that adjust the page setup (as explained in macro example #3 above) and export the active worksheet to a PDF file (as I explain in macro examples #1 and #2 above).
If the condition isn't met (PdfFilename is False), the statements within the If… Then block aren't executed. In such a case, execution of the macro ends without the Excel worksheet being converted to PDF.
As I explain above, if the user of the VBA application cancels the Save As dialog box, the GetSaveAsFilename method returns False. Therefore, PdfFilename is False whenever the user cancels the Save As dialog box. In such cases, you probably don't want to proceed with the process of saving the Excel file as PDF.
Section #2: With… End With Statement And Worksheet.ExportAsFixedFormat Method.
These statements are executed when the condition tested by the If… Then statement above (PdfFilename <> False) is met.
I explain all of these statements in great detail within the previous macro examples (here and here).
One of these rows of VBA code is, however, not exactly the same as in the previous examples:
Filename:=PdfFilename
This is the line that specifies the value of the Filename parameter of the ExportAsFixedFormat method. In other words, this line is the one that specifies the filename of the PDF file that results from the conversion.
In this particular case, the filename that is assigned to the PDF file is the value of the PdfFilename variable. In turn, the value of the PdfFilename is determined by the Application.GetSaveAsFilename method in block #2 above.
As a consequence of this, the filename is simply the one that is specified by the user of the application by using the Save As dialog displayed by the GetSaveAsFilename method.
For example, in the situation displayed in the following screenshot, if you press the Save button (on the lower right corner of the dialog box), the PDF file is saved under the filename “Sample Excel File Saved As PDF 5”.
Section #3: End If.
The last row of the If… Then statement (and the sample macro in general) signals the end of the If… Then statement.
As a general rule, whenever you want to save all of the worksheets within an Excel workbook, you can simply use the Workbook.ExportAsFixedFormat method. The explanations I provide throughout this Excel tutorial (particularly the first section) are generally applicable to the Workbook.ExportAsFixedFormat method.
For example, the following (very simple) macro (Save_Excel_As_PDF_9) is the equivalent to the one in example #1 above.
The difference between the 2 macros is that:
However, when you use the Workbook.ExportAsFixedFormat method, all the worksheets are exported to the same PDF file. This may be what you want in certain situations.
However, in other situations, you may want to save each worksheet within a workbook in a separate file. You can achieve this by working with the Worksheet.ExportAsFixedFormat method and loops.
In the context of the ExportAsFixedFormat method and saving Excel files as PDF, the following 2 are the main things you must understand in connection to loops:
When properly structured, a loop allows your VBA code to go through every single worksheet and saves it as a separate PDF file.
Let's take a look at a macro that combines the ExportAsFixedFormat method with loops in order to save each worksheet as a separate PDF file:
Notice how most of the VBA code in the sample Save_Excel_As_PDF_10 macro is (once more) substantially the same as that of previous macro examples in this tutorial. The following screenshot shows the new items, which I explain further below.
Let's take a closer look at each of the rows that contain new items within this sample macro:
This statement declares a variable called iCounter. The selected data type is Integer which, as explained in this tutorial, can store integers between -32,768 and 32,767.
The iCounter variable is used as a loop counter, a topic I explain further when covering the following rows.
This is the opening statement for the loop. In this particular example, I use a For… Next loop.
This is, however, not the only type of loop you can use for these purposes. You can, for example, (also) use the For Each… Next loop when exporting each worksheet to a separate PDF file.
The initial value of the counter is 1 (iCounter = 1). The end value of the counter, which determines when the looping stops, is specified by the Sheets.Count property. The value returned by Worksheets.Count is the number of worksheets.
Each time the VBA code goes through the loop, the value of the iCounter variable increases by 1.
As a consequence of the above, the macro loops a number of times equal to the number of worksheets in the active workbook.
As I explain above, this is the opening statement of the With… End With block that adjusts the page setup. All of the statements within a With… End With block (rows #4 to #10) make reference to the object that appears in this statement (Worksheets(iCounter).PageSetup).
In previous examples, this statement made reference to the page setup settings of the active sheet by making reference to “ActiveSheet.PageSetup” instead of “Worksheets(iCounter).PageSetup”.
The difference between both statements is, therefore, on the specific Worksheet object used within the syntax of the Worksheet.PageSetup property.
I explain Rows #4 to #11 in example #3 above.
The purpose of this block is to adjust the relevant properties of the PageSetup object for the relevant worksheet (as determined by the loop).
I explain, in detail, this statement and all the parameters of the ExportAsFixedFormat method used therein in a previous section of this tutorial.
There are, however, 2 rows that differ from what appears in the previous examples:
In both cases, the difference is in the use of the Worksheets collection and an index (Worksheets(iCounter)) to refer to the relevant worksheet.
The reason for this is, as I explain above, related to the For… Next loop. More precisely:
After reading this Excel tutorial, you're ready to start creating macros for purposes of saving Excel files as PDF.
This Excel VBA Save as PDF Tutorial is accompanied by files containing the data and macros I use in the examples above. You can get immediate free access to these example files by clicking the button below.
In addition to having a very good understanding of the ExportAsFixedFormat VBA method, you've also seen how you can:
In addition to the above, you've seen 10 different examples of VBA code that allow you to save Excel files as PDF using several different settings and parameters.
As with most topics related to Excel and VBA, I've not exhausted the topic despite writing this very comprehensive tutorial. You can use the knowledge and ideas you've gained here in order to create different macros that achieve similar purposes.
