Finding worksheets with Broken Links to other workbooks
 

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 :-)
--
Regards, Alan

Finding worksheets with Broken Links to other workbooks
 

try bill manville's findlinks

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

--


Gary

"OldDude" wrote in message
...
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 :-)
--
Regards, Alan

Finding worksheets with Broken Links to other workbooks
 

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


"Gary Keramidas" wrote:

try bill manville's findlinks

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

--


Gary

"OldDude" wrote in message
...
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 :-)
--
Regards, Alan