Excel VBA: Choose Location and Save as PDF

In this article, I’ll show you how you can choose a location and save your workbook as a PDF using VBA in Excel.


Excel VBA: Choose a Location and Save as PDF File (Quick View)

Sub Choose_Location_and_Save_as_PDF()

PDF_Name = "MyPDF.pdf"

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Desired Location"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

PDF_Name = File_Dialog.SelectedItems(1) & "\" & PDF_Name

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name

End Sub

VBA Code to Choose a Location and Save the File as PDF Using Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Choose a Location and Save the File as PDF (Step-by-Step Analysis)

So, let’s go to our main discussion today without further delay. We’ll break down the VBA code step-by-step to learn how to save the file in the desired location as a PDF using Excel VBA.

⧪ Step 1: Inserting the Necessary Input

First of all, we have to insert the necessary input into the code. There is only one input required in this code. That’s the name of the PDF in which I want the file workbook to save.

I’ve named it MyPDF.pdf. You put it according to your wish.

PDF_Name = "MyPDF.pdf"

Inserting Input Choose a Location and Save the File as PDF Using VBA

⧪ Step 2: Choosing the Desired Location

Next, we need to choose the location to save the PDF. We’ll use a VBA Filedialog object to choose the location.

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Desired Location"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

Choosing the Location Choose a Location and Save the File as PDF Using VBA

⧪ Step 3: Extracting the Desired Location

After choosing the desired location, we’ll allocate it with the name of the file.

PDF_Name = File_Dialog.SelectedItems(1) & "\" & PDF_Name

⧪ Step 4: Saving the PDF File

This is the most important step. We’ll save the active worksheet as a PDF file.

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name

Saving File Choose a Location and Save the File as PDF Using VBA

Note: If you want to save only a particular worksheet, not the whole workbook, use the name of that particular sheet in spite of ActiveWorkbook.

For example, to save Sheet1 only, use:

Worksheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name

Now, the complete VBA code will be:

VBA Code:

Sub Choose_Location_and_Save_as_PDF()

PDF_Name = "MyPDF.pdf"

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Desired Location"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

PDF_Name = File_Dialog.SelectedItems(1) & "\" & PDF_Name

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name

End Sub

VBA Code to Choose a Location and Save the File as PDF Using Excel VBA

Read More: How to Save Excel as PDF (6 Useful Ways)


Developing a Macro to Choose a Location and Save the File as PDF

We’ve seen the step-by-step analysis of the code to choose a location and save the file as a PDF using VBA.

Now let’s see how to develop a Macro to run the code.

Here we’ve got a workbook with the names of some books, their authors and the prices of a bookshop.

We’ll develop a Macro to save this as a PDF file.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

Opening VBA Window

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

Inserting Module Choose a Location and Save the File as PDF Using VBA

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

⧪ Step 4: Running the Code

Click on the Run Macro tool from the toolbar above.

Running the Code Choose a Location and Save the File as PDF Using VBA

The code will run. A file dialog will open asking you to choose the desired location.

Choosing Location Choose a Location and Save the File as PDF Using VBA

Choose the location and click Open. And you’ll find a PDF file created in your desired location (E:\ExcelDemy) with the desired name (MyPDF.pdf).

It contains the same content as the active workbook.

Read More: How to Save Macros in Excel Permanently (2 Suitable Ways)


Things to Remember

Make sure that the workbook on which you are running the code, that’s the workbook which you want to save as PDF, is not empty. That is, at least one cell of the book needs to contain a value. Otherwise, the code will show an error and it won’t be executed.


Conclusion

Therefore, this is the process to use Excel VBA to choose a location and save a file as a PDF. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Hi there,

    I am using this code to do exactly what it is meant to – save the excel tab as a PDF.

    It runs brilliantly on windows users, but it keeps failing on mac users.

    The mac users are using excel and not pages.

    Is there any help you could offer?

    • Hello Michael, thank you for reaching out. We will be working on this matter in MAC and update the article with this information. Right now, please try the code below. Hope this will be useful for you.

      Sub SaveAsPDF()
      ChDir _
      "/Users/Excel Document/Blog 1/"
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
      "/Users/Excel Document/Blog 1/" & "PDF-" & Range("B4").Text & ".pdf" _
      , Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas _
      :=False, OpenAfterPublish:=False
      End Sub

      The code should save the document as PDF and the name of the document will be followed by the text in the B4 cell of your Excel sheet.

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo