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?

QB

 

Question Info


Last updated July 24, 2018 Views 1,950 Applies to:
Answer
Answer

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.Cells.Clear
  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
  Else
    MsgBox "No links to Excel worksheets detected."
  End If
  'housekeeping
  Set reportWS = Nothing
End Sub

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.