Listing Workbook Links

[markkramer]

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

[invisigirl]

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!

[NateO]

You could use a vba procedure like the one below:

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

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?

Hope this helps.

Test
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-10 15:05 ]