Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing all external links within a file
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing all external links within a file
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" wrote in message ... 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 (Clayton McGuire) wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing all external links within a file
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 '************** "Clayton McGuire" wrote in message om... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing all external links within a file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Links in Excel 2007 with variable source file | Excel Worksheet Functions | |||
External workbook links don't update when source file changes | Excel Worksheet Functions | |||
Persistent External File Links | Setting up and Configuration of Excel | |||
Variables in links and external file references | Links and Linking in Excel | |||
Listing the links | Excel Discussion (Misc queries) |