Pásalo utiliza cookies propias y de terceros para analizar tráfico y para publicidad. Si sigues navegando aceptas nuestra Política de Privacidad.

Cómo identificar los links rotos en tu Excel usando una macro (Excel VBA)

Descubre nuestra macro para lidiar con los links rotos en Excel vía VBA (macros). Fácil, rápido, versátil
autor Jose Gabriel
tiempo de lectura 8 min
Actualizado: 29 sept 2022Publicado: 24 nov 2017

No sé tú, pero yo uso mucho Excel. Un error que me encuentro es este:

Mensaje de error en Excel por enlaces externos rotos
Mensaje de error en Excel por enlaces externos rotos

En este post comparto mi solución para resolver este problema usando macros de Excel. Si no sabes qué son las macros o VBA… probablemente quieras dejar de leer.

¿Por qué sale este error?

Lo más normal es que tengas una fórmula en tu Excel que cargue un valor de otro Excel (de ahí lo de “externo”). Si mueves el archivo “fuente”, el archivo “original” lo pierde de vista y ya no encuentra el valor, y Excel te avisa. ¡Qué majo Excel que nos avisa! Lo que se le da mal a Excel aquí, es decirnos donde está el problema, y ofrecer soluciones.

¿Cómo se arregla?

Pues hay más de una manera, pero yo os comparto mi favorita: usando una macro!

¿Qué hace la macro?

El resumen de la lógica es el siguiente:

  1. El código buscará todos los links externos, y los pondrá en una lista (en una pestaña nueva).
  2. Los que estén rotos, nos dará la opción de:
    • Reemplazarlos por su valor actual (de la última vez que se cargó).
    • O simplemente revisar la lista para identificar el archivo fuente, y volverlo a poner en su sitio 🙂

¿Por qué esta solución es mejor que otras?

  1. Una sóla macro que te permite elegir qué hacer (listar los problemas y/o arreglarlos automáticamente).
  2. Si solo tienes una fórmula, puede que la manera normal de Excel te valga, pero si tienes muchas esto es genial para tener una lista rápida.
  3. Te identifica todas las celdas con links externos, no sólo las que tienen dichos links rotos. Con lo cual puedes utilizarlo para prevenitr problemas, no solo par arreglarlos cuando lleguen.

El código!

He intentado ir añadiendo comentarios sobre el propio código para ayudaros a leerlo.Vamos allá.

