How to Create and Open an Excel PDF via VBA Without Saving an Unwanted Copy
Question details
The user needs to export an Excel workbook or worksheet to a PDF using a VBA macro and immediately view it, without prompting the user to save a local copy.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a PDF from an Excel worksheet via VBA macros for immediate viewing.
- Observed behavior
- The default macro behavior typically prompts for a save destination and retains the file. The goal is to bypass the save prompt, generate the PDF, and open it directly without keeping an unwanted file.
Ensure that the Developer tab is enabled in your spreadsheet settings so you can access the VBA Editor, and check that macros are enabled for your workbook.
Use ExportAsFixedFormat with OpenAfterPublish Set to True
By directly calling the ExportAsFixedFormat method and omitting the GetSaveAsFilename prompt, you can generate and open the PDF instantly.
To prevent Excel from prompting the user for a save destination, you must avoid using Application.GetSaveAsFilename. Instead, specify the ExportAsFixedFormat parameters directly within the active sheet and set OpenAfterPublish:=True.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Double-click the worksheet or module where you want to add the code, or insert a new Command Button and double-click it to access its Click event.
Paste the following code into the editor: Private Sub CommandButton1_Click() Application.ScreenUpdating = False ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, OpenAfterPublish:=True Application.ScreenUpdating = True End Sub
Close the VBA editor and click your Command Button (or run the macro from the Developer tab). The PDF will generate and open automatically without asking for a file name.
Export to a Windows Temporary Folder
If you absolutely must ensure no permanent copy is retained, you can force the macro to export the PDF to the system's temporary folder.
Use WPS Spreadsheet for VBA Macros and Fast PDF Export
WPS Office offers robust VBA support and built-in PDF conversion tools, allowing you to run your macros smoothly while maintaining full compatibility with Microsoft Excel formats.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled spreadsheet.
- 2. Enable the Developer Tab: Navigate to the ribbon, click on the Developer tab, and select 'Macros' or 'VBA Editor'.
- 3. Run Your Export Script: Paste your ExportAsFixedFormat script into the editor and run it directly to generate and view your PDF.
Frequently Asked Questions
Why does my VBA code keep prompting me to save the PDF?
If your VBA script includes 'Application.GetSaveAsFilename', it will trigger a prompt asking the user to choose a save destination. Removing this line and executing 'ExportAsFixedFormat' directly will bypass the prompt.
Can I use VBA to delete the PDF immediately after opening it?
Attempting to delete the file immediately via VBA using the 'Kill' command often fails because the PDF viewer locks the file while it is open. It is recommended to save the file to a temporary folder instead, which Windows will eventually clean up.
Does WPS Office support VBA commands like ExportAsFixedFormat?
Yes, WPS Spreadsheet has excellent VBA compatibility. You can run standard Excel VBA scripts, including 'ExportAsFixedFormat', to generate PDFs seamlessly within WPS Office.
