title: Check and fix broken links in excel to other worksheets
body: <p>Hello I am new to VBA and to this Forum. </p>
  
  <p>So I have a workbook that copy's data from other workbooks using active links (So I can refresh the worksheet and get updated values) and have the hyperlink (for a copied workbook) pasted in one of the columns. I want to be able to check if the links are broken and repair them. So I added a refresh button to keep the values updated and ErrorHandler to the sub but am unsure how to get excel to identify/store which row has the broken link and paste the new link to the file. Is this possible and how would I do it. </p>
  
  <p>If that is not possible, is it possible to identify the broken hyperlinks (the column that has the pasted hyperlinks). I found this forum but am unsure how to change it so it checks excel files? <a href="https://stackoverflow.com/questions/22256522/checking-for-broken-hyperlinks-in-excel">Checking for broken hyperlinks in Excel</a></p>
  
  <pre><code>     '///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  '
  'This code refreshes all links in the active worksheet.
  '
  '///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  Sub RefreshAllLinks()
  
  'Minimize runtime
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  'Initialize Variables
  Dim summarywb As Workbook
  
  'Set initial values
  Set summarywb = ThisWorkbook
  
  'Refresh all linked data on the active worksheet
  summarywb.ActiveSheet.Activate
  'On Error GoTo HRepair
  summarywb.UpdateLink Name:=summarywb.LinkSources
  
  HRepair:
  Dim lngCount As Long
      Dim cl As Range
  
      Set cl = ActiveCell
      ' Open the file dialog
      With Application.FileDialog(msoFileDialogOpen)
          .AllowMultiSelect = True
          .Show
          ' Display paths of each file selected
          For lngCount = 1 To .SelectedItems.Count
              ' Add Hyperlinks
              cl.Worksheet.Hyperlinks.Add _
                  Anchor:=cl, Address:=.SelectedItems(lngCount), _
                  TextToDisplay:=.SelectedItems(lngCount)
          Next lngCount
      End With
  
  'Display back on
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  End Sub
  </code></pre>