Скачать или смотреть How to Open a PDF After Printing from Excel VBA

Discover how to use VBA to generate and open a PDF file directly after exporting your Excel workbook.
---
This video is based on the question https://stackoverflow.com/q/66399246/ asked by the user 'GSabev' ( https://stackoverflow.com/u/13152691/ ) and on the answer https://stackoverflow.com/a/66399681/ provided by the user 'Dy.Lee' ( https://stackoverflow.com/u/8107092/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Get location or directly open file after printing to PDF using Workbook.PrintOut() method in VBA Excel

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Open a PDF After Printing from Excel VBA

Are you looking to enhance your VBA macro to not just export an Excel workbook as a PDF but also open that PDF immediately? You’re in the right place! This guide will guide you through a step-by-step solution to accomplish this task. Say goodbye to manually searching for your PDF files after generation and hello to a more efficient workflow!

The Challenge

When using Excel's VBA to export a workbook to a PDF format, one common hurdle arises: how to retrieve the path and filename of the newly created PDF if you haven’t predefined them. In many scenarios, especially when utilizing the PrintOut() method with PrintToFile set to true, the resultant file properties can be elusive.

Your Existing Code

You've already set up a macro to export the workbook, and here’s a brief look at your current implementation:

[[See Video to Reveal this Text or Code Snippet]]

Printer Configuration

Your set_printer() subroutine efficiently handles printer settings:

[[See Video to Reveal this Text or Code Snippet]]

The Solution: Using ExportAsFixedFormat

Instead of using the PrintOut method, a more efficient approach is to utilize the ExportAsFixedFormat method. This method allows you to specify not only the output file name but also to automatically open it once the export is complete!

Step-by-Step Implementation

Set Up the Macro: Plan your workbook configuration and set the directory for the PDF file.

Prompt for Filename: Use Application.GetSaveAsFilename to let users define the file name and location.

Export the Workbook: Call the ExportAsFixedFormat method to create the PDF and open it immediately.

Here’s the Complete Code

You can use the following code snippet:

[[See Video to Reveal this Text or Code Snippet]]

Understanding the Code

Wb Variable: This holds the reference to the current workbook, ensuring that you are exporting the active one.

ChDir and GetSaveAsFilename: This sets the folder where the PDF is saved and prompts the user for a file name.

ExportAsFixedFormat: The key method here, it handles the export and option to open the file once it's created.

Conclusion

With this robust method, you can automatically open your newly created PDF after exporting your Excel workbook, enhancing productivity and saving valuable time. It's the little enhancements like these that can greatly improve your workflows, especially in environments where document generation is frequent.

We hope this guide helps you set up your Excel VBA to seamlessly generate and view PDFs in an instant. Happy coding!