Phantom Data Connection Remover: Find and Delete Dead ODBC, OLEDB, and Power Query Links

TL;DR: You open a workpaper and get three “This workbook contains connections” popups before you can do anything. The prior preparer’s SQL queries, dead ODBC links, and abandoned Power Query connections are still there. This macro builds a report showing every connection, tells you which ones feed live tables and pivots (safe to keep) and which are orphaned (safe to delete), then removes the dead ones in one click.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with data connections — any file that triggers “This workbook contains connections” or shows entries in Data → Queries & Connections

What the macro does:

• Iterates every connection in ThisWorkbook.Connections
• Creates a “Connections-Report” sheet with: name, type, connection string (truncated to 80 characters), refresh-on-open status, and whether any active table or pivot depends on it
• Counts orphaned connections (zero downstream consumers) and asks to remove them
• Removes only orphaned connections — live connections stay untouched

What the macro does NOT do:

• It does NOT remove connections that feed active QueryTables or pivot caches. The report marks these as “In Use” and they are never deleted
• It does NOT crack passwords or bypass connection credentials. If a connection is locked with a password, its string is displayed but the macro doesn’t attempt to connect
• It does NOT touch Power Query queries defined in the workbook. These show as connections of type “OLEDB” and are analyzed the same way as any other connection

Limitations:

• Power Query dependency detection is approximate. If a Power Query query loads to a table and that table name matches a connection’s output, the macro flags the connection as “In Use.” Complex query chains with intermediate steps may not be perfectly detected
• Connection string display is truncated. Long connection strings (especially ODBC with full driver paths) are cut to 80 characters for readability. The full string is available in Excel’s connection properties dialog
• OLEDB connections without explicit output table names are flagged as “Unknown” for dependency status. Review these manually before deleting
• Works on the workbook containing the macro. Store in Personal Macro Workbook to run on any file

#The Macro

Option Explicit

