Trap error – Excel file link

Topic

New Reply

When an Excel file is opened and it contains an invalid link to another workbook, Excel responds with a dialog box. Is it possible to trap the error, run some code to fix the invalid link, and suppress the dialog box? If so, how…?

I am just looking for advice how to detect and respond to the invalid link; I will muddle my way thru the other stuff.

Reply | Quote

Replies

You can use the Workbook_Open event in the ThisWorkbook module:

Code:

Private Sub Workbook_Open()
  Dim i As Integer
  Dim arrLinks
  Application.DisplayAlerts = False
  
  arrLinks = Me.LinkSources(xlExcelLinks)
  For i = 1 To UBound(arrLinks)
	If Dir(arrLinks(i)) = "" Then
	  ' File has not been found
	  MsgBox arrLinks(i) & " can't be found", vbExclamation
	End If
  Next i
  Application.DisplayAlerts = True
End Sub

Reply | Quote

Thanks Hans. Worked 100%

Reply | Quote