How to Automatically Open a PDF File After Saving in Excel Using VBA

Sharing is caring!

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 workbook
  • Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1) – a function that extracts just the file path and base filename, without the .xlsx extension, using string manipulation
  • Shell – 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:

  1. The entire script is contained within the Workbook_BeforeSave subroutine, which executes right before the file is saved.
  2. It starts by declaring a string variable called sPath to store the file path of the PDF we want to open.
  3. It then sets the value of sPath using ThisWorkbook.FullName to get the current workbook’s full path and filename.
  4. However, we don’t want the .xlsx extension, so it uses Left() and InStrRev() to trim off everything after the last period in the filename.
  5. It then appends “.pdf” to the end of this trimmed filename to get the path of the corresponding PDF file.
  6. Finally, it uses the Shell command to execute cmd.exe, passing it the /S /C flags and the sPath 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:

  1. Open the Excel workbook you want to configure
  2. Press Alt+F11 to launch the Visual Basic Editor
  3. In the left-hand Project panel, expand the tree node for your workbook
  4. Find and expand the Microsoft Excel Objects folder
  5. Double-click on ThisWorkbook to open its code window
  6. Paste the VBA script provided above into this window
  7. 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:

  1. Go to File > Options > Trust Center > Trust Center Settings
  2. Click on Macro Settings in the left sidebar
  3. Select Enable all macros (not recommended for security reasons) or Disable all macros except digitally signed macros
  4. If you choose the second option, you will need to digitally sign the macro yourself
  5. 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:

  1. Open the desired Excel workbook
  2. Go to File > Save As Adobe PDF
  3. In the Acrobat PDFMaker dialog box, click the Options button
  4. Check the box labeled View Adobe PDF result
  5. 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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *