¿Los enlaces que no funcionan causan estragos en sus hojas de trabajo? ¡No te preocupes! Este tutorial le enseñará 3 formas sencillas de encontrar y reparar enlaces de Excel rotos, además de nuestra propia solución de un solo clic como bonificación adicional 🙂
Las celdas de Excel a menudo pueden vincularse a otros libros de trabajo para extraer información relevante de allí. Cuando un libro de origen se elimina, reubica o daña, las referencias externas a ese archivo se descomponen y sus fórmulas comienzan a generar errores. Obviamente, para arreglar las fórmulas, necesitas encontrar enlaces rotos. La pregunta es ¿cómo? Las respuestas siguen a continuación 🙂
Encuentra y repara enlaces rotos en Excel
Para detectar enlaces que no funcionan a otros libros de trabajo, realice los siguientes pasos:
- En la pestaña Datos, en el grupo Consultas y conexiones, haga clic en el Editar enlaces botón.
Si este botón está atenuado, eso significa que no hay referencias externas en su libro de trabajo.
- En el cuadro de diálogo Editar enlaces, puede ver una lista de todas las fuentes externas a las que hace referencia su libro de trabajo actual. Cuando se abre esta ventana por primera vez, todos los enlaces tienen el estado Desconocido. Para aclarar el estado, haga clic en el Comprobar estado botón en el lado derecho:
- Cuando la información se actualice, revise el estado de cada enlace. Para mayor comodidad, puede hacer clic en el encabezado de la columna Estado para ordenar los registros en consecuencia.
Obviamente, los enlaces diagnosticados como Error: Fuente no encontrada estan rotos. En mi libro de trabajo, hay dos enlaces de este tipo:
- Sigamos adelante y arreglemos los enlaces rotos. Para esto, seleccione la fuente que reporta un error y haga clic en el botón Cambiar fuente botón de la derecha:
- En la ventana Cambiar origen que se abre, navegue por las carpetas de su computadora hasta que encuentre el libro de trabajo correcto, selecciónelo y haga clic en Aceptar (o haga doble clic en el libro de trabajo):
- Repita el paso anterior para cada fuente problemática individualmente. Cuando haya terminado, haga clic en el Cerca botón.
Después de corregir todas las fuentes erróneas, puede notar que su lista de enlaces se ha acortado. El motivo es que es posible que haya tenido varias apariciones del mismo libro de trabajo y, después de cambiar la fuente, las incorrectas desaparecieron de la lista.
Por ejemplo, teníamos los siguientes pares que se referían al mismo archivo: Colrado report.xlsx (mal escrito) y Colorado report.xlsx (correcto); Florida_report.xlsx (inexistente) y Florida report.xlsx (correcto). Después de corregir los enlaces, las fuentes incorrectas desaparecieron y ahora tenemos esta lista:
Identifique y corrija enlaces rotos con Buscar y reemplazar
La función Editar enlaces discutida anteriormente puede ayudarlo a obtener rápidamente una lista de todas las fuentes externas en un libro de trabajo, pero no muestra qué celdas contienen esas referencias externas. Para identificar dichas celdas, puede usar Buscar y reemplazar de Excel.
Encuentre enlaces rotos a todos o a un libro de trabajo específico
Los enlaces externos siempre apuntan a otro archivo de Excel que tiene «.xl» como parte de la extensión del nombre de archivo, como .xls, .xlsx, .xlsm, etc. Puede utilizar este hecho cuando busque referencias a libros de trabajo externos. O puede buscar texto específico (subcadena) dentro de un nombre de libro en particular. Los pasos detallados siguen a continuación.
- Presione Ctrl + F para abrir el cuadro de diálogo Buscar y reemplazar. O haga clic en Buscar y seleccionar > Buscar… en la pestaña Inicio del grupo Edición.
- En el cuadro de diálogo Buscar y reemplazar, haga clic en el botón Opciones.
- Dependiendo de si desea buscar todos los enlaces externos en un libro de trabajo o solo las referencias a un archivo específico, escriba uno de los siguientes en el cuadro Buscar:
- Para buscar todos los enlaces, escriba .SG.
- Para buscar enlaces a un libro de trabajo en particular, escriba el nombre de ese libro de trabajo o su parte única.
- En el cuadro Dentro de, seleccione Libro de trabajo para buscar en todas las pestañas o Sábana para buscar en la hoja de trabajo actual.
- En el cuadro Buscar en, elija Fórmulas.
- Haga clic en el botón Buscar todo.
Y ahora viene la parte clave: analizar los resultados.
si buscaste todas las referencias externas, luego haga clic en el encabezado de la columna Valor para ordenar los resultados por valor y desplazarse hacia abajo en la lista. ¡Los enlaces rotos a otros libros de trabajo tendrán un #REF! error en la columna Valor.
Si buscó referencias a un libro de trabajo específicosimplemente revise los resultados.
Por ejemplo, sabemos que algunas celdas del informe de resumen apuntan al archivo inexistente (Colrado report.xlsx) y deseamos saber exactamente qué celdas. Entonces, escribimos la palabra mal escrita colrado en el cuadro Buscar y presionamos Buscar todo. Como resultado, Excel muestra 3 referencias de este tipo en 2 hojas diferentes. ¡La referencia! los errores en la columna Valor confirman que los tres enlaces están rotos:
Nota. Este método de diagnóstico de enlaces rotos no es 100% confiable. Por ejemplo, un #REF! El error puede ser devuelto por una fórmula en la celda vinculada en el libro de origen, y también se mostrará en los resultados de Buscar todo. Eso no significa, sin embargo, que la referencia a ese otro libro esté rota. Por lo tanto, al usar este enfoque, tiene sentido hacer clic en cada error y volver a verificar las referencias manualmente.
Reparar enlaces rotos a un libro de trabajo específico
En la lista de resultados de Buscar todos, puede hacer clic en cualquier elemento para navegar a la celda que contiene el enlace y editar cada uno individualmente. O puede usar la función Reemplazar todo para corregir todas las ocurrencias de un enlace no válido a la vez. Así es cómo:
- En el cuadro de diálogo Buscar y reemplazar, cambie a la Reemplazar pestaña.
- En el cuadro Buscar, escriba la ruta o el nombre de archivo incorrectos.
- En el cuadro Reemplazar con, escriba el nombre de archivo o la ruta correctos.
- Haga clic en Reemplazar todo.
Nota. Después de hacer clic en el botón Reemplazar todo, es posible que se abra la ventana Actualizar valores y se le solicite que elija el libro de trabajo de origen. No haga eso y simplemente haga clic en Cancelar sin seleccionar nada.
Como ejemplo, reemplacemos un libro de trabajo incorrecto con el nombre Colrado report.xlsx por el correcto Colorado report.xlsx. En este caso particular, reemplazar una sola palabra (colrado) también funcionará. Sin embargo, tenga en cuenta que un texto específico se reemplazará en cualquier lugar dentro de la cadena de ruta (la ruta completa a un archivo se muestra si el libro de origen está cerrado en ese momento). Por lo tanto, cuanto más pequeño sea el texto que ingrese, mayor será la posibilidad de un error.
Una vez realizada la sustitución, el #REF! los errores desaparecen de los resultados y en su lugar se muestran los valores normales:
De manera similar, puede reemplazar el ruta a un archivo fuente. Por ejemplo, si el libro de origen estaba originalmente en la carpeta Documentos y luego lo movió a la subcarpeta Informes en la misma carpeta, puede reemplazar \Documentos\ con \Documentos\Informes\.
Alguien puede decir que es ridículo usar Buscar y reemplazar para resolver el problema de los enlaces rotos, pero que yo sepa, esta es la única característica incorporada que puede ayudarlo. encontrar celdas que contengan enlaces rotos.
Verifique si hay enlaces rotos con VBA
El siguiente código recorre cada referencia externa en un libro de trabajo e intenta averiguar si está roto o no. Para encontrar archivos externos, utilizamos el método LinkSources. Para identificar enlaces rotos, se utiliza el método LinkInfo.
Sub FindBrokenLinks()
linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)
Dim reportHeaders() As String
Dim rangeCur As Range
Dim sheetCur As Worksheet
Dim rowNo As Integer
Dim linkFilePath, linkFilePath2, linkFileName As String
Dim linksStatusDescr As String 'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus
Dim sheetReportName As String
sheetReportName = "Broken Links report"
linksStatusDescr = "File missing"
reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")
rowNo = 1 'Header row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then
ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear
Else
Sheets.Add.Name = sheetReportName
End If
Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)
For indI = 0 To UBound(reportHeaders)
sheetReport.Cells(rowNo, indI + 1) = reportHeaders(indI)
Next
For Each sheetCur In ActiveWorkbook.Worksheets
If sheetCur.Name <> sheetReport.Name Then
For Each rangeCur In sheetCur.UsedRange
If rangeCur.HasFormula Then
For indI = LBound(linksDataArray) To UBound(linksDataArray)
linkFilePath = linksDataArray(indI) 'LinkSrouces returns the full file path with the file name
linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, "\")) 'extract only the file name
linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "\")) & "[" & linkFileName & "]" 'the file path with the workbook name in square brackets
linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)
If xlLinkStatusMissingFile = linksStatusCode And (InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2)) Then
rowNo = rowNo + 1
With sheetReport
.Cells(rowNo, 1) = sheetCur.Name
.Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
.Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
.Cells(rowNo, 3) = "'" & rangeCur.Formula
.Cells(rowNo, 4) = linkFilePath
.Cells(rowNo, 5) = linksStatusDescr
End With
Exit For
End If
Next indI
For Each namedrangeCur In Names
If InStr(rangeCur.Formula, namedrangeCur.Name) Then
linkFilePath = ""
linksStatusCode = -1
If 0 < InStr(namedrangeCur.RefersTo, "[") Then
linkFilePath = Replace( Split( Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)
End If
If xlLinkStatusMissingFile = linksStatusCode Then
rowNo = rowNo + 1
With sheetReport
.Cells(rowNo, 1) = sheetCur.Name
.Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
.Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
.Cells(rowNo, 3) = "'" & rangeCur.Formula
.Cells(rowNo, 4) = linkFilePath
If 0 < Len(linkFilePath) Then
.Cells(rowNo, 5) = linksStatusDescr
End If
End With
End If
Exit For
End If
Next namedrangeCur
End If
Next rangeCur
End If
Next
Columns("A:E").EntireColumn.AutoFit
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Se genera una lista de enlaces no válidos en una nueva hoja de trabajo denominada Informe de enlaces rotos. La columna B tiene un hipervínculo a la celda que contiene el enlace.
Puede insertar el código en su propio libro de trabajo o descarga nuestro archivo de muestra con la macro así como las instrucciones paso a paso sobre cómo usarla.
Nota. Este código solo encuentra enlaces a libros de trabajo no válidos (inexistentes, movidos o eliminados), pero no a hojas faltantes. El motivo es que el método LinkInfo solo comprueba el nombre del archivo. Un intento de verificar el nombre de una hoja da como resultado el Error 2015.
Encuentra enlaces rotos en Excel con un clic
Mientras lee la primera parte de este tutorial, es posible que se sienta un poco desanimado porque no existe una manera simple de encontrar todos los enlaces rotos en un archivo, por ejemplo, haciendo clic en un solo botón. Aunque tal solución no existe en Excel, nada nos impide desarrollarla nosotros mismos 🙂
Para los usuarios de Ultimate Suite, proporcionamos una herramienta de un solo clic para encontrar todas las referencias externas en un libro de trabajo o solo enlaces rotos. Simplemente haga clic en ¿Buscar enlaces? en la pestaña Herramientas de Ablebits, e inmediatamente verá una lista de todos los enlaces en el libro de trabajo actual, donde los no válidos están resaltados en rojo claro. Para limitar la lista a enlaces que no funcionan, seleccione la Solo enlaces rotos caja.
Al hacer clic en una dirección de celda en el panel del complemento, lo llevará a una celda que contiene un enlace particular. ¡Eso es todo al respecto!
A diferencia del código VBA anterior, el complemento encuentra todo tipo de enlaces rotos incluidos aquellos en los que falta una hoja o está mal escrita.
Así es como se comprueban los enlaces rotos en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Versión totalmente funcional de Ultimate Suite de 14 días (archivo .zip)
(.xlsm)