I have had an issue with an Excel workbook that was giving an external reference warning. Excel gives the option to break these references, but doesn't give any way to identify where the reference is.
I did eventually discover the reference after running the Error Checking utility on every sheet of the workbook. It was hidden inside a table object. The reference was not contained in a data connection, or formula. It is not searchable through the normal Ctrl+F type Find search, nor can I find it contained in any VBA object.
The reference occurs if you create a table object, then add a formula to a column (that has an external reference), then manually replace the formula in each row with something else. Excel maintains the original formula somewhere inside the file. I have tried to access various properties of the ListObject corresponding to the table, the range that contains the column, the ListColumn of the ListObject containing the formula and many others.
The reference is somewhere in the file, it can be shown by clicking the cell's formula error warning and selecting "Restore to Calculated Column Formula".
Is there a way of finding these hidden references systematically with VBA or otherwise?
Edit 1:
I wrote the following script to search various different objects for hidden references to external sources, but haven't found the object that contains this type of reference.
Sub ListLinks()
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
' Identify any links to external workbooks
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
wb.Sheets.Add
xIndex = 1
For Each link In wb.LinkSources(xlExcelLinks)
Application.ActiveSheet.Cells(xIndex, 1).Value = link
xIndex = xIndex + 1
Next link
End If
Dim outSheet As Worksheet
Set outSheet = wb.Worksheets.Add
' Extract all hidden references into a searchable sheet
Dim ws As Worksheet
Dim sh As Shape
With Range("A1:D1")
.Value = Array("ObjectType", "Parent", "ObjectName", "Reference")
.Interior.Color = xlColor1
.Font.Color = xlColor2
End With
' All shape objects can have an action assigned that may be in another workbook
i = 2
For Each ws In Worksheets
For Each sh In ws.Shapes
outSheet.Cells(i, 1).Value = "Shape"
outSheet.Cells(i, 2).Value = ws.Name
outSheet.Cells(i, 3).Value = sh.Name
outSheet.Cells(i, 4).Value = "'" & sh.OnAction
i = i + 1
Next
Next
' All name references may point to a range or table in another workbook
Dim nm As Name
For Each nm In ActiveWorkbook.Names
outSheet.Cells(i, 1).Value = "Name"
outSheet.Cells(i, 3).Value = nm.Name
outSheet.Cells(i, 4).Value = "'" & nm.RefersTo
i = i + 1
Next
' All chart series and chart shapes can contain references
Dim ch As Chart
Dim srs As Series
For Each ch In ActiveWorkbook.Charts
For Each srs In ch.SeriesCollection
outSheet.Cells(i, 1).Value = "ChartsSeries"
outSheet.Cells(i, 2).Value = ch.Name
outSheet.Cells(i, 3).Value = srs.Name
outSheet.Cells(i, 4).Value = "'" & srs.Formula
i = i + 1
For Each sh In ch.Shapes
outSheet.Cells(i, 1).Value = "ChartsShapes"
outSheet.Cells(i, 2).Value = ch.Name
outSheet.Cells(i, 3).Value = sh.Name
outSheet.Cells(i, 4).Value = "'" & sh.OnAction
i = i + 1
Next
Next
Next
' As above, but for charts in a Worksheet, previous was for Chart Sheets
Dim chOb As ChartObject
For Each ws In Worksheets
For Each chOb In ws.ChartObjects
For Each srs In chOb.Chart.SeriesCollection
outSheet.Cells(i, 1).Value = "ChartsObjectsSeries"
outSheet.Cells(i, 2).Value = ws.Name & " | " & ch.Name
outSheet.Cells(i, 3).Value = srs.Name
outSheet.Cells(i, 4).Value = "'" & srs.Formula
i = i + 1
Next
For Each sh In chOb.Chart.Shapes
outSheet.Cells(i, 1).Value = "ChartsObjectsShapes"
outSheet.Cells(i, 2).Value = ws.Name & " | " & ch.Name
outSheet.Cells(i, 3).Value = sh.Name
outSheet.Cells(i, 4).Value = "'" & sh.OnAction
i = i + 1
Next
Next
Next
' Query tables can reference external sheets
Dim qryTbl As QueryTable
For Each ws In Worksheets
For Each qryTbl In ws.QueryTables
outSheet.Cells(i, 1).Value = "QueryTables"
outSheet.Cells(i, 2).Value = ws.Name
outSheet.Cells(i, 3).Value = qryTbl.Name
outSheet.Cells(i, 4).Value = "'" & qryTbl.Connection
i = i + 1
Next
Next
Dim lstObj As ListObject
For Each ws In Worksheets
For Each lstObj In ws.ListObjects
For Each qryTbl In lstObj.QueryTables
outSheet.Cells(i, 1).Value = "TableQueryTables"
outSheet.Cells(i, 2).Value = ws.Name & " | " & lstObj.Name
outSheet.Cells(i, 3).Value = qryTbl.Name
outSheet.Cells(i, 4).Value = "'" & qryTbl.Connection
i = i + 1
Next
Next
' OLEObjects such as images can point to external sources
Dim oleOb As OLEObject
For Each ws In Worksheets
For Each oleOb In ws.OLEObjects
outSheet.Cells(i, 1).Value = "OLEObjects"
outSheet.Cells(i, 2).Value = ws.Name
outSheet.Cells(i, 3).Value = oleOb.Name
outSheet.Cells(i, 4).Value = "'" & oleOb.SourceName
i = i + 1
Next
Next
' Hyperlinks can point to external sources
Dim hypLk As Hyperlink
For Each ws In Worksheets
For Each hypLk In ws.Hyperlinks
outSheet.Cells(i, 1).Value = "HyperLinks"
outSheet.Cells(i, 2).Value = ws.Name
outSheet.Cells(i, 3).Value = hypLk.Name
outSheet.Cells(i, 4).Value = "'" & hypLk.SubAddress
i = i + 1
Next
Next
End Sub
Edit 2:
From Slai's comment I can see the reference inside the XML of the file in /xl/tables/table1.xml, in
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:B4" totalsRowShown="0">
<autoFilter ref="A1:B4"/>
<tableColumns count="2">
<tableColumn id="1" name="a"/>
<tableColumn id="2" name="b" dataDxfId="0">
<calculatedColumnFormula>[1]Sheet1!$A2</calculatedColumnFormula>
</tableColumn>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>
Is there any way to access this from inside the VBA object model?
