Listing all external links within a file

  • Thread starter Thread starter Clayton McGuire
  • Start date Start date
C

Clayton McGuire

Dear Gurus,

Windows 2K, Excel 2K.

Before anyone mentions them... Bill Manville's Link Finder and
Navigator Utilities are both great and really useful tools, but not
for what I need for this little project.

So on to the query. Hopefully quite simple, but maybe not.

I need to produce a list showing all external links in a workbook,
something along the lines of:

Sheet Name Address Links to
Sheet1 $A$1 Test.xls
Sheet1 $A$2 Test2.xls
Sheet2 $A$3 Test3.xls

....but I can't find an appropriate property at the range object level.
Something useful like Activecell.HasExternalLinksToAnotherWorkbook
and Activecell.TheNameOfTheExternallyLinkedWorkbook doesn't seem to be
forthcoming. And I'd rather not use methods that involve searching
for characters like [, ], !, \, .xls, etc as Excel is not that hard to
trick and you end up with additional reported links where none exist.

Seeing Linkfinder and Navigator Utilities in action proves that it can
be done, I just haven't been able to work out how. Any new ideas on
this one would be greatly appreciated.

Regards,

Clayton.
 
Yep
set vArr = ThisWorkbook.Linksources(xlExcelLinks)
should be

vArr = ThisWorkbook.Linksources(xlExcelLinks)

I would still get the list and use it to search using the find command.

--
Regards,
Tom Ogilvy


keepitcool said:
Typo for Tom :)

SET vArr=
s/b
LET vArr=


Excel has a rudimentary way of exposing it's links to the user.
workbook.linksources is just an array that gives you the links, not the
places where they're used.

However to achieve what to try to do, it's either looping or you might
try with following code (which needs to be refined, but alas...

If .Formula Like "*[[]*]*!*" Then

put it in some sort of loop and end up with:


Sub ListLinkst()

Dim f As Range
Dim r As Range
Dim c As Range

On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set r = Worksheets(2).Cells(1)
Set f = Worksheets(1).Cells.SpecialCells(xlFormulas)

If Not f Is Nothing Then
For Each c In f
With c
If .Formula Like "*[[]*]*!*" Then
r(1, 1) = .Address
r(1, 2) = "'" & .Formula
Set r = r.Offset(1)
End If
End With
Next c
End If

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub


HTH


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Dear Gurus,

Windows 2K, Excel 2K.

Before anyone mentions them... Bill Manville's Link Finder and
Navigator Utilities are both great and really useful tools, but not
for what I need for this little project.

So on to the query. Hopefully quite simple, but maybe not.

I need to produce a list showing all external links in a workbook,
something along the lines of:

Sheet Name Address Links to
Sheet1 $A$1 Test.xls
Sheet1 $A$2 Test2.xls
Sheet2 $A$3 Test3.xls

...but I can't find an appropriate property at the range object level.
Something useful like Activecell.HasExternalLinksToAnotherWorkbook
and Activecell.TheNameOfTheExternallyLinkedWorkbook doesn't seem to be
forthcoming. And I'd rather not use methods that involve searching
for characters like [, ], !, \, .xls, etc as Excel is not that hard to
trick and you end up with additional reported links where none exist.

Seeing Linkfinder and Navigator Utilities in action proves that it can
be done, I just haven't been able to work out how. Any new ideas on
this one would be greatly appreciated.

Regards,

Clayton.
Click to expand...
Click to expand...
 
Clayton,

I would like to know why the list of links, generated in the utilities you
mentioned, don't meet your objectives?
Also thought you would want to know: I went to your website, after clicking
on the "Links" link, my IE 6 web browser quit. I had to reboot to get it to
work again...

Regards,
Jim Cone
San Francisco, CA
'**************
 
Thanks to all who replied. Looks like the pattern search is the only
way to go. Which is kind of what I expected, but good to have
confirmation.

Cheers,

Clayton
 
Back
Top