Listing Workbook Links

markkramer

Board Regular
Joined
May 8, 2002
Messages
162
My company is moving to a new server and a new folder structure. No doubt, this is going to play having with our workbooks that have links to other workbooks. Is there a way to list all the existing links in a workbook (along with the cell location where the link occurs) so that they can be printed out or saved in a Word document? That way, after the transition, it will be easier for us to put the links back in.

Any help would be appreciated!

Mark
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I think someone asked something similar the other day. The only way I know is to show ALL the formulas (Tools/Options/View/Formulas). Then of course you could go into File/Page Setup, check row and column headings, and print it out.

Somebody smart might be able to give you a better answer. Good luck! :)
 
Upvote 0
You could use a vba procedure like the one below:<pre>
Sub Links()
Dim ws As Worksheet, i As Integer, cl As Range, frm As String
Dim rng As Range, n As Integer, FrmCls As Range
Application.ScreenUpdating = False
On Error GoTo errorhandler
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "SearchResults"
redo:
Sheets("SearchResults").[a1] = "Results:"
n = Sheets.Count
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "SearchResults" Then GoTo 2
On Error GoTo 1
Set FrmCls = ws.Range("a1").SpecialCells(xlFormulas)
For Each cl In FrmCls
frm = cl.Formula
If InStr(frm, "[") Then
Set rng = cl
Sheets(n).[a65536].End(xlUp).Offset(1) = "'" & frm
Sheets(n).Hyperlinks.Add _
Anchor:=Sheets(n).[a65536].End(xlUp).Offset(, 1), _
Address:="", _
SubAddress:=ws.Name & "!" & rng.Address, _
TextToDisplay:=ws.Name & "!" & rng.Address(False, False)
End If
Next cl
1:
Set FrmCls = Nothing
Next ws
2:
Sheets("SearchResults").[a:b].EntireColumn.AutoFit
Application.ScreenUpdating = True
End
errorhandler:
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Select Case MsgBox("You need to delete the results sheet before " & _
"running this procedure." & Chr(13) & Chr(13) & _
"Click 'Yes' to refresh the results sheet, click" & _
" 'NO' to cancel this procedure", vbYesNo)
Case vbYes
Application.DisplayAlerts = False
Sheets("SearchResults").Delete
Application.DisplayAlerts = True
Links
Case vbNo
Application.ScreenUpdating = True
End Select
End Sub</pre>


You can place this in the workbook itself or a personal workbook and let her rip.

Question: Why move it to Word?
Question2: Why do anything in Word? :biggrin:

Hope this helps.

Test
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-10 15:05
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top