How to Run an Excel Hyperlink Macro After Power Query Refresh
Question details
The user needs to execute a VBA macro that adds hyperlinks immediately after a Power Query refresh is completed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating a data table via Power Query, which overwrites the existing worksheet cells and removes VBA-generated hyperlinks in the process.
- Observed behavior
- When Power Query refreshes, it deletes the hyperlinks created by previous VBA macros. The user must find a way to trigger the macro automatically once the data refresh finishes.
Before attempting to automate the macro execution, ensure that you have disabled 'Background Refresh' in your Power Query properties so VBA does not trigger the hyperlink macro before the data finishes loading.
Use a Master VBA Macro with Background Refresh Disabled
The most reliable method is to disable background refresh for your query and use a single master macro to first refresh the data and then run the hyperlink macro sequentially.
Power Query operates asynchronously by default, meaning VBA will continue executing the next lines of code while the query is still refreshing data. By disabling the background refresh, you force Excel to wait until the query finishes loading before moving to the hyperlink macro code.
Navigate to the 'Data' tab and click 'Queries & Connections'. Right-click your query, select 'Properties', and uncheck 'Enable background refresh' under the Usage tab. Click OK.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.
Insert a new module and write a macro that refreshes the workbook using 'ActiveWorkbook.RefreshAll'. On the next line, type the name of your hyperlink macro (e.g., 'Call AddHyperlinks').
Instead of refreshing the query from the Excel ribbon, run this new master macro whenever you need to update your data and apply the hyperlinks.
Use the Worksheet Change Event
If you prefer to refresh using the standard Excel interface, you can use a Worksheet_Change event to trigger the macro automatically when the query table updates.
Need a simpler way to manage spreadsheet data? Try WPS Office
If you frequently struggle with complex VBA workarounds and Power Query refresh issues in Microsoft Excel, consider WPS Office. It provides a highly compatible, free, and lightweight alternative for all your spreadsheet needs without the steep learning curve.
- 1. Download the software: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Open your macro-enabled files: Launch WPS Spreadsheet and seamlessly open your existing .xlsm files without losing data.
- 3. Enable VBA features: If your workbook relies on macros, ensure you activate the built-in VBA environment within WPS to execute your automated tasks.
Frequently Asked Questions
Why do my custom hyperlinks disappear after a Power Query refresh?
Power Query essentially drops and recreates the data table upon refreshing. Any custom cell formatting, including manual or VBA-generated hyperlinks applied directly to the worksheet, gets overwritten by the incoming raw data.
How do I turn off background refresh for Power Query?
Go to the 'Data' tab on the ribbon, select 'Queries & Connections', right-click the specific query, choose 'Properties', and uncheck the 'Enable background refresh' option under the Usage tab.
Can I use an AfterRefresh event for Power Query instead of a master macro?
Yes, but it requires setting up an application-level event handler or a Class Module using the QueryTable.AfterRefresh event, which is significantly more complex than turning off background refresh and running a sequential macro.
