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