Sub RemovePhantomConnections()
    ' ── Phantom Data Connection Remover ────────────────
    ' Lists every ODBC, OLEDB, and Power Query data
    ' connection in the workbook. Flags orphaned
    ' connections (no downstream tables or pivots). On
    ' confirm, removes only the orphans.
    '
    ' Zero input. Report-first-then-act pattern.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Connections-Report"
    Const MAX_STRING_LEN As Long = 80

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim conn As WorkbookConnection
    Dim rpt As Worksheet
    Dim row As Long, orphanCount As Long, totalCount As Long
    Dim connType As String, connStr As String, refreshStr As String
    Dim consumerCount As Long, statusText As String
    Dim orphanNames As String
    Dim qt As QueryTable, pt As PivotTable, pc As PivotCache
    Dim ws As Worksheet

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Check for connections ──────────────────────────
    totalCount = ThisWorkbook.Connections.Count
    If totalCount = 0 Then
        MsgBox "No data connections found in this workbook.", _
               vbInformation, "All Clear"
        GoTo CleanUp
    End If

    ' ── Create report sheet ────────────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rpt = ThisWorkbook.Sheets.Add( _
        Before:=ThisWorkbook.Sheets(1))
    rpt.Name = OUT_SHEET

    ' ── Report headers ─────────────────────────────────
    rpt.Cells(1, 1) = "Connection Name"
    rpt.Cells(1, 2) = "Type"
    rpt.Cells(1, 3) = "Connection String (truncated)"
    rpt.Cells(1, 4) = "Refresh on Open"
    rpt.Cells(1, 5) = "Status"
    rpt.Range("A1:E1").Font.Bold = True

    ' ── Analyze each connection ────────────────────────
    row = 2
    orphanCount = 0
    orphanNames = ""

    For Each conn In ThisWorkbook.Connections
        ' ── Determine connection type ──────────────────
        connType = "Unknown"
        On Error Resume Next
        connType = conn.Type
        On Error GoTo CleanUp

        ' ── Get connection string (truncated) ──────────
        connStr = ""
        On Error Resume Next
        If conn.OLEDBConnection Is Not Nothing Then
            connStr = conn.OLEDBConnection.Connection
            connType = "OLEDB"
        ElseIf conn.ODBCConnection Is Not Nothing Then
            connStr = conn.ODBCConnection.Connection
            connType = "ODBC"
        End If
        On Error GoTo CleanUp

        If connStr = "" Then connStr = "(not accessible)"

        ' Truncate long strings
        If Len(connStr) > MAX_STRING_LEN Then
            connStr = Left(connStr, MAX_STRING_LEN - 3) & "..."
        End If

        ' ── Refresh on open? ───────────────────────────
        refreshStr = "No"
        On Error Resume Next
        If conn.OLEDBConnection Is Not Nothing Then
            If conn.OLEDBConnection.RefreshOnFileOpen Then
                refreshStr = "Yes"
            End If
        ElseIf conn.ODBCConnection Is Not Nothing Then
            If conn.ODBCConnection.RefreshOnFileOpen Then
                refreshStr = "Yes"
            End If
        End If
        On Error GoTo CleanUp

        ' ── Count downstream consumers ─────────────────
        consumerCount = 0

        ' Check QueryTables
        For Each ws In ThisWorkbook.Worksheets
            On Error Resume Next
            For Each qt In ws.QueryTables
                If qt.Connection Like "*" & conn.Name & "*" Then
                    consumerCount = consumerCount + 1
                End If
            Next qt
            On Error GoTo CleanUp
        Next ws

        ' Check PivotCaches
        On Error Resume Next
        For Each pc In ThisWorkbook.PivotCaches
            If pc.Connection Like "*" & conn.Name & "*" Then
                ' Count pivot tables using this cache
                For Each ws In ThisWorkbook.Worksheets
                    For Each pt In ws.PivotTables
                        If pt.CacheIndex = pc.Index Then
                            consumerCount = consumerCount + 1
                        End If
                    Next pt
                Next ws
            End If
        Next pc
        On Error GoTo CleanUp

        ' ── Determine status ───────────────────────────
        If consumerCount > 0 Then
            statusText = "In Use (" & consumerCount & _
                         " consumer(s))"
        Else
            statusText = "ORPHANED - safe to remove"
            orphanCount = orphanCount + 1
            orphanNames = orphanNames & "  • " & conn.Name & vbCrLf
        End If

        ' ── Write to report ────────────────────────────
        rpt.Cells(row, 1) = conn.Name
        rpt.Cells(row, 2) = connType
        rpt.Cells(row, 3) = connStr
        rpt.Cells(row, 4) = refreshStr
        rpt.Cells(row, 5) = statusText

        ' Color-code orphaned rows
        If consumerCount = 0 Then
            rpt.Range("A" & row & ":E" & row).Interior.Color = _
                RGB(255, 230, 230)
        End If

        row = row + 1
    Next conn

    ' ── Format report ──────────────────────────────────
    rpt.Columns("A:E").AutoFit
    If rpt.Range("A1").CurrentRegion.Rows.Count > 1 Then
        rpt.ListObjects.Add(xlSrcRange, _
            rpt.Range("A1").CurrentRegion, , xlYes).Name = _
            "ConnTbl"
    End If
    rpt.Activate

    ' ── Report findings ────────────────────────────────
    If orphanCount = 0 Then
        MsgBox "Found " & totalCount & " connection(s). " & _
               "All are in use — nothing to remove." & vbCrLf & _
               vbCrLf & "Report on '" & OUT_SHEET & "' sheet.", _
               vbInformation, "All Clear"
        GoTo CleanUp
    End If

    ' ── Confirm and remove orphans ─────────────────────
    Dim msg As String
    msg = "Found " & totalCount & " connection(s). " & _
          orphanCount & " are orphaned:" & vbCrLf & vbCrLf & _
          orphanNames & vbCrLf & _
          "Remove only the orphaned connections? " & _
          "Active connections will NOT be affected."

    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm Removal") = vbNo Then
        MsgBox "No connections were removed. " & vbCrLf & _
               "Report on '" & OUT_SHEET & "' sheet.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Remove orphaned connections ────────────────────
    Dim connIndex As Long
    connIndex = ThisWorkbook.Connections.Count

    Do While connIndex >= 1
        Set conn = ThisWorkbook.Connections(connIndex)
        consumerCount = 0

        ' Re-check consumers (safety check before deleting)
        For Each ws In ThisWorkbook.Worksheets
            On Error Resume Next
            For Each qt In ws.QueryTables
                If qt.Connection Like "*" & conn.Name & "*" Then
                    consumerCount = consumerCount + 1
                End If
            Next qt
            On Error GoTo CleanUp
        Next ws

        On Error Resume Next
        For Each pc In ThisWorkbook.PivotCaches
            If pc.Connection Like "*" & conn.Name & "*" Then
                For Each ws In ThisWorkbook.Worksheets
                    For Each pt In ws.PivotTables
                        If pt.CacheIndex = pc.Index Then
                            consumerCount = consumerCount + 1
                        End If
                    Next pt
                Next ws
            End If
        Next pc
        On Error GoTo CleanUp

        If consumerCount = 0 Then
            On Error Resume Next
            conn.Delete
            On Error GoTo CleanUp
        End If

        connIndex = connIndex - 1
    Loop

    ' ── Final report ───────────────────────────────────
    MsgBox "Removed " & orphanCount & _
           " orphaned connection(s). " & vbCrLf & _
           (totalCount - orphanCount) & _
           " active connection(s) remain." & vbCrLf & _
           vbCrLf & "Full report on '" & OUT_SHEET & _
           "' sheet.", vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Why a report sheet instead of just a message box

A message box can only show so much — maybe three connection names before it becomes unreadable. When a workbook has 8 connections with different types, connection strings, and dependency counts, you need something you can scan, sort, and reference.

