VERIFICAR EL ESTADO DE LOS ENLACES A OTROS ARCHIVOS DESDE NUESTRO PROYECTO

Hola a todos:

Esta semana un lector me enviaba la siguiente consulta: Se trata de cómo se podría verificar en un archivo si los vínculos con otros archivos, utilizados en fórmulas, referencias, etc, son correctos y no se han eliminado o modificado.

Este es un tema muy recurrente cuando trabajamos con Excel y en entornos en los que compartimos archivos y datos con otros compañeros. Es posible que por error alguien elimine un archivo que está vinculado a nuestro proyecto y no nos demos cuenta hasta que sea demasiado tarde.

Pues bien, para estos casos en concreto, para poder ver e identificar los archivos vinculados y su estado, se puede realizar con VBA un pequeño proceso que extraiga esta información.

Pero antes vamos a desarrollar un ejemplo. Imaginad que tenemos un archivo con los siguientes ID’s y nombres de campo:

y además una serie de archivos donde se almacena la información por tipo de campo:

Dado que tenemos que componer nuestra base de datos con la información del resto de archivos, vamos a utilizar la función Buscarv() para traernos la información.

Una vez que la tenemos, el resultado es el siguiente:

Como podéis ver, las fórmulas funcionan correctamente y los vinculos no contienen errores. Pero resulta que un compañero de nuestro equipo, por equivacación, ha eliminado el archivo que contiene los nombres y, por si fuese poco, otra compañera ha eliminado la hoja del archivo «ASIGNATURAS» que contenía los datos.

Así se presentaría nuestra información una vez que abrimos el archivo. (en el caso de las hoja eliminada al principio nos aparecerá un error de referencia #¡REF! y al evaluar el error se convertirá en #N/A). Pero en el caso de los nombres no nos vamos a dar cuenta, dado que Excel se queda con el valor anterior.

Para realizar el control en nuestro archivo, utilizaremos la siguiente macro:

Sub VERIFICAR_LINKS_EXTERNOS()
'Declaramos variables
Dim i As Integer, infoLink As Integer
Dim eLinks As Variant
Dim MsgLnk As String
Dim fin As Integer
'Borramos datos en Hoja Resumen
Sheets("RESUMEN").Select
With Sheets("RESUMEN")
fin = Application.CountA(.Range("A:A"))
If fin > 1 Then .Range("A2:B" & fin).ClearContents
End With
'si no existen referencias a otros archivos salimos del proceso
eLinks = ThisWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(eLinks) Then
MsgBox ("NO EXISTEN LINK EXTERNOS EN EL LIBRO")
Exit Sub
End If
'Con un bucle verificamos el estado de cada link e identificamos
'su estatus
For i = 1 To UBound(eLinks)
infoLink = ActiveWorkbook.LinkInfo(eLinks(i), xlLinkInfoStatus)
If infoLink = xlLinkStatusMissingFile Then
MsgLnk = "Falta el archivo de origen"
ElseIf infoLink = xlLinkStatusMissingSheet Then
MsgLnk = "Falta la hoja del archivo de origen"
ElseIf infoLink = xlLinkStatusCopiedValues Then
MsgLnk = "Valores copiados"
ElseIf infoLink = xlLinkStatusIndeterminate Then
MsgLnk = "No se puede determinar el estado"
ElseIf infoLink = xlLinkStatusInvalidName Then
MsgLnk = "El nombre no es válido"
ElseIf infoLink = xlLinkStatusNotStarted Then
MsgLnk = "No iniciado"
ElseIf infoLink = xlLinkStatusOld Then
MsgLnk = "El estado puede no estar actualizado"
ElseIf infoLink = xlLinkStatusSourceNotCalculated Then
MsgLnk = "No se ha calculado todavía"
ElseIf infoLink = xlLinkStatusSourceNotOpen Then
MsgLnk = "El documento de origen no está abierto"
ElseIf infoLink = xlLinkStatusSourceOpen Then
MsgLnk = "El documento de origen está abierto"
ElseIf infoLink = xlLinkStatusOK Then
MsgLnk = "Sin errores"
End If
'Pasamos los datos a la hoja RESUMEN
With Sheets("RESUMEN")
.Cells(1, 1) = "RUTA ARCHIVO"
.Cells(1, 2) = "MENSAJE"
.Cells(i + 1, 1) = eLinks(i)
.Cells(i + 1, 2) = MsgLnk
End With
'Mostramos datos en la ventana de Inmediato en nuestro editor de VBA
Debug.Print "Ruta = "; eLinks(i) & " | Mensaje = "; MsgLnk
Next
End Sub

El resultado es el siguiente:

En aquellos casos que o bien han eliminado el archivo o una hoja, tenemos información precisa de lo que ha ocurrido y a que archivos afecta. En el resto de casos nos encontraremos con que no hay errores, aunque es posible que si lleváis tiempo sin ejecutar abrir el archivo (lo cual es muy probable), si ejecutáis la información en el momento de abrir, os aparezca el mensaje «El estado puede no estar actualizado«, lo cual indica que es aconsejable actualizar las fórmulas.

Al final de la macro os dejo la posibilidad de ver la misma información en la ventana de Inmediato de vuestro editor de VBA, por si no queréis utilizar la hoja «Resumen».

He decido poner en la macro, todos los mensajes posibles que Excel puede mostrar, aquí tenéis información sobre este tema: https://msdn.microsoft.com/es-es/library/office/ff196932.aspx

Espero que os resulte de utilidad a la hora de verificar qué archivos están dando problemas o simplemente si todo nuestro trabajo está correctamente vinculado.

Saludos.

PD: cuando ejecutéis la macro en vuestro equipo, el mensaje será para todos los casos «Falta el archivo de origen», dado que, como es obvio esos archivos están en mi equipo 🙂

Descarga el archivo de ejemplo pulsando en: VERIFICAR EL ESTADO DE LOS ENLACES A OTROS ARCHIVOS DESDE NUESTRO PROYECTO