And i got this code, Sub Create_PDF() Dim MyFullName As String Application.ScreenUpdating = False MyFullName = Left(ThisWorkbook.FullName, InStr(1, ThisWorkbook.FullName, '.' ) - 1) ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _ Quality:=xlQualityMinimum. PDF stands for portable document format. It is a format that every operating system on virtually any computer can read. Its become the default standard for transferring and viewing files in an easy way. The problem is that Visual Basic has no built in VB6 PDF functions. Because of this it has been very hard in the past to create PDF files from VB. I am working with VB6.0 and I have a requirenment to create a password protected pdf file in vb6.0. I have successfully created a PDF file but how do i make it password protected?
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:
- Provide a thorough introduction to how you can use Visual Basic for Applications for purposes of adjusting the page setup attributes.
- Introduce some different ways in which you can specify the filename of the PDF file that results after the Excel to PDF conversion.
- Explain how you can save each worksheet from an Excel workbook in a separate PDF file.
The following table of contents lists the different sections of this blog post.
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 subscribing to the Power Spreadsheets Newsletter.
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:
How To Save An Excel File As PDF Using VBA: The ExportAsFixedFormat Method
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 Workbook.ExportAsFixedFormat method, when saving an Excel workbook as PDF.
- The Worksheet.ExportAsFixedFormat method, if you're saving a worksheet as PDF.
- The Range.ExportAsFixedFormat method, when working with a Range object.
- The Chart.ExportAsFixedFormat method, if saving a chart 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:
Parameter #1: Type
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:
- File Type #1: xlTypePDF, represented by the value of 0.
xlTypePDF corresponds to PDF files, the subject of this Excel tutorial. - File Type #2: xlTypeXPS, whose value is 1.
xlTypeXPS corresponds to XPS files, Microsoft's alternative to PDF.
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, as explained at How-To Geek, 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.
Parameter #2: Filename
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:
- Option #1: You can specify the full path and filename of the resulting file.
- Option #2: You can omit the full file path and, instead, just include the filename. In this case, Excel simply saves the converted file in the current default folder.
Filename is a string.
Parameter #3: Quality
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:
- Quality Option #1: xlQualityStandard, whose value is 0.
This is, simply, standard file quality. - Quality Option #2: xlQualityMinimum (value is 0).
This represents minimum file quality.
Parameter #4: IncludeDocProperties
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:
- True: Includes the document properties.
- False: Doesn't include the document properties.
Parameter #5: IgnorePrintAreas
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:
- True: Ignore print areas.
- False: Don't ignore print areas.
Parameters #6 And #7: From And To
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:
- From is the number of the page at which Visual Basic for Applications starts to publish.
If the From argument is omitted, VBA starts at the beginning. - To is the number of the last page that VBA publishes.
If you omit the To argument, publishing goes on until the last page.
Parameter #8: OpenAfterPublish
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:
- True: Display file in PDF viewer after conversion.
- False: Don't display file in PDF viewer after conversion.
Parameter #9: FixedFormatExtClassPtr
As explained at the Microsoft Dev Center, the FixedFormatExtClassPtr parameter of the ExportAsFixedFormat method is a pointer to the FixedFormatExt class.
How To Save An Excel File As PDF Using VBA: Basic Code Examples (Examples #1 And #2)
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.
I generated all of the data in this Excel file using random-name-generator.info and www.randomlists.com.
How To Save An Excel File As PDF Using VBA: Code Example #1
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:
Item #1: ActiveSheet
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:
- Make reference to a Workbook object, and use the Workbook.ExportAsFixedFormat method.
- Use a variable that represents a Range object, and use the Range.ExportAsFixedFormat method.
- Use a representation of a Chart object, and work with the Chart.ExportAsFixedFormat method.
Item #2: ExportAsFixedFormat
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…
Item #3: Type:=xlTypePDF
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.
Excel To PDF Conversion Results: Example #1
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:
- The resulting page layout is not accurate.
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: - The converted PDF file is saved automatically to the default local file location (in this example, the Documents folder) and the file name is the same as that of the original Excel workbook (in this case, Book1).
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:
How To Save An Excel File As PDF Using VBA: Code Example #2
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:
Lines #1 And #2: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
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:
- Tell VBA that the ExportAsFixedFormat method should be applied to the current active worksheet.
- Set PDF as the file format to which the worksheet should be exported to.
Line #3: Filename:=”Sample Excel File Saved As PDF”
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.
Line #4: Quality:=xlQualityStandard
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:
- xlQualityStandard (or 0), as in this case, which specifies standard quality.
- xlQualityMinimum (or 1), which represents minimum quality.
Line #5: IncludeDocProperties:=False
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:
- True indicates that the document properties should be included in the resulting file.
- False (as in this case) indicates that the document properties shouldn't be included.
Line #6: IgnorePrintAreas:=False
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.
- If IgnorePrintAreas is set to True, the macro ignores any print areas.
- If you set IgnorePrintAreas to False (as in this example) the macro uses the print areas set when publishing.
Lines #7 And #8: From:=1, To:=5
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:
- From allows you to set the page at which publishing begins.
- To allows you to determine the last page that is published.
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:
- If the omitted parameter is From, publishing begins at the beginning.
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. - If you omit the To parameter, publishing goes all the way to the last page.
In the Save_Excel_As_PDF_2 macro, I've set the To parameter to 5. This isn't the last page.
Therefore, if I omit the To parameter, the conversion results differ. Instead of stopping the publishing process at page 5 (as the sample VBA code above does), Visual Basic for Applications would only end publishing at the last page.
Line #9: OpenAfterPublish:=True
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.
Excel To PDF Conversion Results: Example #2
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:
- The name of the file, as determined by the Filename parameter is “Sample Excel File Saved As PDF”.
- Publishing goes from pages 1 through 5, as specified using the From and To parameters.
- The PDF file is displayed automatically when the macro is executed and immediately after the conversion process is completed.
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…
How To Adjust The Page Setup To Save An Excel File As PDF Using VBA: The PageSetup Object
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:
- “Represents the page setup description”; and
- Contains all of the page setup attributes.
Each of these page setup attributes is a property of 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:
The PageSetup Object: Properties
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 left section of the header. |
| 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. |
You can find this (and more) information about these properties at the Microsoft Dev Center. Note that, in my opinion, the information at the Microsoft Developer Network (as of the time I'm writing this blog post) has some contradictions.
The following are 2 examples of these differences:
- The basic descriptions of the LeftFooter and LeftHeader properties (“returns or sets the alignment of text”) are different from those of the RightFooter (“returns or sets the distance (…) between the right edge of the page and the right boundary of the footer”) and RightHeader (“returns or sets the right part of the header”) properties.
- The description of the LeftFooterPicture, LeftHeader Picture and RightFooterPicture properties (“returns a Graphic object that represents the picture”) differs from that of the RightHeader (“specifies the graphic image that should be displayed).
Anyway, this shouldn't stop you from working with the PageSetup object to improve your macros that convert Excel files to PDF.
Therefore, let's take a look at how you can use the PageSetup VBA object for purposes of improving the results obtained when saving an Excel file as PDF with the ExportAsFixedFormat method.
How To Save An Excel File As PDF Using VBA: Code Example #3
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:
Row #1: With ActiveSheet.PageSetup
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).
Row #2: .CenterHeader = “Sample Excel File Saved As PDF”
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:
- The PageSetup.CenterFooter property.
- The PageSetup.LeftFooter property.
- The PageSetup.LeftHeader property.
- The PageSetup.RightFooter property.
- The PageSetup.RightHeader property.
Row #3: .Orientation = xlPortrait
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:
- xlPortrait or 0, which (as in this example) represents portrait mode.
- xlLandscape or 1, which represents landscape mode.
Row #4: .PrintArea = “$B$5:$F$105”
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.
Row #5: .PrintTitleRows = ActiveSheet.Rows(5).Address
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:
- ActiveSheet.Rows(5): Here, I use the Worksheet.Rows property to represent Row 5 of the active worksheet.
- Address: This makes reference to the Range.Address property, which returns a string representing the relevant range reference.
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.
Row #6: .Zoom = False
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…
Row #7: .FitToPagesTall = False
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…
Row #8: .FitToPagesWide = 1
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).
Row #9: End With
As anticipated above, this End With statement simply ends the With… End With block that we've just analyzed.
Excel To PDF Conversion Results: Example #3
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:
- The center section of the header says “Sample Excel File Saved As PDF”, as required by the PageSetup.CenterHeader property in row #2 above.
- The printed area doesn't include the Power Spreadsheets logo, the preliminary title of this blog post or the hyperlinked text to this page which appear in the first 3 rows of the Excel worksheet. This is a direct consequence of using the PageSetup.PrintArea property to specify the range from cells B5 to F105 as the print area, as done in row #4 above.
- The header row of the Excel table is repeated in all of the pages within the converted PDF file. This is achieved by the appropriate use of the PageSetup.PrintTitleRows property in row #5 above.
- All of the columns in the original Excel table appear in an appropriate manner. In other words, the table that appears in the PDF file has been scaled in such a way that it is 1 page wide.
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.
How To Change The Filename When Saving An Excel File As PDF Using VBA: How To Work With The Filename Parameter Of The ExportAsFixedFormat Method
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:
- Specify the full path and filename of the file.
- Specify just the filename. In this case, the file is saved in the current default folder.
In the previous macro examples, I've not done much with the Filename parameter. More precisely:
- In the first macro example, I didn't even include the Filename parameter (it's optional).
- In the (slightly more sophisticated) macros #2 (Save_Excel_As_PDF_2) and #3 (Save_Excel_As_PDF_3), I only included the filename (without the path).
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:
Use #1 (Examples #4, #5, #6 And #7): How To Specify The Full Path And Name Of A PDF File Using The Filename Parameter Of The ExportAsFixedFormat Method
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 within that cell.
You can achieve similar results using slightly different structures. For example, in this forum thread at Chandoo.org, user Luke M. provides a VBA code example for converting a worksheet to PDF and name it by concatenating the text in 3 separate cells.
Luke M.'s sample code declares a variable at the beginning of the procedure to store the string representing that filename. In some circumstances, you may find that making such declaration (instead of simply using the Range.Value property directly in the Filename parameter as I do above) is more appropriate.
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.
Use #2 (Example #8): How To Name A PDF File Using The Application.GetSaveAsFilename 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:
- Displays the Save As dialog box.
- Gets the filename that the user enters in the Save As dialog box.
- Doesn't actually save or export a file.
This is the reason why, as shown below, you must anyway use the ExportAsFixedFormat method for purposes of saving an Excel workbook as PDF.
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:
Excel MVP Debra Dalgleish, at Contextures.com, suggests a similar (and slightly more advanced) example of VBA code to export the active sheet as a PDF file (including use of the GetSaveAsFilename method). That VBA code is, in fact, the basis for some statements of the sample macro above (Save_Excel_As_PDF_8).
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:
- Block #1 is a With… End With block that appears in macro examples #3 (where I explain each line of code), #4, #5, #6 and #7.
- Block #2, which uses the Worksheet.ExportAsFixedFormat method, is substantially the same as the equivalent blocks of code in macro examples #2 (where it is explained in detail), #3, #4, #5, #6 and #7.
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:
Block #1: Dim PdfFilename As Variant
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 is the default data type. As explained by Excel guru John Walkenbach in Excel VBA Programming for Dummies, it's main characteristic is the fact that it changes type depending on the particular scenario.
Even though Variant isn't the most efficient data type, it's probably the most desirable for this particular case. The reason for this, as explained at cpearson.com, is that the GetSaveAsFilename method can return different types. For example:
- If the user cancels the Save As dialog box displayed by the GetSaveAsFilename method, the returned value is a Boolean (False).
- If the user provides the filename that should be used to save the converted PDF file, GetSaveAsFilename returns a String.
Variant is able to handle both of these situations and allows your macro to be more flexible.
Block #2: PdfFilename = Application.GetSaveAsFilename( InitialFileName:=”Sample Excel File Saved As PDF 5″, FileFilter:=”PDF, *.pdf”, Title:=”Save As PDF”)
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:
- InitialFileName: Specifies a suggested/default filename.
In this example, the default filename is “Sample Excel File Saved As PDF 5”. - FileFilter: Specifies the file filtering criteria.
In the example above, the only file filter makes reference to PDF files. - Title: Determines the title of the Save As dialog box that is actually displayed.
In the sample Save_Excel_As_PDF_8 macro, the title of the dialog box is “Save As PDF”.
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:
Block #3: If… Then Statement
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:
- Step #1: Tests whether a condition is met.
- Step #2: If the condition is met, performs one action. If the condition isn't met, performs no action.
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.
Vb Instr Examples
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.
How To Save Each Worksheet As A Separate PDF Using VBA: How To Use Loops With The ExportAsFixedFormat Method (Examples #9 And #10)
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:
- The macro in example #1 uses (i) the Application.ActiveSheet property to return the active sheet and (ii) the Worksheet.ExportAsFixedFormat method to export the worksheet to a PDF file.
- The example in this section uses (i) the Application.ActiveWorkbook property to return the active sheet and (ii) the Workbook.ExportAsFixedFormat method to convert the whole workbook to PDF.
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:
- The term looping, in very broad terms, makes reference to the execution of certain statements several (more than 1) time.
- Loops help you to save each worksheet as a separate PDF file because of the way they proceed. More precisely, such a loop proceeds (very roughly) as follows:
Step #1: Executes all of the statements that save an Excel worksheet as PDF.
Step #2: Moves on to the next worksheet.
Step #3: Goes back to step #1 above. In other words, executes (again) all of the statements that save the Excel worksheet as PDF.
When properly structured, a loop allows your VBA code to go through every single worksheet and saves it as a separate PDF file.
If you're not familiar with loops, or wish to learn more about them, you can check out this detailed Excel tutorial about that topic.
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:
This particular macro is based, partially, on a suggestion from IT trainer Alan Murray.
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:
Row #1: Dim iCounter As Integer
Create Pdf File From Vb6 Instrument
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.
Row #2: For iCounter = 1 To Worksheets.Count
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. For example, this macro to export each worksheet to a separate PDF uses a For Each… Next loop.
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.
Row #3: With Worksheets(iCounter).PageSetup
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.
- ActiveSheet makes reference to the current active worksheet.
- Worksheets(iCounter) makes reference to the iCounter-th element of the Worksheets collection.
As explained at the Microsoft Dev Center, the iCounter variable in the statement above is a worksheet index number. The specific number is determined by how many times the VBA code has gone through the loop. Therefore:
On the first loop, iCounter is equal to 1. Therefore, Worksheets(iCounter) makes reference to the first worksheet in the workbook (Worksheets(1)).
The second time, iCounter is equal to 2. In such a case, Worksheets(iCounter) is the equivalent of Worksheets(2) and refers to the second worksheet within the workbook.
… and so on, until…
On the last loop, iCounter is equal to the number of worksheets in the workbook. Therefore, Worksheets(iCounter) makes reference to the last worksheet in the workbook.
Rows #4 To #11: With… End With Block
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).
Rows #12 To #20: Worksheets(iCounter).ExportAsFixedFormat
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:
- Row #12: Worksheets(iCounter).ExportAsFixedFormat.
- Row #14: Filename:=ActiveWorkbook.Path & “” & Worksheets(iCounter).Name.
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:
- The statement “Worksheets(iCounter).ExportAsFixedFormat” exports the relevant sheet (as determined by the current loop number) to PDF.
- The use of “Worksheets(iCounter).Name” within the value of the Filename parameter sets the name of the resulting PDF file to be equal to the name of the current worksheet (which depends on the current loop number).
This is very similar to the Filename parameter value used in example #6 above, where the name of the active sheet is used to name the relevant PDF file. However, in this particular macro (Save_Excel_As_PDF_10), the use of a loop means that each worksheet is saved under its own worksheet name.
Conclusion
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 subscribing to the Power Spreadsheets Newsletter.
In addition to having a very good understanding of the ExportAsFixedFormat VBA method, you've also seen how you can:
- Combine the ExportAsFixedFormat method with the PageSetup object in order to adjust the page setup attributes prior to the Excel to PDF conversion.
- Use different methods for determining the filename of the PDF file you save.
- Use loops for purposes of saving each worksheet as a separate PDF file.
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.
Books Referenced In This Excel Tutorial
Create Pdf File From Jpg Online
Click on the image below to purchase the book at Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com.