VBA & Macro Problems

How to Automatically Save an Excel Workbook Before Closing Using VBA

Huda Qurayshi Oct 19, 2026 869 views

Question details

The user wants to automatically save an Excel workbook upon closing to suppress the default save confirmation prompt, specifically to automate processes via Task Scheduler.

How to Automatically Save an Excel Workbook Before Closing Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating workbook operations using Windows Task Scheduler where an unattended workbook needs to run a macro and close without halting for a save prompt.
Observed behavior: Excel halts the automated closing process by displaying a save confirmation dialog, which interrupts unattended macro executions like PDF generation.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are allowed to run in your Trust Center settings.

Solution 1Recommended

Use ThisWorkbook.Save to Automatically Save and Close

Use this method to save the workbook silently and suppress the confirmation dialog, which is ideal for unattended automation.

By adding a simple save command to the Workbook_BeforeClose event, Excel will save any changes automatically before the application closes. This allows existing scripts, such as PDF generation, to finish first.

Open the VBA Editor

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

Access ThisWorkbook

In the Project Explorer pane on the left side, double-click on 'ThisWorkbook'.

Add the BeforeClose Event

From the left dropdown at the top of the code window, select 'Workbook'. From the right dropdown, select 'BeforeClose'.

Insert the Save Command

Type 'ThisWorkbook.Save' immediately before the 'End Sub' line. Make sure it comes after any other automated code you have.

Use ThisWorkbook.Save to Automatically Save and Close

Tip: Do not add a Sheet1 reference unless you need to make a specific test change. Simply using ThisWorkbook.Save applies to the entire file.

Solution 2

Close Without Saving Changes (Suppress Prompt)

Use this method if you want to discard any background changes (like VLOOKUP updates) without triggering the save prompt.

Automate with WPS Office

Automate Tasks with VBA in WPS Spreadsheets

WPS Office Spreadsheets provides robust support for VBA macros. You can run automated scripts, handle background tasks like generating PDFs, and utilize the Workbook_BeforeClose event seamlessly.

1. Enable Developer Tools: Open WPS Spreadsheets and navigate to the 'Developer' tab on the top ribbon.
2. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to launch the script editor.
3. Apply the Macro: Double-click 'ThisWorkbook' in the Project Explorer, paste your Workbook_BeforeClose code, and save the document as a macro-enabled workbook.

Fully compatible with Microsoft Excel VBA scripts and macro-enabled formats (.xlsm)Lightweight and responsive, making it ideal for background Task Scheduler automationFree to use basic spreadsheet features with an intuitive, familiar UI

microsoft office alternative - wps office

Frequently Asked Questions

Why is SendKeys not recommended for handling the save prompt?

SendKeys relies on the exact timing of the dialog box appearing. If system lag delays the prompt, the key commands may be sent before the dialog appears, causing the script to fail or behave unpredictably.

Where exactly do I paste the Workbook_BeforeClose code?

You must place the code strictly in the 'ThisWorkbook' module within the VBA Editor. It will not trigger if placed in a standard module (like Module1) or a specific worksheet module (like Sheet1).

Will this code work if I run the file unattended through Windows Task Scheduler?

Yes, by suppressing the save prompt with ThisWorkbook.Save, the workbook is able to close silently. This prevents the application from hanging, making it perfect for unattended automation via Task Scheduler.

Excel & Data Problems