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

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"

⧪ 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

⧪ 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

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

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.

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

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

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

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

• How to Save a Worksheet in Excel (2 Handy Approaches)
• Excel VBA: Save Workbook in Specific Folder (4 Suitable Examples)
• How to Save One Sheet in Excel as PDF (3 Useful Ways)
• Excel VBA: Save and Close Workbook (5 Suitable Examples)
• [Fixed!] Document Not Saved Excel Network Drive (5 Possible Solutions)
• How to Save Excel File as CSV (3 Handy Methods)