WPS Spreadsheets VBA: Save PDF with a Cell Value in the File Name
Question details
The user needs a VBA macro in WPS Spreadsheets to export a worksheet to PDF with a file name that includes the current value of cell D3 and the current date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VBA macro currently exports a worksheet to PDF using a fixed file name. The user wants the file name to include the dynamic value from cell D3 so each exported PDF is automatically unique.
- Observed behavior
- The exported PDF uses a fixed file name and does not update when the value in cell D3 changes.
Open the workbook in WPS Spreadsheets, enable macros if prompted, and press Alt+F11 to open the VBA editor. Confirm that cell D3 contains the text you want to use in the PDF file name.
Update the FilePath String to Use Range("D3").Value in the VBA Macro
Use this solution when your macro already exports to PDF but you need the exported file name to change based on the value in cell D3.
In VBA, build the PDF file path as a string that concatenates a fixed prefix, the value in cell D3, and the current date. This replaces the hard-coded file name with a dynamic name before calling ExportAsFixedFormat.
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, then locate the module that contains your PDF export macro.
Find the line that assigns FilePath. Change it to include Range("D3").Value, for example: FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & "1 on 1 " & Range("D3").Value & " " & Format(Date, "dd-mmm-yy") & ".pdf"
Save the VBA project, return to WPS Spreadsheets, and run the macro. The PDF is saved to the desktop and the file name includes the value from D3 and the current date.
Export a PDF with a Dynamic File Name in WPS Spreadsheets
Use the WPS Spreadsheets VBA editor to modify your export macro so it builds the PDF file path from cell D3 and the current date, then saves the PDF to your desired folder.
- 1. Open your workbook: Open the macro-enabled workbook in WPS Spreadsheets and enable macros if prompted.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor and find the module containing your PDF export macro.
- 3. Build the dynamic file path: Edit the FilePath string to concatenate Range("D3").Value and Format(Date, "dd-mmm-yy"), for example: FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & "1 on 1 " & Range("D3").Value & " " & Format(Date, "dd-mmm-yy") & ".pdf"
- 4. Run the macro: Run the macro to export the worksheet as a PDF with the dynamic file name.
Frequently Asked Questions
Can I include both a fixed prefix and the value from cell D3 in the PDF file name?
Yes. Concatenate the prefix, Range("D3").Value, and a date string in the FilePath assignment. For example: FilePath = "C:\Reports\" & "1 on 1 " & Range("D3").Value & " " & Format(Date, "dd-mmm-yy") & ".pdf".
What if cell D3 contains characters that are not valid in a file name?
Invalid characters such as \ / : * ? " < > | will prevent the PDF from being saved. Clean the cell value or use the VBA Replace function to remove or substitute those characters before building FilePath.
How do I change the destination folder from the desktop?
Replace CreateObject("WScript.Shell").SpecialFolders("Desktop") with a folder path such as "C:\Reports\" or use a file dialog. Make sure the destination folder exists before running the macro.
Will this VBA code work in WPS Spreadsheets if the macro was written in Microsoft Excel?
In most cases yes. WPS Spreadsheets supports standard Excel VBA objects such as Range and Format. Open the workbook in WPS Spreadsheets, enable macros, and test the macro to confirm the export path and name.
Do I need to set a reference to use ExportAsFixedFormat for PDF export?
No. ExportAsFixedFormat is available from the WPS Spreadsheets object model for exporting PDF. You only need to build a valid FilePath string before calling it.
