Excel - Find Broken Links

Thanks for this - Power Query wasn't used and the Pivot tables were only based on data within the workbook.

I have tried both VB above but neither work (I'm not that good). When I add "For Each pc....." in the Immediate window and hit return I get error 1004 Application-defined or object-defined error.

I assume for the second vb that should go in a sub and not the Immediate window ? It didn't work as "msochart" isn't defined ?

No problem. Thanks for the update. And yes, you're correct on both things, and the errors you hit make sense.

First, the For Each pc... line can’t be run directly in the Immediate Window. That loop needs context (a Sub) and structured error handling. Running it alone like that throws a 1004 if the SourceData or Connection is malformed or if any cache is broken.

Try this:

1. Open the VBA Editor (Alt + F11).
2. Insert a new module: Right-click > Insert > Module.
3. Paste this:

      Sub ListPivotCacheSources()
          Dim pc As PivotCache
          On Error Resume Next
          For Each pc In ThisWorkbook.PivotCaches
              Debug.Print "SourceData: " & pc.SourceData
              Debug.Print "Connection: " & pc.Connection
          Next pc
          On Error GoTo 0
      End Sub
   

4. Press F5 to run. Then open the Immediate Window (Ctrl + G) to see output.

This avoids crashing on broken caches and shows you if any pivot is still pointing to the missing .xlsm file.

On the second thing, the msoChart constant error is because you tried to run object-level code without defining it in a proper macro. It also needs a Sub, and that enum constant doesn't resolve in Immediate Window.

Try this other version:

Sub ListEmbeddedChartFormulas()
    Dim ws As Worksheet
    Dim co As ChartObject
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        For Each co In ws.ChartObjects
            If co.Chart.SeriesCollection.Count > 0 Then
                Debug.Print "Sheet: " & ws.Name & _
                " | Chart: " & co.Name & _
                " | Formula: " & co.Chart.SeriesCollection(1).Formula
            End If
        Next co
    Next ws
    On Error GoTo 0
End Sub

This one walks through every embedded chart and dumps the first series formula. If any reference the missing file, that’s your source.

Once you run both of these, check the Immediate Window for anything showing a path or file name that doesn’t exist anymore. That’ll tell you exactly what’s trying to link out.