Update links and exit the Sub at the first link that is not found

[dparteka]

I have a main spreadsheet that houses many links to many other spreadsheets. Some of the other spreadsheets have not yet been created so they literally do not exist. I'm using the code shown below to update those links in the main-spreadsheet and that works perfectly fine but when it gets to the first link that it can't find the routine stops with a file explorer window popping up for locating the missing spreadsheet. Basically I'm looking for help with additional code that will exit and shutdown the routine when it gets to the first link/spreadsheet that it can't find and all the others after that.

Thank you for looking and any help offered will be greatly appreciated.

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

 

[Monty]

Hello Daparteka

You can handle the error that occurs when the link is not found by using error handling in your VBA code. Here's an example modification to your code:

On Error Resume Next
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
On Error GoTo 0

This way, if an error occurs (for example, if the link is not found), it will skip the error and continue to the next line of code without showing the file explorer window. `On Error GoTo 0` resets the error handling to the default mode afterward.

If you want to exit the routine when an error occurs, you can modify it further:

On Error Resume Next
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
If Err.Number <> 0 Then
    MsgBox "Error updating links: " & Err.Description
    Exit Sub ' This line will exit the subroutine
End If
On Error GoTo 0

This modification includes a message box notifying you about the error, and it will exit the subroutine using `Exit Sub` if an error occurs. Adjust the message or actions as needed.

 

[dparteka]

Excellent Monty... thank you