Excel VBA Save As PDF: Step-By-Step Guide

And 10 Code Examples To Export Excel To

PDF
By Jorge A. Gomez — 0 Comments

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 Fles to Excel.

The reason why I write about PDF is relatively

straightforward: PDF is one of the most widely used <le formats
formats.

This particular Excel tutorial also focuses on the topic of working with Excel and PDF Fles. More precisely, I
explain in detail how you can save Excel <les as PDF using VBA, and provide 10 examples of
VBA code that you can start using immediately
immediately.

In fact, in this blog post, I go much further than simply showing you how to simply convert Excel
<les to PDF
PDF. Among others, I also
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 <lename of the PDF <le that
results after the Excel to PDF conversion.
 Explain how you can save each worksheet from an Excel workbook in a separate PDF Fle.

The following table of contents lists the different sections of this blog post.

1 How To Save An Excel File As PDF Using VBA: The ExportAsFixedFormat Method
1.1 Parameter #1: Type
1.2 Parameter #2: Filename
1.3 Parameter #3: Quality
1.4 Parameter #4: IncludeDocProperties
1.5 Parameter #5: IgnorePrintAreas
1.6 Parameters #6 And #7: From And To
1.7 Parameter #8: OpenAfterPublish
1.8 Parameter #9: FixedFormatExtClassPtr
2 How To Save An Excel File As PDF Using VBA: Basic Code Examples (Examples #1 And #2)
2.1 How To Save An Excel File As PDF Using VBA: Code Example #1
2.2 How To Save An Excel File As PDF Using VBA: Code Example #2
3 How To Adjust The Page Setup To Save An Excel File As PDF Using VBA: The PageSetup Object
3.1 The PageSetup Object: Properties
3.2 How To Save An Excel File As PDF Using VBA: Code Example #3
4 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
4.1 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
4.2 Use #2 (Example #8): How To Name A PDF File Using The Application.GetSaveAsFilename Method
5 How To Save Each Worksheet As A Separate PDF Using VBA: How To Use Loops With The
ExportAsFixedFormat Method (Examples #9 And #10)
6 Conclusion
7 Which macros do you use for purposes of saving Excel <les as PDF?

If you want to download (for free) the Excel workbook containing all of the macro samples that
I explain in this post
post, along with the sample PDF Fles showing the actual conversions, click here.

The basic VBA method you use for converting Excel Fles 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 <le, worksheet, cell range or chart) to another <le format
format, usually PDF.
You can apply the ExportAsFixedFormat method to several Excel objects
objects. To be more precise,
you’ll generally be working with 1 of the following versions of this method
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
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

parameters. Let’s take a look at each of them:

Parameter #1: Type

You use the Type parameter to specify the type of Fle format to which the Excel Fle must be exported to. In
other words, you determine in which <le format the new (resulting) <le is saved by specifying it in
the Type parameter.

In order to specify the type of Fle you want to convert the Excel Fle to, you use the xlFixedFormatType
enumeration. There are 2 types of <le formats you can choose from:

File Type #1: xlTypePDF, represented by the value of 0.

xlTypePDF corresponds to PDF Fles, the subject of this Excel tutorial.

File Type #2: xlTypeXPS, whose value is 1.

 xlTypeXPS corresponds to XPS Fles, 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 Fle 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 Fles.

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 <lename of the new (converted) <le
<le.

When specifying the Filename parameter, you have 2 broad options

options:

Option #1: You can specify the full path and Flename of the resulting Fle.

Option #2: You can omit the full Fle path and, instead, just include the Flename. In this case, Excel
simply saves the converted Fle in the current default folder.

Filename is a string
string.

Parameter #3: Quality

You have the option of choosing the quality of the resulting PDF <le (standard or minimum)
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 Fle quality.

Quality Option #2: xlQualityMinimum (value is 0).

This represents minimum Fle 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 <le 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 Fle.

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
end. More precisely:

From is the number of the page at which Visual Basic for Applications starts to publish
publish.

If the From argument is omitted, VBA starts at the beginning.

To is the number of the last page that VBA publishes

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 <le should be displayed in the PDF viewer as soon as the export
process is completed.

You specify whether the Fle is displayed or not by setting the OpenAfterPublish parameter to True or
False
False, as follows:

True: Display Fle in PDF viewer after conversion.

False: Don’t display Fle 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
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 Fle 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 Fle
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
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 speciFed) 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 <le
<le.

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 <le format to which the relevant worksheet should be exported to
to.

