VBA & Macro Problems

How to Fix VBA Date Errors When Saving Excel as PDF

Khadija Khan Oct 14, 2026 869 views

Question details

The user is encountering a VBA runtime error when attempting to include a date in the file name of an exported PDF.

How to Fix VBA Date Errors When Saving Excel as PDF

Product: Excel
Device & OS: Windows
Scenario: Automating the export of an Excel worksheet to a PDF file using a VBA macro that incorporates a date from a worksheet cell into the final file name.
Observed behavior: The VBA macro fails and returns an error because standard date formats pull forward slashes (/), which are strictly prohibited in Windows file names.

Before you start

Ensure that the cell you are referencing in your VBA script contains a valid date value, and save a backup of your workbook before editing the macro code.

Solution 1Recommended

Use the Format Function to Remove Invalid Characters

Modify your VBA code to format the date with hyphens instead of forward slashes, converting it into a valid Windows file name.

Windows does not allow forward slashes (/) in file and folder names. When Excel VBA reads a cell formatted as a date, it often retrieves the default system format (such as MM/DD/YYYY). Attempting to save a PDF with these slashes causes a runtime error.

To fix this, you can use the VBA Format$ function to convert the date into a safe text string before concatenating it into your file path.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Locate the macro

In the Project Explorer on the left, double-click the module that contains your PDF export code to open it.

Format the date variable

Before the line of code that defines the final file path, declare a variable and format the date using hyphens. For example, insert: dato = Format$(Range("AE10").Value, "yyyy-mm-dd")

Update the export path

Modify your PDF export command so that it concatenates the new formatted date variable (dato) into the file name instead of referencing the cell directly. Run the macro to test the PDF export.

Use the Format Function to Remove Invalid Characters

Best Practice for Sorting: Using the "yyyy-mm-dd" format is highly recommended because it naturally sorts your exported PDF files in chronological order within Windows File Explorer.

Advanced Macro Support

Easily Write and Run VBA Macros in WPS Office

WPS Spreadsheet provides robust compatibility with Excel VBA macros. You can seamlessly edit your code, use the Format function to handle date variables, and export your worksheets directly to PDF without needing extra add-ins.

1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click 'Macro' > 'Visual Basic Editor', or simply press Alt + F11.
3. Update and run the code: Apply the Format$ function to your date variable, save your changes, and click the 'Run' button to export your worksheet to PDF without errors.

Fully compatible with Microsoft Excel (.xlsm, .xls) macro-enabled formats.Built-in VBA editor for creating, debugging, and running scripts smoothly.Native support for exporting worksheets directly to PDF.Free, lightweight, and loads much faster than traditional office suites.

microsoft office alternative - wps office

Frequently Asked Questions

What characters are invalid in Windows file names?

Windows prohibits the use of the following characters in file names: < > : " / \ | ? *. If your VBA macro tries to save a file containing any of these characters, it will trigger a run-time error.

Can I use dots instead of hyphens for the date format in VBA?

Yes, dots are perfectly acceptable in Windows file names. You can format your date using dots by updating your code to Format$(Range("AE10").Value, "yyyy.mm.dd"), and the file will save successfully.

Why does my macro work on one PC but fail with a date error on another?

If you do not explicitly format the date in your VBA code, Excel relies on the computer's system regional settings. A PC set to use hyphens by default will work perfectly, while a PC set to use slashes will fail. Using the Format$ function ensures the macro behaves consistently across all devices.

Excel & Data Problems