I am trying to use an existing excel macro that does
ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSou
when the links are broken, the dialog box appears to browse for the file. I could use
Application.DisplayAlerts = False
to suppress the dialog box. However, I need to know if there was a broken link. The Excel sheet that this macro works on has hundreds of external links. There is a lot of processing that happens after the UpdateLink call. That processing cannot happen unless all the links have been updated. If there is missing or broken links sources, I need to stop and notify the calling program that there was an error. I also tried checking Err.Number but that is always 0.
I need to somehow find out that there are broken external links and stop the macro if there are missing links. I also need a way to notify the calling program about this.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
to check the status it can also be done manual.
Edit>>Links>>Check status button.
I wished I could approach the status property, though could not find it.
Still here an example can be found to check the status:
http://www.mrexcel.com/arc
I changed the code a bit to get a function like:
Function CheckLinkedStatus()
Dim strLinkStats(10) As String, lsLinkStatus As XlLinkStatus
alinks = ActiveWorkbook.LinkSources
If IsEmpty(alinks) Then Exit Function
For i = 1 To UBound(alinks)
lsLinkStatus = ActiveWorkbook.LinkInfo(al
If lsLinkStatus = 1 Then
CheckLinkedStatus = True
Exit Function
Else
CheckLinkedStatus = False
End If
Next i
End Function
It checks the status of the active workbook. If a broken link is found then it returns True or else False
regards,
Jeroen
Thanks Jeroen. Here is what I modified a little bit more ;)
Function CheckLinkedStatus()
Dim LinkStatus As XlLinkStatus
Dim ExternalLinksSources() As Variant
Dim I As Integer
ExternalLinksSources = ActiveWorkbook.LinkSources
If IsEmpty(ExternalLinksSourc
CheckLinkedStatus = True
Exit Function
End If
For I = LBound(ExternalLinksSource
LinkStatus = ActiveWorkbook.LinkInfo(Ex
If LinkStatus <> xlLinkStatusOK And LinkStatus <> xlLinkStatusOld Then
CheckLinkedStatus = False
Exit Function
End If
Next I
CheckLinkedStatus = True
End Function
Thanks for the grade.
Good change you made. Made me search for other statusses as you used like xlLinkStatusOld.
here more infor about it: (though you might already found it):
http://msdn2.microsoft.com
Business Accounts
Answer for Membership
by: roos01Posted on 2007-08-10 at 12:00:43ID: 19672658
to suppres the dialog for updating links you might use this line:
Application.AskToUpdateLin
to turn it on:
Application.AskToUpdateLin
Jeroen