VBA & Macro Problems

How to Fix Excel VBA Run-Time Error 1004 During Query Refresh

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to resolve an Excel VBA Run-time Error 1004 that occurs when a macro attempts to refresh backend queries, causing the data-source operation to be cancelled.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Executing a VBA macro to refresh backend data queries in a shared workbook.
Observed behavior: The macro fails with Run-time error 1004 and an operation-cancelled data-source message, despite working perfectly on colleagues' machines.

Before you start

Before troubleshooting, verify the exact Microsoft Office build version you and your colleagues are running, as recent updates may introduce VBA performance bugs. Ensure you have saved a backup of your workbook containing the original macro code before modifying the query refresh scripts.

Solution 1Recommended

Execute Connection Refresh and Add DoEvents

Adding a DoEvents command ensures that Excel completes the background connection refresh before proceeding to the next line of VBA code.

Often, background queries create timing problems where the VBA code advances before the refresh operation actually completes, triggering Error 1004. Explicitly forcing the system to yield execution resolves this.

Open the VBA Editor

Open your Excel workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.

Update the Refresh Command

Locate the macro module responsible for refreshing the backend queries. Replace your current refresh code with the specific connection refresh command: ActiveWorkbook.Connections("Query - YourQueryName").Refresh

Add the DoEvents Function

Immediately add the word DoEvents on the next line to pause code execution until the operating system finishes processing the refresh.

Save and Test

Save the macro, close the VBA Editor, and run the macro again to verify the query updates without errors.

Maintain the Active Workbook: Ensure that your VBA code does not change the ActiveWorkbook during the refresh operation, as this will cause the refresh to fail.

Solution 2

Disable Background Refresh for Data Connections

Background queries can cause timing conflicts in VBA execution. Forcing them to run in the foreground ensures the macro waits for the data to update.

Solution 3

Revert to a Previous Office Build

If the error started appearing after a recent Office update (like moving from build 2405 to 2408), reverting to an older, stable version can resolve the bug.

Free Microsoft Office alternative

Experience Stable Data Management with WPS Office

Microsoft Office updates can sometimes introduce frustrating VBA bugs and query errors like Run-time Error 1004. WPS Office offers a free, lightweight, and highly compatible alternative that ensures smooth performance. With a familiar user interface and robust support for standard spreadsheet formats, you can easily migrate your work without worrying about unexpected update-related crashes.

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 downloaded setup file and follow the straightforward on-screen instructions to install the suite.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing data workbooks to continue your work without interruption.

Highly compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv) and legacy files.Lightweight installation with stable releases and no forced unstable updates.Advanced spreadsheet functions and seamless data processing capabilities.Familiar ribbon interface ensures a zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

What causes Run-time error 1004 in Excel VBA?

Run-time error 1004 is a generic error code in Excel VBA that typically occurs when a macro tries to interact with an object or data source that is unavailable, locked, or improperly referenced. In the context of query refreshes, it often happens due to timing issues where the code advances before the background refresh completes.

How does the DoEvents function help with Excel macros?

The DoEvents function temporarily pauses the execution of your VBA macro and hands control back to the operating system. This allows pending background tasks—like refreshing a data connection or updating the screen—to finish completely before the macro continues to the next line of code.

Why does my macro work for colleagues but fail on my computer?

Discrepancies between machines are often caused by different versions or builds of Microsoft Office (e.g., one user might be on a buggy update like build 2408 while others are on an older, stable version). Additionally, differences in network speed, default connection properties, or system resources can affect how background queries are processed.

Excel & Data Problems