Create a WPS Office VBA Save As Dialog to Export an Excel Report to PDF
Question details
The user wants to create a VBA macro in WPS Office Spreadsheets that opens a Save As dialog with a prepopulated PDF filename and then exports a report range to the selected PDF location.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating report export from a spreadsheet so users can choose a PDF save location without editing the macro code.
- Observed behavior
- The macro should display a Save As dialog, default to a filename built from a cell value and current timestamp, and save the selected range as a PDF only when a location is chosen.
Open the workbook that contains the report range and make sure the Macro/VBA feature is enabled in WPS Office Spreadsheets. Identify the worksheet and range that should be exported to PDF before editing the code.
Use Application.GetSaveAsFilename and ExportAsFixedFormat in a WPS Office VBA Macro
Use this solution when you need a reusable VBA macro that prompts for a PDF save location and automatically names the exported report.
Application.GetSaveAsFilename only returns the selected path; it does not save the file. The macro must capture that path and pass it to ExportAsFixedFormat.
Use the InitialFileName argument to prepopulate the report name from a cell value and the current date/time so repeated exports do not overwrite each other.
In WPS Office Spreadsheets, press Alt+F11 or go to the Developer tab and click Visual Basic to open the VBA editor.
Click Insert > Module, then paste the following macro: Dim vntFileSpec As Variant Dim rng As Range Set rng = Sheets("Report").Range("A1:E20") vntFileSpec = Application.GetSaveAsFilename(InitialFileName:=Range("B2") & " Report_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf", FileFilter:="PDF Files (*.pdf), *.pdf") If vntFileSpec <> False Then rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vntFileSpec End If
Change Sheets("Report").Range("A1:E20") to the sheet and report range you want exported, and change Range("B2") to the cell that contains the filename prefix.
Press F5 or assign the macro to a button. When the Save As dialog opens, accept the prepopulated name or browse to another folder, then click Save.
Export an Excel Report to PDF with a WPS Office VBA Save As Dialog
Use the VBA editor in WPS Office Spreadsheets to write a macro that calls Application.GetSaveAsFilename and ExportAsFixedFormat, so users can choose the PDF location and get a prepopulated filename without extra tools.
- 1. Open the VBA editor: In WPS Office Spreadsheets, press Alt+F11 or go to the Developer tab and click Visual Basic to open the macro editor.
- 2. Insert a module: Click Insert > Module and paste the VBA macro, including Application.GetSaveAsFilename and ExportAsFixedFormat.
- 3. Set the export range: Assign the report range, for example Set rng = ThisWorkbook.Sheets("Report").Range("A1:E20").
- 4. Run the macro: Press F5 or create a button, then choose the PDF location in the Save As dialog.
- 5. Verify the PDF: Open the exported PDF and confirm the report layout and filename.
Frequently Asked Questions
Does Application.GetSaveAsFilename save the PDF automatically?
No. It only returns the path the user selected. After capturing the returned path in a Variant, pass it to ExportAsFixedFormat with the selected filename to create the PDF.
How do I prepopulate the filename from a cell like B2?
Set the InitialFileName argument to Range("B2") & " Report_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf". This creates a unique default filename each time the dialog opens.
What should I change to export a different range?
Adjust the Set rng assignment, for example Set rng = Sheets("Report").Range("A1:E20") or Set rng = ActiveSheet.UsedRange, before running the macro.
Will this work for files saved in Teams or SharePoint?
The macro can work if the selected path is a supported and accessible local or sync location. For online-only Teams or SharePoint locations, open or sync the file locally first and verify that the macro can write the PDF to the selected path.
Can I prevent overwriting an existing PDF?
The timestamp in the default filename reduces overwrites. If you need a hard block, add a Dir(vntFileSpec) check and show a message before calling ExportAsFixedFormat.
