Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating links to a worksheet on a network. | Excel Worksheet Functions | |||
Creating links to a worksheet on a network. | Excel Worksheet Functions | |||
Worksheet links | Excel Worksheet Functions | |||
Listing the links | Excel Discussion (Misc queries) | |||
Worksheet links | Excel Worksheet Functions |