You specify the relevant Fle 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 <les 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 <les to PDF
PDF.

The reason for this is that, as I show below, the macro (literally) just saves the active worksheet to
a PDF <le
<le. 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 Fle that accompanies this tutorial as PDF. The following image shows the resulting
PDF Fle:

As Frst glance, the resulting PDF Fles looks OK. However, there are several things that can be
improved
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 Fle is saved automatically to the default local Fle location (in this example, the
Documents folder) and the Fle 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
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 Frst sample macro I provide above
(Save_Excel_As_PDF_1).
These <rst 2 lines are the whole body of that previous macro
macro. In this particular case, they have the
same purpose as that which I explain above
above. Namely:

Tell VBA that the ExportAsFixedFormat method should be applied to the current active worksheet.

Set PDF as the Fle 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 <nal PDF <le
<le.

As I explain above, the previous sample macro simply used the name of the active Excel workbook to name
the PDF Fle that was created after executing the ExportAsFixedFormat method. By using the Filename
parameter, you can choose a different Flename.

In this particular case, the Flename that I’m using is quite simple and only includes the actual Flename.
Therefore, the PDF Fle is saved in the default Fle location.

However, as I explain above, you can use this same parameter for purposes of determining the
full path of the converted PDF Fle.

Line #4: Quality:=xlQualityStandard

This line of VBA code determines the quality of the published PDF <le
<le. The Quality parameter of the
ExportAsFixedFormat can be set to either of 2 values
values:

xlQualityStandard (or 0), as in this case, which speciFes 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
not. IncludeDocProperties can be
set to either of the following 2 values
values:

True indicates that the document properties should be included in the resulting Fle.

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
etermine
whether any print areas that are set when publishing should be ignored or not
not. Just as the
previous parameter (IncludeDocProperties), IgnorePrintAreas can be set to either True or False
False.

If IgnorePrintAreas is set to True

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 <rst and last pages that are published in the converted <le
<le. 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 <le in the PDF viewer once the
conversion process has been completed
completed.

You can also set OpenAfterPublish to False

False. In this case, the published PDF Fle isn’t displayed.

Excel To PDF Conversion Results: Example #2

For purposes of comparing the results obtained by the Frst 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 Frst 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 <le

<le, as determined by the Filename parameter is “Sample Excel File Saved As PDF”.

Publishing goes from pages 1 through 55, as speciFed using the From and To parameters.

The PDF <le 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
method.

You may notice, however, that there are a few things that you can’t <x with the
ExportAsFixedFormat method alone
alone. This includes, among others, one that I mention above:

The page layout isn’t properly adjusted. Therefore, the resulting PDF Fle only shows the Frst 4 columns of
the table within the original Excel source Fle. 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 <les as PDF
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 Fle 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 Fle 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
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
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
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
purpose.
Some of these properties aren’t relevant for the purpose of this Excel tutorial on how to save an Excel Fle 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 SpeciFes whether the sheet is centered horizontally when printed.

CenterVertically SpeciFes whether the sheet is centered vertically when printed.

DifferentFirstPageHeaderFooter Determines whether the header or footer on the Frst page is different.

OddAndEvenPagesHeaderFooter SpeciFes 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 qualiFer, returns an Application object

representing the creator of that object.

BlackAndWhite SpeciFes 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 SpeciFes 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 Frst page of an Excel

workbook (or a section).

FirstPageNumber Returns or sets the Frst 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 Fle when the size changes.

Zoom Returns or sets a value representing the percentage by which Excel

scales the Excel worksheet for printing.