Recordatorio: Para abrir el editor de código en Excel, podéis pulsar Atl+F11. Ahí podéis pegar el código y presionar F5 para ejecutarlo (poniendo el cursor dentro la primera Sub.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
Sub arreglarLinks()
'@source: https://www.pasalo.es/como-identificar-los-links-rotos-en-tu-excel-usando-una-macro-excel-vba/

  Dim outputSheet As Worksheet

  alinks = ActiveWorkbook.LinkSources(xlExcelLinks)

  If IsEmpty(alinks) Then
    'Didn't find links. Say that and exit
    MsgBox "No hay links externos! :)"
    Exit Sub
  Else 
    'If there are some links
    xx = MsgBox("He encontrado algunos links externos. A continuación crearé una pestaña nueva listandolos." & vbNewLine & "Si encuentro links rotos, te daré la opción de, si quieres, reemplazar esas celdas con su último valor conocido." & vbNewLine & vbNewLine & "Vamos allá? :)", vbOKCancel + vbQuestion, "Confirmation needed")
    If xx = vbCancel Then Exit Sub
  End If

  Sheets.Add
  Set outputSheet = ActiveSheet

  outputSheet.Range("A1") = "Worksheet"
  outputSheet.Range("B1") = "Cell"
  outputSheet.Range("C1") = "Formula"
  outputSheet.Range("D1") = "Workbook"
  outputSheet.Range("E1") = "Link Status"
  outputSheet.Range("F1") = "Action Taken"
  
  outputSheet.Range("a1:f1").Font.Bold = True
      
  'Recorremos cada pestaña buscando links rotos:
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> outputSheet.Name Then 'no hace falta mirar la pestaña de output
      For Each Rng In ws.UsedRange
        If Rng.HasFormula Then
          For j = LBound(alinks) To UBound(alinks)
            filePath = alinks(j)   'LinkSrouces returns full file path with file name
            Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))   'extract just the file name
            filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]"  'file path with brackets
       
            If InStr(Rng.Formula, filePath) Or InStr(Rng.Formula, filePath2) Then
              NextRow = outputSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
              outputSheet.Range("A" & NextRow) = ws.Name
              outputSheet.Range("B" & NextRow) = Replace(Rng.Address, "$", "")
              outputSheet.Hyperlinks.Add Anchor:=outputSheet.Range("B" & NextRow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
              outputSheet.Range("C" & NextRow) = "'" & Rng.Formula
              outputSheet.Range("D" & NextRow) = filePath
              outputSheet.Range("E" & NextRow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
              outputSheet.Range("F" & NextRow) = "None"
              Exit For
            End If
          Next j
            
          For Each namedRng In Names
            If InStr(Rng.Formula, namedRng.Name) Then
              filePath = Replace(Split(Right(namedRng.RefersTo, Len(namedRng.RefersTo) - 2), "]")(0), "[", "") 'remove =' and range in the file path
              NextRow = outputSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
              outputSheet.Range("A" & NextRow) = ws.Name
              outputSheet.Range("B" & NextRow) = Replace(Rng.Address, "$", "")
              outputSheet.Hyperlinks.Add Anchor:=outputSheet.Range("B" & NextRow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
              outputSheet.Range("C" & NextRow) = "'" & Rng.Formula
              outputSheet.Range("D" & NextRow) = filePath
              outputSheet.Range("E" & NextRow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
              outputSheet.Range("F" & NextRow) = "None" 'default
              Exit For
            End If
          Next namedRng
        End If
      Next Rng
    End If
  Next
  Columns("A:E").EntireColumn.AutoFit
  LastRow = outputSheet.Range("A" & Rows.Count).End(xlUp).Row
  
  For r = 2 To LastRow
    If ActiveSheet.Range("E" & r).Value = "File missing" Then
      countBroken = countBroken + 1
    End If
  Next
  
  If countBroken > 0 Then
    sInput = MsgBox("Tienes " & countBroken & " link(s) rotos.  Quieres reemplazarlos con sus valores? (son links con error 'File missing')" & vbNewLine & vbNewLine & "Si le das a 'No', podrás revisarlos y llamar a esta macro después otra vez", vbYesNo + vbExclamation, "Warning")
    If sInput = vbYes Then
      For r = 2 To LastRow
        If ActiveSheet.Range("E" & r).Value = "File missing" Then
          'Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).ClearContents
          ActiveSheet.Range("F" & r).Value = "Link Removed"
          Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).Value = Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).Value
        End If
      Next
      dummy = MsgBox(countBroken & " links rotos eliminados!", vbInformation)
    End If
  End If
End If


End Sub 



Private Function linkStatusDescr(statusCode)
'@source: https://www.pasalo.es/como-identificar-los-links-rotos-en-tu-excel-usando-una-macro-excel-vba/
'@returns: A readable text from the linkStatus. ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus)). \nSource: https://access-excel.tips/find-external-links-broken-links/

Select Case statusCode
  Case xlLinkStatusCopiedValues
    linkStatusDescr = "Copied values"
  Case xlLinkStatusIndeterminate
    linkStatusDescr = "Unable to determine status"
  Case xlLinkStatusInvalidName
    linkStatusDescr = "Invalid name"
  Case xlLinkStatusMissingFile
    linkStatusDescr = "File missing"
  Case xlLinkStatusMissingSheet
    linkStatusDescr = "Sheet missing"
  Case xlLinkStatusNotStarted
    linkStatusDescr = "Not started"
  Case xlLinkStatusOK
    linkStatusDescr = "No errors"
  Case xlLinkStatusOld
    linkStatusDescr = "Status may be out of date"
  Case xlLinkStatusSourceNotCalculated
    linkStatusDescr = "Source not calculated yet"
  Case xlLinkStatusSourceNotOpen
    linkStatusDescr = "Source not open"
  Case xlLinkStatusSourceOpen
    linkStatusDescr = "Source open"
  Case Else
    linkStatusDescr = "Unknown status"
  End Select
End Function

Noticias | Salud | Religión | Viajando con Héctor | Curiosidades | Deportes
Política de Privacidad