How to Automatically Open a PDF File After Saving in Excel Using VBA
Do you find yourself frequently exporting Excel spreadsheets to PDF format and then having to manually locate and open the generated PDF file? Wouldn’t it be nice if Excel could automatically open the PDF for you as soon as it’s saved?
With a simple VBA script, you can configure Excel to do just that – launch the exported PDF file immediately after the spreadsheet is saved, without any extra clicks. This article will show you exactly how to set up this useful automation.
Understanding the VBA Code to Auto-Open PDFs
To make Excel open a PDF automatically after saving, you need to add some VBA code in the Workbook_BeforeSave
event. This event triggers right before the workbook is saved, allowing you to specify additional actions to perform during the save process.
The key components of the VBA script are:
ThisWorkbook.FullName
– a property that returns the complete path and filename of the current Excel workbookLeft(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1)
– a function that extracts just the file path and base filename, without the .xlsx extension, using string manipulationShell
– a command that launches an executable program, which in this case will be the exported PDF file
The VBA Code Explained
Here is the complete VBA script to automatically open a PDF after saving an Excel workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sPath As String
sPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1) & ".pdf"
Shell "cmd.exe /S /C " & sPath, vbNormalFocus
End Sub
Let’s break down what this code does line by line:
- The entire script is contained within the
Workbook_BeforeSave
subroutine, which executes right before the file is saved. - It starts by declaring a string variable called
sPath
to store the file path of the PDF we want to open. - It then sets the value of
sPath
usingThisWorkbook.FullName
to get the current workbook’s full path and filename. - However, we don’t want the .xlsx extension, so it uses
Left()
andInStrRev()
to trim off everything after the last period in the filename. - It then appends “.pdf” to the end of this trimmed filename to get the path of the corresponding PDF file.
- Finally, it uses the
Shell
command to execute cmd.exe, passing it the/S /C
flags and thesPath
variable. This launches the PDF file using the default PDF reader on the computer.
So in a nutshell, when you save the Excel workbook, this macro grabs the workbook’s filename, swaps the .xlsx extension with .pdf, and automatically opens that PDF file.
Where to Put the VBA Script
To use this auto-open PDF VBA script:
- Open the Excel workbook you want to configure
- Press Alt+F11 to launch the Visual Basic Editor
- In the left-hand Project panel, expand the tree node for your workbook
- Find and expand the Microsoft Excel Objects folder
- Double-click on ThisWorkbook to open its code window
- Paste the VBA script provided above into this window
- Close the VB Editor
The macro is now in place and will automatically run whenever you save changes to the Excel workbook.
Enabling Macros in Excel
By default, macros are disabled in Excel for security. To allow the VBA script to run, you must enable macros:
- Go to File > Options > Trust Center > Trust Center Settings
- Click on Macro Settings in the left sidebar
- Select Enable all macros (not recommended for security reasons) or Disable all macros except digitally signed macros
- If you choose the second option, you will need to digitally sign the macro yourself
- Click OK to close the Trust Center and OK again to close Excel Options
Your macro should now be able to execute when the workbook is saved. If you get a warning about macros being disabled when you open the file, you’ll need to enable them using the steps above.
Important Considerations and Troubleshooting
Here are a few key points to keep in mind when using this auto-open PDF macro:
- The PDF file must have the exact same name as the Excel file for the macro to work correctly. It looks for a PDF file with an identical base filename as the XLSX file.
- The PDF file must be located in the same folder as the Excel file. The macro assumes the PDF is in the same directory that the workbook is saved in.
- If you get an error message saying “File not found”, carefully check that the PDF file exists and has the exact same name as the Excel file (just with a .pdf extension instead of .xlsx).
- If Excel shows a warning that macros have been disabled when you open the workbook, you’ll need to enable macros in the Trust Center settings as described earlier.
- To digitally sign the macro (required for the “Disable all macros except digitally signed macros” setting), go to Tools > Digital Signature in the VBA editor, create a new digital certificate, and then choose that certificate to sign the VBA project.
Alternative: Using Adobe Acrobat Actions
If you have the paid Adobe Acrobat application (not the free Adobe Reader), you can achieve the same automated PDF opening without using any VBA code by leveraging Acrobat’s Action Wizard:
- Open the desired Excel workbook
- Go to File > Save As Adobe PDF
- In the Acrobat PDFMaker dialog box, click the Options button
- Check the box labeled View Adobe PDF result
- Click OK to close the Options dialog, then Save to create the PDF
Now whenever you save that particular workbook as a PDF using the Acrobat add-in, it will automatically open the exported PDF file as soon as it’s created.
Final Thoughts
Opening a generated PDF file automatically after saving an Excel workbook is a handy way to quickly preview the final PDF document without having to manually find and launch the file yourself. With the straightforward VBA script provided in this article, you can easily configure Excel to always open the corresponding PDF as soon as the workbook is saved.
The VBA code works by retrieving the path and filename of the active XLSX workbook, replacing the extension with .pdf, and then using the Shell command to open that PDF file with the default PDF viewer. As long as macros are enabled in Excel and there is a PDF file with a matching filename in the same directory as the workbook, this convenient automation will work as expected.
For users who have Adobe Acrobat, the same functionality can be achieved without any macro programming by simply using Acrobat’s Action Wizard to specify that PDFs should open automatically after they are exported from Excel.
Hopefully this in-depth tutorial has helped clarify the process of making Excel automatically open PDFs after they are saved using VBA. Feel free to post any additional questions you may have!
FAQs
What is the purpose of automatically opening a PDF file after saving in Excel?
Automatically opening a PDF file after saving in Excel saves time and effort by allowing you to quickly preview the generated PDF without manually locating and opening the file.
What is the key VBA event used to trigger the auto-open PDF functionality?
The Workbook_BeforeSave
event is used to trigger the auto-open PDF functionality. This event fires right before the workbook is saved, allowing you to specify additional actions to perform during the save process.
Where should I paste the VBA code to enable the auto-open PDF feature?
To enable the auto-open PDF feature, open the Visual Basic Editor (Alt+F11), expand the “Microsoft Excel Objects” folder in the Project panel, double-click on “ThisWorkbook,” and paste the VBA code into the code window.
What are the requirements for the PDF file to be automatically opened?
The PDF file must have the exact same name as the Excel file (with a .pdf extension) and be located in the same folder as the Excel file for the auto-open feature to work correctly.
Is there an alternative method to automatically open PDFs after saving in Excel without using VBA?
Yes, if you have Adobe Acrobat (not Adobe Reader), you can use the Action Wizard to automatically open PDFs after they are exported from Excel. Simply go to File > Save As Adobe PDF, click Options, and check the “View Adobe PDF result” box.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.