Ok, haven;t given up on this problem, though have not progressed as well as I would have liked.

Here is the code that I am using ("Test" is the man macro I run which calls the two subsequent macros) at the moment:

[vba]Option Explicit

Sub Test()

Call Find_Internal_Links_in_Activesheet_only

Call Find_External_Links_in_CLOSED_Workbooks_references_only

End Sub

Sub Find_Internal_Links_in_Activesheet_only()

Dim cel As Range

ActiveCell.DirectPrecedents.Select

For Each cel In Selection

MsgBox cel.Address

Next

Call test_find_external_links

End Sub

Sub Find_External_Links_in_CLOSED_Workbooks_references_only()

Dim cel_formula_text As String, ext_link As String
Dim cel_formula_length As Double, charac_position As Double
Dim startlink_ind As Boolean, letter As String

'initialise
cel_formula_text = Selection.Formula
cel_formula_length = Len(cel_formula_text)
startlink_ind = False
ext_link = ""

For charac_position = 1 To cel_formula_length Step 1
letter = Mid(cel_formula_text, charac_position, 1)
If startlink_ind Then
ext_link = ext_link & letter
End If

'picks up the ext_link using the "'" value
If letter = "'" And Not startlink_ind Then
startlink_ind = True
ext_link = "'"
ElseIf letter = "'" And startlink_ind Then
While (charac_position <= cel_formula_length) And (("A" <= letter And letter <= "Z") Or ("a" <= letter And letter <= "z") Or ("0" <= letter And letter <= "9") Or letter = "!" Or letter = "$" Or letter = "'" Or letter = ":")
ext_link = ext_link & letter

charac_position = charac_position + 1
letter = Mid(cel_formula_text, charac_position, 1)
Wend

MsgBox ext_link
startlink_ind = False

End If
Next charac_position

End Sub[/vba]
So the Macro named "Find_Internal_Links_in_Activesheet_only" finds links in the activeworksheet of the activecell.

and

the macro named "Find_External_Links_in_CLOSED_Workbooks_references_only" finds links to CLOSED external workbook references.

I haven't been able to fault the macros in finding the specified types of links.

The only 2 types of links that aren't found with the above macros are:
  • An external link of an already open workbook
  • An internal link in a different worksheet in the same workbook
I had thought that the code in the previous post found these, but als, it fails when there are closed workbooks in the activecell i.e. displays nothing in the Msgbox output.

Ok, so mike, Bob, could you please help to amend the above code to help isolate out the 2 types of links required.

I have tried hard but am not getting anywhere.

Any help appreciated.

regards,