How to Automatically Refresh Excel Power Query Data Without Opening the Workbook
Question details
The user needs to automate the data refresh process for an Excel workbook stored on SharePoint that uses Power Query to pull from SQL databases, specifically without manually opening the file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Maintaining updated datasets in a shared SharePoint Excel file where Power Query connections to external SQL sources need to run in the background unattended.
- Observed behavior
- Standard Excel workbooks fail to refresh closed Power Query connections automatically, requiring a user to manually open the file to trigger the data update.
Verify that you have administrative access to your SharePoint site and SQL database, and ensure your organization's Microsoft 365 licensing supports Power BI or Power Automate.
Migrate the Data Model to Power BI for Scheduled Refreshes
Since Excel cannot natively refresh closed workbooks, moving the Power Query connection to Power BI allows for automated, scheduled data refreshes from SQL sources.
Power BI shares the same underlying Power Query engine as Excel. By handling the data extraction and transformation in Power BI Service, you bypass the need for an open Excel client.
Open Power BI Desktop, click 'Get Data', and select your SQL database. Recreate or paste your existing Power Query M-code into the Power BI Advanced Editor.
Once your data model is built, click 'Publish' on the Home tab to upload the report to your designated Power BI Service workspace.
If your SQL database is hosted on-premises, install and configure a Microsoft On-premises data gateway to allow Power BI to securely access the data.
In Power BI Service, locate your dataset, click the 'Settings' icon, expand the 'Scheduled refresh' section, toggle it on, and define your automated refresh times.
Use Power Automate to Trigger Workbook Updates
Utilize Microsoft Power Automate to create a scheduled cloud flow that interacts with your Excel files stored in SharePoint or OneDrive.
Looking for a Fast, Lightweight Office Alternative?
If you don't require complex enterprise data gateways like Power BI or SharePoint automation, and simply need a powerful, everyday spreadsheet tool for offline data analysis, WPS Office is highly recommended.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the installer and follow the quick on-screen instructions to set up the software.
- 3. Open Your Data Files: Launch WPS Spreadsheets and open your existing Excel workbooks seamlessly to start analyzing your data.
Frequently Asked Questions
Can Excel natively refresh a closed workbook stored on SharePoint?
No, standard Excel does not natively support automatically refreshing Power Query connections while the workbook is closed. It generally requires the file to be opened by a user or an automated script running on a desktop instance of Excel.
Can I set Power Query to refresh immediately upon opening?
Yes. Open your workbook, navigate to Data > Queries & Connections, right-click your connection, select 'Properties', and check the box that says 'Refresh data when opening the file'.
Do I need a premium license to automate Excel refreshes?
Using advanced Microsoft services to refresh background datasets often requires premium licensing, such as Power BI Pro for frequent scheduled refreshes or Power Automate Premium if using custom SQL and Gateway connectors.
Can Office Scripts natively refresh Power Query connections in Excel for the Web?
Currently, Office Scripts executed on Excel for the Web have limitations regarding refreshing external data connections like Power Query to SQL databases. Using Power BI is the officially recommended approach for this scenario.
