How to Schedule Excel to Open, Refresh Data, Run Macros, and Save
Question details
The user wants to automate a daily routine where an Excel workbook opens automatically, refreshes Power Query data, executes a save macro, and closes itself while displaying a completion message.
- Product
- Excel
- Device & OS
- Windows
- Scenario
- Automating daily reports and data updates without manual intervention.
- Observed behavior
- The goal state is an automated workflow that successfully triggers the workbook to open, refresh data, save, and close at a scheduled time.
Ensure your Excel macro security settings allow macros to run automatically without prompting, and disable background refresh in Power Query so the macro waits for the data to finish loading before saving.
Automate with a Workbook_Open Macro and Windows Task Scheduler
Set up a macro to execute immediately upon opening the file, and use Windows Task Scheduler to launch the workbook at the desired daily time.
This approach leverages Excel's native event triggers combined with the built-in Windows Task Scheduler. It requires writing a short VBA script in the 'ThisWorkbook' module and adjusting query connection settings to prevent premature saving.
Go to the Data tab, click 'Queries & Connections'. Right-click your query, choose 'Properties', and uncheck 'Enable background refresh'. This forces VBA to wait for the refresh to complete before moving to the next line of code.
Press Alt + F11 to open the VBA Editor. In the Project Explorer, double-click 'ThisWorkbook'. Enter a Private Sub Workbook_Open() script that calls your data refresh method (e.g., ActiveWorkbook.RefreshAll), runs your custom Save macro, displays a MsgBox for completion, and uses Application.Quit to close Excel.
Click File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)'. Ensure this file is saved in a Trusted Location so macros are not blocked.
Open Windows Task Scheduler and select 'Create Basic Task'. Name the task, set the trigger to 'Daily' at your preferred time (e.g., 4:00 AM). Choose 'Start a program' as the action. Browse to your Excel application executable (excel.exe), and in the 'Add arguments' box, enter the full file path of your .xlsm workbook in quotation marks.
Automate Your Workflows with WPS Spreadsheet
WPS Spreadsheet provides excellent support for VBA and macros, allowing you to run automated tasks, refresh data, and manage large datasets smoothly. It offers high compatibility with Microsoft Excel macro files without the high cost.
- 1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file. Your macros and scripts will be retained.
- 2. Manage VBA Macros: Navigate to the Developer tab to access the VBA Editor and adjust your Workbook_Open scripts as needed.
- 3. Schedule the Automation: Use Windows Task Scheduler to call the WPS Spreadsheet executable and open your automated workbook daily.
Frequently Asked Questions
Why does my macro save the workbook before the data finishes refreshing?
If 'Enable background refresh' is checked in your Power Query connection properties, VBA will continue executing the rest of the code without waiting. Uncheck this option to force VBA to wait until the refresh completely finishes.
How do I bypass the macro warning prompt when Task Scheduler opens the file?
To run macros automatically without prompts, place your workbook in a Trusted Location in the Trust Center settings, or adjust your macro security to 'Enable all macros' (though this is less secure).
What happens if my computer is asleep at the scheduled time?
In Windows Task Scheduler, right-click your task, go to Properties, and under the Conditions tab, check the option 'Wake the computer to run this task' to ensure it runs even if the PC is sleeping.
Can I schedule a macro without using Windows Task Scheduler?
Yes, you can use VBA's Application.OnTime method. However, this requires Excel and the workbook to remain open and running in the background constantly, making Task Scheduler a more reliable method for waking and launching.
