Workbook links cannot be updated...

When I open a xlsx it spits out

"This workbook contains one or more links that cannot be updated ..."

If I click Edit links, I get a nice menu, with no way to determine where/how the link is being used?  Why is something that should be so obvious, burried?!

So, how can I determine where this/these links are being used, so I can determine if I actually want to break or update them?



And here's some code that should do the trick and give you a list you can refer back to later.  You'll need to add the code into a module much as before, plus you need to add a worksheet to the workbook and name it LinksList.  How long it takes to get the job done depends on the number of worksheets and number of cells on each sheet with formulas in them.

Here's the code, and as before, after pasting it into the VB code module, you can run it by clicking within it and pressing [F5], the results will be placed onto the LinksList worksheet.

Sub ShowAllLinksInfo()
'requires a worksheet to be added to the
'workbook and named LinksList
  Dim aLinks As Variant
  Dim i As Integer
  Dim anyWS As Worksheet
  Dim anyCell As Range
  Dim reportWS As Worksheet
  Dim nextReportRow As Long
  Set reportWS = ThisWorkbook.Worksheets("LinksList")
  reportWS.Range("A1") = "Worksheet"
  reportWS.Range("B1") = "Cell"
  reportWS.Range("C1") = "Formula"
  aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
  If Not IsEmpty(aLinks) Then
    'there are links somewhere in the workbook
    For Each anyWS In ThisWorkbook.Worksheets
      If anyWS.Name <> reportWS.Name Then
        For Each anyCell In anyWS.UsedRange
          If anyCell.HasFormula Then
            If InStr(anyCell.Formula, "[") > 0 Then
              nextReportRow = reportWS.Range("A" & Rows.Count).End(xlUp).Row + 1
              reportWS.Range("A" & nextReportRow) = anyWS.Name
              reportWS.Range("B" & nextReportRow) = anyCell.Address
              reportWS.Range("C" & nextReportRow) = "'" & anyCell.Formula
            End If
          End If
        Next ' end anyCell loop
      End If
    Next ' end anyWS loop
    MsgBox "No links to Excel worksheets detected."
  End If
  Set reportWS = Nothing
End Sub

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated March 12, 2020 Views 2,082 Applies to: