VBA & Macro Problems

How to Automatically Overwrite Existing PDF Files using Excel VBA

Phi Hung Vo Oct 22, 2026 868 views

Question details

The user wants to force an Excel VBA macro to automatically overwrite an existing PDF file when exporting data, without manual prompts.

Product: Excel
Device & OS: not provided
Scenario: Running an Excel VBA macro to export student grade summaries as PDF files multiple times.
Observed behavior: The macro leaves the old PDF content unchanged upon re-running, and the ExportAsFixedFormat code does not display an overwrite prompt.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the correct system permissions to delete files in the target directory.

Solution 1Recommended

Use the VBA Kill Statement to Delete the Existing PDF

The most reliable way to ensure a PDF is updated is to check if the file already exists and permanently delete it before running the export command.

In Excel VBA, the ExportAsFixedFormat method does not always prompt for overwriting or cleanly replace an existing PDF file. To bypass this limitation, you can programmatically delete the existing PDF file using the VBA Kill command before generating the new one.

Define the target file path

In your VBA script, declare a string variable (e.g., file_path_pdf) that contains the full directory path and filename of the PDF you intend to export.

Check if the file already exists

Add a conditional statement using the Dir function: If Dir(file_path_pdf) <> "" Then. This checks whether a file with that name already occupies the destination folder.

Delete the old PDF file

Inside the If statement, insert the command Kill file_path_pdf. This instructs the macro to delete the existing PDF file automatically.

Run the export command

After the If statement is closed, execute your normal ActiveSheet.ExportAsFixedFormat or Workbook.ExportAsFixedFormat code. Since the old file is gone, the new PDF will save successfully.

Use the VBA Kill Statement to Delete the Existing PDF

Preventing Permission Errors: Make sure the PDF file is not currently open in a PDF viewer like Adobe Acrobat. If the file is locked by another application, the Kill statement will result in a 'Permission Denied' runtime error.

Seamless VBA Integration

Export to PDF and Run Macros Easily with WPS Office

WPS Office provides robust compatibility with Excel macros and features native, high-quality PDF export capabilities. You can seamlessly run your existing VBA scripts to automate your workflow without needing expensive software or extra add-ins.

1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm file containing the PDF export code.
2. Access the VBA Editor: Press Alt + F11 to open the built-in WPS Macro Editor.
3. Update your export script: Ensure your macro includes the Kill statement to automatically overwrite existing PDF files.
4. Run your automated export: Execute the macro directly from the developer tab to instantly generate your updated PDF reports.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xls)Native support for advanced VBA macros and scriptingBuilt-in, high-quality PDF converter for seamless document exportLightweight architecture ensuring fast macro execution

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Permission Denied' error when running the VBA Kill statement?

This usually happens if the target PDF file is currently open in a PDF reader or preview pane. You must close the file in all other applications before the macro can delete it.

Can I suppress the overwrite warning using DisplayAlerts instead?

Setting Application.DisplayAlerts = False works for many standard Excel save operations, but the ExportAsFixedFormat method for PDFs often ignores this setting. Deleting the file using the Kill method is the most reliable workaround.

Does the Dir and Kill method work on Mac OS?

While the Dir and Kill functions exist on Mac, file paths are constructed differently (using colons or forward slashes instead of backslashes). Additionally, Mac OS sandboxing may restrict the Kill command, requiring you to grant explicit file access permissions to Excel.

Excel & Data Problems