You will sometimes need to convert your Excel files to PDF. For instance, when sending files to your colleagues or bosses. You can send the file as an Excel file. However, the presentation is better and more professional if it's in PDF form. But how can you convert Excel files to PDFs? You can save it as a PDF using VBA.
In this article, you will learn how to do that.
Bonus: You can effortlessly convert Excel files to PDF format without needing to write any code, thanks to PDFelement. Once you've installed this smart PDF editor on your computer, an add-on will seamlessly integrate into your Microsoft Office suite. With just one click, you can save Excel files as PDFs directly within Microsoft Excel. Additionally, PDFelement offers the capability to batch convert Excel, Word, PowerPoint, and other formats into PDFs. It facilitates the creation of fillable PDF forms, as well as editing and merging PDF files. In short, it is an all-in-one solution to meet all your PDF-related needs.
In this article
Part 1. What Is VBA?
VBA stands for "Visual Basic for Applications." Microsoft Corp. developed this programming language and integrated it into MS Office applications. It lets you access functions beyond what apps like MS Excel offers. Also, VBA lets you customize applications to meet your specific needs.
Microsoft wrote MS Office programs using VBA. And VBA works as an internal programming language on these programs. So, essentially, VBA lets you "inherit" the developer's powers. It lets you add features to MS Office programs to help you automate computer processes.
But of course, you need to know how to code using the VBA programming language first.
Part 2. How To Save (Print to) Excel to PDF Using VBA
VBA lets you save Excel files as PDFs. That is, if you know the right codes and how to enter VBA codes in Excel. Not everyone is knowledgeable about this. Don't worry. We got your back. Below are the codes that you will need.
1. Save Active Excel Sheets as PDF With VBA
Suppose you don't need to save the whole workbook as a PDF. You only need to save the active sheet or the sheet you are currently viewing. Here's what you should do.
- Open the Excel Workbook with MS Excel.
- Ensure you are viewing the sheet you want to save as a PDF.
- Press "Alt + F11" on your keyboard to open VBE (Visual Basic Editor.)
- Find your workbook name in the "Project-VBAProject" panel. Right-click on it.
- Click "Insert" > "Module."
- Paste this code:
Sub SaveActiveSheetsAsPDF()
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub
Note: Edit what's on the saveLocation line. Put a path to the folder where you want the PDF to be stored. Also, change the "myPDFFile.pdf" to your desired file name.
- Press "CTRL + S" to save your workbook. A dialog box will appear. Click "No."
- Click the "Save as type" drop-down list in the "Save As" dialog box. Select "Excel macro-enabled workbook."
- Press "Alt + Q."
- Open the Macro-enabled Workbook you just created. Press "Alt+F8.'
- Select the Macro you created earlier > click "Run."
- Check the folder you specified in the code to look for the generated PDF file.
2. Use VBA To Save Active Excel Workbooks as PDF
So, what if you actually want to save the whole Workbook as PDF? Here's what you should do:
- Open the Excel Workbook with MS Excel.
- Open VBE by pressing "Alt + F11."
- Find your workbook name in the "Project-VBAProject" panel. Right-click on it.
- Click "Insert" > "Module."
- Paste this code:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub
Note: Edit what's on the saveLocation line. Put a path to the folder where you want the PDF to be stored. Also, change the "myPDFFile.pdf" to your desired file name.
- Press "CTRL + S" to save your workbook. A dialog box will appear. Click "No."
- Click the "Save as type" drop-down list in the "Save As" dialog box. Select "Excel macro-enabled workbook."
- Press "Alt + Q."
- Open the Macro-enabled Workbook you just created. Press "Alt+F8.'
- Select the Macro you created earlier > click "Run."
- Check the folder you specified in the code to look for the generated PDF file.
3. Save Selected Parts of an Excel as a PDF
Now, what if you don't want to save the whole workbook as a PDF? Also, you don't need to save the whole sheet. You can select the cells you want to include in the PDF. Follow these steps:
- Open the Excel Workbook with MS Excel.
- Select the cells you want to appear in the PDF file.
- Open Visual Basic Editor by pressing "Alt + F11."
- Find your workbook name in the "Project-VBAProject" panel and right-click on it.
- Click "Insert" > "Module."
- Paste this code:
Sub SaveSelectionAsPDF()
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub
Note: Edit what's on the saveLocation line. Put a path to the folder where you want the PDF to be stored. Also, change the "myPDFFile.pdf" to your desired file name.
- Press "CTRL + S" to save your workbook. A dialog box will appear. Click "No."
- Click the "Save as type" drop-down list in the "Save As" dialog box. Select "Excel macro-enabled workbook."
- Press "Alt + Q."
- Open the Macro-enabled Workbook you just created. Press "Alt+F8.'
- Select the Macro you created earlier > click "Run."
- Check the folder you specified in the code to look for the generated PDF file.
4. Save Each Worksheet as a Separate PDF
What if you need to save all the sheets in the workbook? However, each sheet must be a separate PDF file. You can follow the method for saving the active sheet as a PDF for each sheet. But that will take too long. Instead, you can do this:
- Open the Excel Workbook with MS Excel.
- Press "Alt + F11" to open VBE.
- Find your workbook name in the "Project-VBAProject" panel and right-click it.
- Click "Insert" > "Module."
- Paste this code:
Sub LoopSheetsSaveAsPDF()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
Next
End Sub
Note: This method will save the PDFs in the same folder as the workbook. As per the PDF documents' names, they will be based on the Workbook name.
- Press "CTRL + S" to save your workbook. A dialog box will appear. Click "No."
- Click the "Save as type" drop-down list in the "Save As" dialog box. Select "Excel macro-enabled workbook."
- Press "Alt + Q."
- Open the Macro-enabled Workbook you just created. Press "Alt+F8.'
- Select the Macro you created earlier > click "Run."
- Check the folder you specified in the code to look for the generated PDF file.
Part 3. Save Excel as PDF Without VBA
Saving Excel Workbooks as PDFs using VBA is amazing. You only need two things: (1) MS Excel installed on your computer and (2) knowledge of the VBA programming language.
The thing is, those two things are not very accessible. You may not have a license for MS Excel, so you can only use it for viewing Workbooks. The online version does not let you use macros. Speaking of the macros themselves, not everyone can write them. So, you'll look for VBA codes written by others on the internet.
Here's a suggestion. Use PDF software to convert Excel files to PDF files instead. It is a lot easier. Additionally, you'll have a tool you can use to edit the PDFs you will create.
Wondershare PDFelement – Simplify Excel to PDF Conversion
Wondershare PDFelement - PDF Editor is one of the most popular PDF software. You can say it's a household name. This program lets you edit, combine, compress, convert PDFs, and more. Time has proven how reliable PDFelement is. On top of its sophisticated features, this software also boasts an easy-to-use interface.
You can use PDFelement to convert Excel Workbooks to PDF files.
Convert Excel to PDF With Wondershare PDFelement
Follow these steps to convert an Excel file to a PDF.
- Install and open PDFelement.
- Click "Open PDF" > "From File."
- Select the Excel file you want to convert to PDF.
- PDFelement will automatically convert the file. Then, it will open the generated PDF. Review the PDF.
- Save the file by pressing "CTRL + S" on your keyboard or by clicking "File" > "Save" or the "Save" button on the top toolbar.
In addition to converting Excel to PDF, you can also use PDFelement to convert PDF to Excel, Word, image, or other Microsoft Office file formats. Check out the video below to learn more:
Conclusion
You can't save Excel Workbooks as PDF files using MS Excel. That's at least if you don't know how to use VBA codes to add that function. But that's not easy, even if you know about the capabilities of the Visual Basic Editor integrated into MS Excel. You need to know how to code using VBA or research what codes to enter on the VBE.
Thus, using PDF software like PDFelement to convert Excel files to PDFs is wise. It simplifies the conversion, allowing you to turn Excel files into PDFs more easily. As a bonus, you'll have an amazing PDF tool installed on your device.