Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brookfield
 
Posts: n/a
Default Need listing of links in a worksheet

a new software is being implemented however all of the files will change and
not have a space in the name. this will break all of my links. how can I
know what the links were from each file. is there a macro that I can run?
  #2   Report Post  
JMB
 
Posts: n/a
Default

I use this macro to find the cell addresses that are linked to external
workbooks as well as the external workbooks path. I added a column to show
what the linked formula is (don't know if you'll need that or not).

If you run this on the active workbook it'll create a list of your links.

Sub FindLink()
Dim Report As Object
Set Report = Sheets.Add
Dim LinkList As Variant
Dim LinkPath As Variant

On Error Resume Next

LinkList = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(LinkList) Then
For i = 1 To UBound(LinkList)
LinkPath = Split(LinkList(i), "\", -1, vbTextCompare)
For Each x In Worksheets
If x.Name < Report.Name Then
For Each y In x.UsedRange
If InStr(1, y.Formula, LinkPath(UBound(LinkPath)), vbTextCompare) 0
Then
Report.Select
ActiveCell.Value = x.Name & y.Address
ActiveCell.Offset(0, 1).Value = LinkList(i)
ActiveCell.Offset(0, 2).Value = Replace(y.Formula, _
"=", "", 1, 1, vbTextCompare)
ActiveCell.Offset(1, 0).Select
End If
Next y
End If
Next x
Next i
End If

End Sub

"Brookfield" wrote:

a new software is being implemented however all of the files will change and
not have a space in the name. this will break all of my links. how can I
know what the links were from each file. is there a macro that I can run?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating links to a worksheet on a network. Peter Vousden Excel Worksheet Functions 0 April 7th 05 01:17 AM
Creating links to a worksheet on a network. Peter Vousden Excel Worksheet Functions 0 April 7th 05 01:17 AM
Worksheet links Debbie Excel Worksheet Functions 0 January 26th 05 10:01 PM
Listing the links Robert Seynaeve Excel Discussion (Misc queries) 2 January 26th 05 11:24 AM
Worksheet links Bruce the Battler Excel Worksheet Functions 5 December 6th 04 05:49 AM


All times are GMT +1. The time now is 11:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"