The report sheet gives you permanent documentation. After the macro runs, the “Connections-Report” sheet stays in the workbook. You can see which connections were removed, which remain, and why. Six months later when someone asks “why doesn’t the inventory pivot refresh anymore?” you can look at the report and see that connection was flagged as “In Use” and left alone — the problem is something else entirely.

The report is formatted as an Excel Table (ListObjects.Add) which gives you free sorting and filtering. Click the “Status” column dropdown and filter to “ORPHANED” to see just the connections that were removed.

#The two-pass safety check

This macro checks dependencies twice — once during the reporting pass, and once again during the deletion pass:

' Pass 1: Reporting
For Each conn In ThisWorkbook.Connections
    ' ... count consumers, build report ...
Next conn

' --- confirmation message box ---

' Pass 2: Deletion (re-checks consumers)
Do While connIndex >= 1
    ' ... re-count consumers ...
    If consumerCount = 0 Then conn.Delete
    connIndex = connIndex - 1
Loop

Why the double-check? Between pass 1 and pass 2, the user reads the confirm message box. If the macro only checked once, the connection states are frozen in time. Re-checking right before deletion is an extra safety net — it ensures that no pivot or query table was somehow re-associated with a connection by the time the deletion happens.

In practice, this almost never changes anything. But when you’re deleting data connections from a production workpaper, a 2-millisecond re-check is cheap insurance.

#Detecting OLEDB vs. ODBC connections

Excel’s WorkbookConnection object doesn’t have a clean .Type property that returns a readable string. Instead, you check which sub-object exists:

If conn.OLEDBConnection Is Not Nothing Then
    connStr = conn.OLEDBConnection.Connection
    connType = "OLEDB"
ElseIf conn.ODBCConnection Is Not Nothing Then
    connStr = conn.ODBCConnection.Connection
    connType = "ODBC"
End If

A connection is OLEDB if the .OLEDBConnection object exists, and ODBC if .ODBCConnection exists. If neither exists, it might be a Power Query connection (which uses ODBC under the hood in some Excel versions) — the On Error Resume Next guards prevent crashes, and the type defaults to “Unknown” if detection fails.

#Counting consumers — QueryTables and PivotCaches

A connection is “in use” if any QueryTable or PivotCache references it. The macro checks both:

' Check QueryTables on every sheet
For Each ws In ThisWorkbook.Worksheets
    For Each qt In ws.QueryTables
        If qt.Connection Like "*" & conn.Name & "*" Then
            consumerCount = consumerCount + 1
        End If
    Next qt
Next ws

' Check PivotCaches
For Each pc In ThisWorkbook.PivotCaches
    If pc.Connection Like "*" & conn.Name & "*" Then
        ' Count pivot tables using this cache
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                If pt.CacheIndex = pc.Index Then
                    consumerCount = consumerCount + 1
                End If
            Next pt
        Next ws
    End If
Next pc

The Like "*" & conn.Name & "*" check matches if the connection name appears anywhere in the QueryTable or PivotCache connection string. This is deliberately loose — it’s better to flag a connection as “In Use” when it might not be than to flag it as orphaned when it actually is.

PivotCaches get extra scrutiny: if a cache is flagged as matching, the macro counts every PivotTable that uses that cache. A single connection with 3 pivot tables feeding from it shows “In Use (3 consumers)” — which tells you this connection is load-bearing and should not be touched.

#Reverse-order deletion to avoid index shifts

The deletion loop runs backwards:

Dim connIndex As Long
connIndex = ThisWorkbook.Connections.Count

Do While connIndex >= 1
    Set conn = ThisWorkbook.Connections(connIndex)
    ' ... check and delete ...
    connIndex = connIndex - 1
Loop

Deleting connection #3 shifts connections #4, #5, #6… down by one index. If you looped forward (1, 2, 3…), deleting connection #3 means the old #4 becomes the new #3 — and your loop counter already passed #3, so you skip it. Looping backward avoids this entirely. Delete #5, and #1 through #4 are unaffected.

#Why orphaned rows are highlighted red

If consumerCount = 0 Then
    rpt.Range("A" & row & ":E" & row).Interior.Color = _
        RGB(255, 230, 230)
End If

The light red fill on orphaned rows makes the report scannable at a glance. You open the Connections-Report sheet and immediately see: two rows are red, three are white. The red rows are the ones about to be deleted. If a connection you expected to be active is red, you know something’s wrong — cancel the deletion and investigate.

#The confirm message box lists orphan names

Before asking “delete or cancel?” the macro shows exactly which connections are orphaned:

orphanNames = orphanNames & "  • " & conn.Name & vbCrLf

The message box shows the full list. If you see a connection name you recognize — maybe PowerQuery_TB_Import is orphaned because the TB pivot was deleted but you planned to rebuild it — click No. No harm done. The report sheet is already created, so you have documentation either way.

#Adapt It