How to Fix a Deleted Excel Connection Still Appearing in Queries
Question details
A deleted VBA connection in Excel remains visible in the Queries & Connections pane, causing errors and disabling related commands.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing data connections and macros in an Excel workbook.
- Observed behavior
- The deleted connection appears as a stale entry in the pane, triggering errors and disabling the refresh, properties, and delete commands.
Before attempting to force-delete the stale connection, ensure you have saved a backup copy of your workbook, as modifying internal workbook data or using VBA scripts can sometimes lead to unintended data loss.
Force Remove the Stale Connection via VBA
Use a short VBA script to manually bypass the disabled interface and delete the phantom connection from the workbook.
When the standard user interface disables the delete command for a connection, you can often bypass this limitation by using Excel's Visual Basic for Applications (VBA) to forcefully remove the specific connection object.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.
In the top menu, click 'Insert' and select 'Module' to create a blank script window.
Type the following code into the module window: Sub DeleteConnection() ActiveWorkbook.Connections("YourConnectionName").Delete End Sub (Replace "YourConnectionName" with the exact name of the stale connection).
Click anywhere inside the code and press F5, or click the 'Run' button in the toolbar, to execute the script.
Close the VBA Editor and check your Queries & Connections pane to confirm the stale connection has been successfully removed.
Remove the Connection via the XML Structure (Advanced)
If the connection is deeply corrupted and VBA fails, you can remove it by manually editing the workbook's underlying XML files.
Switch to WPS Office for Reliable Spreadsheet Management
If persistent bugs like phantom queries and macro errors in Microsoft Excel are slowing you down, try WPS Office. It serves as a highly compatible, lightweight, and free alternative, offering smooth handling of large datasets and robust spreadsheet tools without the frustrating glitches.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the downloaded file and follow the quick on-screen instructions to install the suite.
- 3. Open Your Workbooks: Launch WPS Spreadsheet and open your existing Excel files seamlessly to continue your work.
Frequently Asked Questions
Why can't I delete a connection in Excel normally?
A connection delete command is often disabled if the connection is currently being used by a PivotTable, a Chart, or a background process. If a VBA macro deleted the data source but left the connection object behind, it can become a corrupted or 'stale' entry.
Does clearing the Office cache fix ghost connections?
Sometimes, clearing the cache forces the workbook to rebuild the connections list. You can try going to File > Options > Data and clicking 'Clear Cache' under Data Cache Management, though script-based removal is usually required for persistent ghost connections.
Will deleting a connection delete my data?
Deleting a connection only removes the link to the external data source or query. The existing imported data will remain in your workbook as static values, but it will no longer update when the original source changes.
