Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Listing Workbook Links

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •