Finding worksheets with Broken Links to other workbooks

[OldDude]

Hi,

I have spent hours searching forums and trying in vain to reliably test
whether any workbook in a nominated folder structure contains invalid links.
It need to work in Excel XP & 2003.

I use FileSys/NewSearch to populate the FoundFiles object with all Excel
workbooks in the folder tree:
With FileSys
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = True
'.Filename = "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
If Not .Execute() > 0 Then
'No Excelworkbooks found
MsgBox "There were no files found."
End If
End With
then open each 1 (with update suppressed) and use LinkSources to populate
an array with all the external links from that workbook :
TargetLinks = TargetWorkBook.LinkSources(xlExcelLinks)

If I then try to loop through and check their status using:
TargetWorkBook.LinkInfo(TargetLinks(loopcounter),
xlLinkInfoStatus),
I get a meaningless result, as they have not been updated.
However, if I try updating them, using :
TargetWorkBook.UpdateLink Name:=TargetWorkBook.LinkSources

I get an "File Open" dialog for any broken links, although valid links will
produce a correct result. If I try setting DisplayAlerts = False, I get
"Runtime Error 1004, Method 'Update Link' of object '_Workbook' failed.

Am I going about this the wrong way? Is there a way to find all workbooks in
a folder tree containing broken links without opening them?

Please help while I still have some hair left

 

[Gary Keramidas]

try bill manville's findlinks

http://www.bmsltd.co.uk/MVP/Default.htm

 

[OldDude]

Thanks Gary,

Unfortunately, FindLinks is not useful in this case, as there are
potentially 100's of workbooks containing links in the folder structure, so I
need to be able to check them automatically & just list workbooks that
contain broken links.

Regards, Alan