You can <nd 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 (“speciFes
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 Fles 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 Fle 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 Fle 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 <rst section of the macro
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 quali<cation 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
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
header.

You specify the text that you want to appear in this section as a string
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
speci<es the page orientation
orientation. The Orientation property can take the following 2 values
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
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
page.

Generally, you specify the rows to be repeated as a string using A1-style references
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

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
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
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
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 innuence the resulting PDF
Fle 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 Frst 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 Fle.
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 Fle 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 Ft the
print area appropriately to the page. The Ffth column (Favorite Food Ingredient) was left out (in macro
example #2) or published by itself in the last pages of the PDF Fle (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 <lename of the resulting PDF <le
<le. Visual Basic for Applications allows you to
do either of the following
following:

Specify the full path and <lename of the Fle.

Specify just the <lename

<lename. In this case, the Fle 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 Frst 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 Flename (without the path).

This resulted in the converted PDF Fles being saved in the default folder (the Documents folder, in my
case).

The use of the Filename that I make in these Frst 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 Fles.

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 Fles 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 Fles as PDF, and those PDF Fles must always be saved in the same
folder, you can simply hard-code the relevant <le 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
speciFed the full path of the Fle using the Filename argument.

The consequence of this change is that the resulting PDF Fle 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 Fle 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 kexible <lename
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 Flename
(“\Sample Excel File Saved As PDF 4”) is that, now, Visual Basic for Applications saves the resulting
PDF <le in the same folder as the active workbook
workbook. In other words, if you use this Filename
structure, the new PDF Fle 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
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 Fle path to the active workbook and the
Flename, which is surrounded by quotations (“”).

You can take this nexibility 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 Fle.

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 Flename within the Filename parameter. The Fle path continues to be provided by
the Workbook.Path property.

As a Fnal 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
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 Flename. In some circumstances, you may Fnd 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 Flename (as in the example above), make
sure that the cell contains no forbidden characters
characters. If the cell from which you take the Flename
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 signiFcant nexibility 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 <lename from the user
user. This allows the user of your VBA application to specify the full path and
Flename.

I cover the Application.GetSaveAsFilename method in detail in my Excel tutorial about the

topic (I link to it above)
above). For purposes of this macro example, it’s enough to know that the
GetSaveAsFilename method:

1. Displays the Save As dialog box.

2. Gets the Flename that the user enters in the Save As dialog box.

3. Doesn’t actually save or export a Fle.

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 Fle (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
#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

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
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
name. You use the variable to represent a certain value.

In this example, I use the PdfFilename variable for purposes of storing the <lename 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 efFcient 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
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 Flename that should be used to save the converted PDF Fle,
GetSaveAsFilename returns a String
String.

Variant is able to handle both of these situations and allows your macro to be more nexible.

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 <le name provided by the user through the Save As
dialog
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
parameters:

InitialFileName: SpeciFes a suggested/default Flename.

In this example, the default Flename is “Sample Excel File Saved As PDF 5”.

FileFilter: SpeciFes the Fle Fltering criteria.

In the example above, the only Fle Flter makes reference to PDF Fles.

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

If… Then statement does 2 things
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
False.

If the condition is met (PdfFilename isn’t False), the statements within the If
If… Then block
are executed
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 Fle (as I explain in macro examples #1 and #2 above).

If the condition isn’t met (PdfFilename is False), the statements within the If
If… Then block
aren’t executed
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
box. In such cases, you probably don’t want to proceed with the process of
saving the Excel Fle as PDF.

Section #2: With

With… End With Statement And Worksheet.ExportAsFixedFormat Method.

These statements are executed when the condition tested by the If

If… Then statement above
(PdfFilename <> False) is met
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 speciFes the value of the Filename parameter of the ExportAsFixedFormat method. In
other words, this line is the one that speci<es the <lename of the PDF <le that results from the
conversion.

In this particular case, the Flename that is assigned to the PDF Fle 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 <lename is simply the one that is speci<ed 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 Fle is saved under the Flename “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
If…
Then statement
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
method. The explanations I provide
throughout this Excel tutorial (particularly the Frst 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 Fle.

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 Fle. 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 Fle. You
can achieve this by working with the Worksheet.ExportAsFixedFormat method and loops.

In the context of the ExportAsFixedFormat method and saving Excel Fles as PDF, the following 2 are the
main things you must understand in connection to loops
loops:

The term looping, in very broad terms, makes reference to the execution
xecution of certain statements
several (more than 1) time
time.

Loops help you to save each worksheet as a separate PDF Fle because of the way they proceed. More
precisely, such a loop proceeds (very roughly) as follows
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 Fle.

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
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 Fle:

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

This statement declares a variable called iCounter
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

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
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 speciFed 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 speciFc 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
number. The speci<c number is determined by how many times the VBA
code has gone through the loop
loop. Therefore:
 On the Frst loop, iCounter is equal to 1. Therefore, Worksheets(iCounter) makes reference to the Frst
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
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 Fle 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 Fle. 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 Fles as
PDF. Remember that you can get immediate free access to the Excel workbook (with all the
macros explained above) and the PDF <les accompanying this blog post by clicking here.

In addition to having a very good understanding of the ExportAsFixedFormat VBA method

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
conversion.

Use different methods for determining the <lename of the PDF <le you save.

Use loops for purposes of saving each worksheet as a separate PDF Fle.

In addition to the above, you’ve seen 10 different examples of VBA code that allow you to save
Excel <les 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. Just make sure that you take a few seconds to leave a
comment below sharing with the rest of us
us:

Which macros do you use for purposes of saving

Excel files as PDF?
The 10 examples of VBA code to save Excel Fles as PDF that I provide in this Excel tutorial don’t cover every
possible scenario or situation that you may face when creating macros to convert Excel Fles to PDF.
In fact, you don’t have to search too long in order to start Fnding examples of code showing the different
things you can do once you know how to save Excel Fles as PDF using VBA. The following are some
websites where you can Fnd additional sample macros that save Excel <les as PDF
PDF:

Ron De Bruin’s workbook with 9 VBA code examples to create and mail PDF Fle with Excel. If you’re
using Excel in Mac, Ron has also prepared some sample macros that may work for you (depending
on which version of OfFce for Mac you’re using).

The VBA code suggested at analystcave.com for purposes of printing all Excel Fles within a particular
directory to a subfolder called “pdf”.

As you can see, you really have a lot of nexibility regarding what you can do when saving Excel Fles as PDF
using VBA. I would be quite interested in learning which macros you use for purposes of saving
Excel <les as PDF
PDF. Please make sure to share your comments and suggestions with the rest
of us by leaving a comment below
below.

Books Referenced In This Excel Tutorial

Click on the image below to purchase the book at Amazon.

You may also be interested in...

Excel VBA Copy Paste: R1C1-Style Notation Excel Macro Tutorial for Excel VBA Delete Blank Excel VBA Array: The
The Complete Tutorial And The FormulaR1C1 Beginners: Create Or Empty Rows: 5 Easy- Complete Tutorial for
For Co... Property I... Macros ... To-Us... Beginne...

DO YOU WANT MORE EXCEL TUTORIALS AND RESOURCES?

Get immediate FREE access to the Power Spreadsheets Go-To List of Excel Shortcuts and all
the Excel workbooks accompanying the Power Spreadsheets Excel and VBA tutorials.

Plus, receive FREE updates about new tutorials that will help you improve your Excel skills.

Enter your email here...

GET INSTANT FREE ACCESS

Unsubscribe anytime | 100% Privacy | No Spam

0 Comments Power Spreadsheets !

1 Login

Sort by Best
♥ Recommend ⤤ Share

Start the discussion…

Be the first to comment.

ALSO ON POWER SPREADSHEETS

350 + Excel Keyboard Shortcuts And Hotkeys: Excel Resources

Free PDF Cheat Sheet 22 comments • 7 months ago•

SUBSCRIBE HERE FOR FREE

 Join thousands of Power Excel
Users and receive FREE updates about
new tutorials that will help you improve
your Excel skills.

Get immediate FREE access to Excel

Shortcuts and Hot Keys: The Power
Spreadsheets Cheat Sheet and all the
Excel workbooks accompanying the
Power Spreadsheets Excel and VBA
tutorials.

Enter your email here...

GET INSTANT FREE ACCESS

Unsubscribe anytime | 100% Privacy | No Spam

POWER SPREADSHEETS IN SOCIAL MEDIA

!"#$

MORE THAN 350 EXCEL KEYBOARD

SHORTCUTS FOR FREE

Get immediate free access to Excel Shortcuts and

Hotkeys: The Power Spreadsheets Cheat Sheet
Excel Resources | Excel Shortcuts Contact

Copyright © 2015–2016 Jorge Alberto Gomez Soto. All rights reserved

Privacy Policy | Terms and Conditions | Limit of Liability and Disclaimer of Warranty
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the
Microsoft Corporation.
Some of the links in Power Spreadsheets are affiliate links. If you make a purchase through one of them, I may
receive a commission. This commission comes at no additional cost to you,