[Excel VBA] How to Bulk Delete Power Query Queries and Data Connections

2025年12月30日

Overview

In workbooks that utilize Power Query, unnecessary queries and connection information often remain from the trial-and-error process.

To save you the trouble of manually deleting unnecessary objects, I will introduce a macro that deletes Queries and Connections all at once using VBA. Please use this for regular maintenance.

Prerequisites

Item	Content
Excel	Microsoft 365 / 2016 or later
Execution Location	Target Workbook (ThisWorkbook)
References	None required (Works with standard features)

Sample Code

Please paste the following code into a standard module and execute it.

Sub ClearPowerQueryObjects()

    Dim wb As Workbook          ' Target Workbook
    Dim idx As Long             ' Loop counter
    
    Set wb = ThisWorkbook       ' Change to ActiveWorkbook if necessary

    '----- Delete Queries -----
    For idx = wb.Queries.Count To 1 Step -1
        wb.Queries(idx).Delete
        DoEvents                ' Ensure responsiveness
    Next idx

    '----- Delete Data Connections -----
    For idx = wb.Connections.Count To 1 Step -1
        wb.Connections(idx).Delete
        DoEvents
    Next idx

End Sub

Code Explanation

Lines 4–6: The target workbook is stored in wb to simplify subsequent processing.
Lines 9–12: By looping in reverse order (Step -1) from wb.Queries.Count, we avoid index shifts that occur when deleting items.
Lines 15–18: The Connections property is also processed in reverse order to mass delete all connections remaining in the workbook.

Application Examples

Purpose	Implementation Example
Delete only specific names	Add a condition: `If wb.Queries(idx).Name Like "test_*" Then ...`
Show confirmation dialog	`If MsgBox("Delete?", vbYesNo) = vbNo Then Exit Sub`
Keep a deletion log	Output to Immediate Window: `Debug.Print wb.Queries(idx).Name`

FAQ

Q. Can I delete queries in other workbooks?

A. Yes, you can specify the target by setting Set wb = Workbooks("filename.xlsx").

Q. Will reference errors occur after deletion?

A. If there are tables or pivot tables that reference the queries or connections, errors will occur upon refreshing. Please check dependencies before deleting.

Summary

By bulk deleting unnecessary Power Query queries and data connections using VBA, you can simultaneously organize your workbook and improve performance.

I recommend first testing the operation on a test workbook before applying it to your business files.

よかったらシェアしてね！