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
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: Excel VBA Macro to Save PDF in Specific Folder
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. 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: Excel Macro to Save as PDF
Things to Remember
Make sure that the workbook on which you are running the code, that is the workbook that you want to save as a 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles
- Excel Macro to Save as PDF with Filename from Cell Value
- Excel VBA: Create Invoice and Save PDF Format
- Excel Macro: Save as PDF with Date in Filename
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.