-
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,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules