Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-07-2017, 09:00 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default Macro how to add hyperlink to text in document

Hi



I want to format a document with Bible verses scattered throughout the document. They will be hyperlinked to relevant Bible chapters and verses in other documents.

This is an example of paragraph:

John was apparently among the first of John the Baptist’s disciples to be introduced to Jesus and one of the first four to be invited to be followers of Christ. (Mark 1:16-20; John 1:35-39) John, who may have been a cousin of Jesus, developed a close friendship with Jesus, becoming known as “the disciple whom Jesus loved.” (John 13:23; John 21:20, 24) John was present at the heartbreaking scene of execution, where Jesus entrusted the care of his mother to John. Also, it was John who outran Peter as they sped to the tomb to investigate the report that Jesus had risen.— John 19:26, 27; John 20:2-4.

So from the start I would like the macro to search for Mark, John etc and then a number followed by a colon and another number (eg 1:1). Once found, it would create an underlying hyperlink formatted as follows: W:\Skydrive\Docs \John\Mark.htm#chpt_1_16, and apply it to the text so that it is hyperlinked.

Thanks
Reply With Quote
  #2  
Old 10-09-2017, 02:38 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

The following code will replace the text specified with hyperlinks.

Code:
Option Explicit

Sub insert_authority_hyperlinks()

    Const AUTHORITY_PLACEHOLDER             As String = "<authority>"
    Const PATH_TO_REFERENCES                As String = "W:\Skydrive\Docs\<authority>.htm#chpt_"
    Const SEARCH_TEXT                       As String = "([\(])(<authority>)(*)([)])"
    
    'adjust the 3 to the number you actually need
    Dim authorities(1 To 3)                 As String
    Dim authority                           As Variant
    Dim rng                                 As Word.Range
    Dim name                                As String
    Dim chapter                             As String
    Dim verses(1 To 2)                      As String
    Dim end_of_rng                          As Long
    
    authorities(1) = "John"
    authorities(2) = "Matthew"
    authorities(3) = "Mark"
    ' etc
    
    For Each authority In authorities
        Set rng = ActiveDocument.StoryRanges(wdMainTextStory)
        Do
            With rng.Find
                .Text = Replace(SEARCH_TEXT, AUTHORITY_PLACEHOLDER, authority)
                .MatchWildcards = True
                .Forward = True
                .Wrap = wdFindStop
                .Execute
                If .Found Then
                   
                    end_of_rng = rng.End + 1
                    name = Split(rng.Text, " ")(0)
                    name = Replace(name, "(", "")
                    chapter = Split(Split(rng.Text, " ")(1), ":")(0)
                    verses(1) = Split(Split(rng.Text, ":")(1), "-")(0)
                    'Just in case you need the end reference
                    verses(2) = Split(Split(rng.Text, ":")(1), "-")(1)
                    verses(2) = Replace(verses(2), ")", "")
    
                    ActiveDocument.Hyperlinks.Add _
                        Anchor:=rng, _
                        Address:=Replace(PATH_TO_REFERENCES, AUTHORITY_PLACEHOLDER, authority) & chapter & "_" & verses(1), _
                        TextToDisplay:=rng.Text
                        
                    rng.Start = end_of_rng
                    rng.End = ActiveDocument.StoryRanges(wdMainTextStory).End
                    
                End If
            End With
        Loop Until Not rng.Find.Found
     Next
End Sub
The caveats to this code are

1. You may get unwanted spaces in the hyperlink if these are present in the source text. If that's the case then you'd want to add some trim statements where we create the authority, chapter and verses strings.

2. I've actually no idea if the format you provided for a hyperlink is valid.

I've tested the above on a word document containing

(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)


and the field codes of the hyperlinks created are of the form


{ HYPERLINK "W:\\Skydrive\\Docs\\John.htm#chpt_1_16" }
....
{ HYPERLINK "W:\\Skydrive\\Docs\\Matthew.htm#chpt_9_89" }

But of course on my system the hyperlinks do nothing as the targets don't exist.
Reply With Quote
  #3  
Old 10-09-2017, 03:38 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Hey slaycock WOW!!...thanks for the quick reply I will give it a go...
Reply With Quote
  #4  
Old 10-09-2017, 04:32 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Hi slaycock,

I have tried the script, and it works of sorts  but is selecting all the text between the brackets and hyperlinking it to one and not seperating out the different references ie John 13:23; John 21:20, 24 is one hyperlink instaed of 2.

I wondered if it would be possible to find a colon ( then check the characters before the colon for numbers, if present select and hyperlink?

Thanks for your work….
Reply With Quote
  #5  
Old 10-09-2017, 05:00 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

OK. The code currently only looks for references of the form '(John 1:x-y)'

From your original post there are other variants of that along the lines of

(Mark 1:16-20; John 1:35-39) two different references
(John 13:23; John 21:20, 24) three references using different separators ';' and ','.
John 19:26, 27; John 20:2-4. No brackets plus two separators ';' and ','.

There is a least consistency in the use of ';' and ',' in the examples you've provided.

Think of the possible confusion to your users when converting the following

John 21:20, 24

I'm assuming that this is two hyperlinks

John 21:20 and
John 21:24

You could have the two hyperlinks as

John 21:20, 24

Note that the comma isn't blue. I think is confusing for a user as its not clear if the second hyperlink is independent to the first.

Alternatively we can convert

John 21:20, 24

to

John 21:20, John 21:24

during the search, which is a bit more wordy but much clearer.

I'm happy to have a look at upgrading the search function but it would be helpful to know what format you'd prefer the output (and also if there are any further variants in how references are provided)
Reply With Quote
  #6  
Old 10-09-2017, 05:09 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Thanks again.

I am the user and would understand any hyperlinks created

You are right in your assumption

This is two hyperlinks

John 21:20 and
John 21:24

And would be as two hyperlinks

John 21:20, 24

Here is another example of what I have to format:

the Word: Or “the Logos.” Greek, ho loʹgos. Here used as a title, it is also used at John 1:14 and Revelation 19:13. John identified the one to whom this title belongs, namely, Jesus. This title was applied to Jesus during his prehuman existence as a spirit creature, during his ministry on earth as a perfect man, and after his exaltation to heaven. Jesus was God’s Word of communication, or Spokesman, for conveying information and instructions to the Creator’s other spirit sons and to humans. So it is reasonable to think that prior to Jesus’ coming to earth.—Genesis 16:7-11; Genesis 22:11; Genesis 31:11; Exodus 3:2-5; Judges 2:1-4; Judges 6:11, 12; Judges 13:3.

So some refs are without the semi-colon. I suppose that was why I was trying to search for the colon as a marker.
Reply With Quote
  #7  
Old 10-09-2017, 05:36 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

Based on your examples we can see that there is a pattern.

1. Henceforth we can ignore the enclosing () if they are present.

2. The initial search should be for

<authority><1 or more spaces><number><colon>

3. If we find a reference of the above form then we can define three following options

a) a hyphen John 1:1-2
b) a semi-colon
c) a number
Reply With Quote
  #8  
Old 10-09-2017, 05:38 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Sounds good...
Reply With Quote
  #9  
Old 10-09-2017, 06:23 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

Sorry there was quite a bit more that should have been part of my last post but it didn't materialise.

I'll look at this again tonight as I can't continue to ignore work work.....
Reply With Quote
  #10  
Old 10-09-2017, 06:29 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Thanks alot...
Reply With Quote
  #11  
Old 10-10-2017, 08:10 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

Well, that was an entertaining exercise. There are certainly some vagaries and ranges and moving ranges after inserting a hyperlink.

The following code is now much close to what you want. You have to ensure that the last text is not s reference, hence my 'some more text'

Code:
Option Explicit

' Note the use of module scope constants and variable
 
Const NAME                              As Long = 0
Const CHAPTER                           As Long = 1
Const VERSE_1                           As Long = 2
Const VERSE_2                           As Long = 3
Const NULL_REFERENCE                    As String = ",,,"

Const SPACE                             As String = " "
Const COMMA                             As String = ","
Const SEMI_COLON                        As String = ";"
Const HYPHEN                            As String = "-"
Const USCORE                            As String = "_"
Const LBRACKET                          As String = "("
Const RBRACKET                          As String = ")"
Const COLON                             As String = ":"
Const POINT                             As String = "."

Const FULL_HYPERLINK                    As Boolean = True
Const VERSE_ONLY_HYPERLINK              As Boolean = False

Const AUTHORITY_PLACEHOLDER             As String = "<authority>"
Const PATH_TO_REFERENCES                As String = "W:\Skydrive\Docs\<authority>.htm#chpt_"

Const LIST_OF_AUTHORITIES               As String = "John, Matthew,Mark , Revelation," _
                                                    & "Genesis, Exodus , Judges "
                                                    
Private authority_ref()                     As String

Sub main()
 
    Dim authorities()                       As String
    Dim authority                           As Variant
    
    authorities = Split(Replace(LIST_OF_AUTHORITIES, SPACE, vbNullString), COMMA)
    authority_ref = Split(NULL_REFERENCE, COMMA)
    For Each authority In authorities                   'authority is a variant but the sub argument is a string hence the cast
        authority_ref(NAME) = CStr(authority)
        insert_authority_hyperlinks
    Next
   
End Sub
   
Sub insert_authority_hyperlinks()
 
    ' The search text means the following
    ' Search terms have been split into a number of terms using ()
    '(<authority>)  find text starting with the reference authority
    '(*) select all characters until the character specified in the next () field
    '(:) include the colon in the selection
    '(*) select all characters until the character specified in the next field is found
    '([ ,-;]) select all characters until we find a space, comma, hyphen or semicolon
   
    Const PRIMARY_SEARCH_TEXT                       As String = "(<authority>)( @)([0-9])(*)(:)(*)([ ,\-;])"
    
    Dim rng                                 As Word.range
    Dim end_of_rng                          As Long
 
    Set rng = ActiveDocument.StoryRanges(wdMainTextStory)
    Do
        With rng.Find
            .Text = Replace(PRIMARY_SEARCH_TEXT, AUTHORITY_PLACEHOLDER, authority_ref(NAME))
            .MatchWildcards = True
            .Forward = True
            .Wrap = wdFindStop
            .Execute
            If .Found Then
               process_single_hyperlink rng, FULL_HYPERLINK
            End If
        End With
    Loop Until Not rng.Find.Found
   
End Sub

Function process_single_hyperlink(rng As Word.range, hyperlink_type As Boolean)

    Const AFTER_A_NUMBER                        As String = " ),.;"
    Const RUN_OF_NUMBERS                        As String = "([0-9]{1,})"

    Dim last_char                               As String
    
    last_char = rng.Characters.Last.Text
    Select Case last_char
    
        Case HYPHEN
            rng.MoveEndUntil cset:=AFTER_A_NUMBER
            authority_ref(NAME) = Split(rng.Text, SPACE)(0)
            authority_ref(CHAPTER) = Split(Split(rng.Text, SPACE)(1), COLON)(0)
            authority_ref(VERSE_1) = Split(Split(rng.Text, COLON)(1), HYPHEN)(0)
            'Just in case you need the end reference
            authority_ref(VERSE_2) = Split(Split(rng.Text, COLON)(1), HYPHEN)(1)
            authority_ref(VERSE_2) = Mid$(authority_ref(VERSE_2), 1, Len(authority_ref(VERSE_2)) - 1)
            insert_hyperlink rng, FULL_HYPERLINK
            If last_char = COMMA Then
                insert_hyperlink rng, VERSE_ONLY_HYPERLINK
            End If
            
        Case COMMA
            rng.MoveEnd unit:=wdCharacter, Count:=-1
            authority_ref(NAME) = Split(rng.Text, SPACE)(0)
            authority_ref(CHAPTER) = Split(Split(rng.Text, SPACE)(1), COLON)(0)
            authority_ref(VERSE_1) = Split(Split(rng.Text, COLON)(1), HYPHEN)(0)
            insert_hyperlink rng, FULL_HYPERLINK
           
            If rng.Characters.Last = "," Then
                rng.Select
                rng.End = ActiveDocument.StoryRanges(wdMainTextStory).End
                rng.Select
                 With rng.Find
                    .Text = RUN_OF_NUMBERS
                    .MatchWildcards = True
                    .Forward = True
                    .Wrap = wdFindStop
                    .Execute
                End With
                rng.MoveEnd unit:=wdCharacter, Count:=1
                process_single_hyperlink rng, VERSE_ONLY_HYPERLINK
            End If
            
        Case SPACE, SEMI_COLON, POINT, RBRACKET
            rng.MoveEnd unit:=wdCharacter, Count:=-1
            If hyperlink_type = FULL_HYPERLINK Then
                authority_ref(NAME) = Split(rng.Text, SPACE)(0)
                authority_ref(CHAPTER) = Split(Split(rng.Text, SPACE)(1), COLON)(0)
                authority_ref(VERSE_1) = Split(Split(rng.Text, COLON)(1), HYPHEN)(0)
            Else
                authority_ref(VERSE_1) = rng.Text
            End If
            insert_hyperlink rng, hyperlink_type
            
        Case Else
            MsgBox "Oops.  We found something that wasn't expected.  The problem text is selected", vbOKOnly
            rng.Select
            Stop
    End Select
    
End Function

Sub insert_hyperlink(reference As Word.range, hyperlink_type As Boolean)
    
    Dim range_len                       As Long
    
    range_len = reference.Characters.Count
    
    ActiveDocument.Hyperlinks.Add _
                Anchor:=reference, _
                Address:= _
                    Replace(PATH_TO_REFERENCES, AUTHORITY_PLACEHOLDER, authority_ref(NAME)) _
                    & authority_ref(CHAPTER) _
                    & "_" _
                    & authority_ref(VERSE_1), _
                TextToDisplay:=IIf(hyperlink_type, reference.Text, authority_ref(VERSE_1))
    reference.MoveStart unit:=wdCharacter, Count:=range_len
    
End Sub
The case statement code in process_single_hyperlink is a bit messy but I don't have time to tidy it further.

The code above has been tested on the text below.

Quote:

(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
(John 1:16-20)
(Mark 3:18-33)
(Matthew 9:89-101)
John was apparently among the first of John the Baptist’s disciples to be introduced to Jesus and one of the first four to be invited to be followers of Christ. (Mark 1:16-20; John 1:35-39) John, who may have been a cousin of Jesus, developed a close friendship with Jesus, becoming known as “the disciple whom Jesus loved.” (John 13:23; John 21:20, 24) John was present at the heartbreaking scene of execution, where Jesus entrusted the care of his mother to John. Also, it was John who outran Peter as they sped to the tomb to investigate the report that Jesus had risen.— John 19:26, 27; John 20:2-4.

the Word: Or “the Logos.” Greek, ho loʹgos. Here used as a title, it is also used at John 1:14 and Revelation 19:13. John identified the one to whom this title belongs, namely, Jesus. This title was applied to Jesus during his prehuman existence as a spirit creature, during his ministry on earth as a perfect man, and after his exaltation to heaven. Jesus was God’s Word of communication, or Spokesman, for conveying information and instructions to the Creator’s other spirit sons and to humans. So it is reasonable to think that prior to Jesus’ coming to earth.—Genesis 16:7-11; Genesis 22:11; Genesis 31:11; Exodus 3:2-5; Judges 2:1-4; Judges 6:11, 12; Judges 13:3. some more text
I've still no idea if the hyperlink fields that are inserted are actually correct hyperlinks.

Good luck.
Reply With Quote
  #12  
Old 10-10-2017, 08:39 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Thankyou so much...I can see the work that has gone into this...I will try tonight on my mega document...enjoy a beer...I would need it after putting that together....
Reply With Quote
  #13  
Old 10-10-2017, 09:14 AM
slaycock slaycock is offline Windows 7 64bit Office 2013
Competent Performer
 
Join Date: Sep 2013
Posts: 117
slaycock is on a distinguished road
Default

When you are developing skills in a language it sometimes quite interesting to have to solve problems over which you have no control or influence (and which are not made up examples out of a text book).

I could spend my time playing Soduku but your problem was much more interesting.

You may get some false or failed hits. This will be where the references don't conform to the rules derived from the reference texts.

Its easy to find the stem of a reference (e.g. John 1:1) but then defining what happens after that depends on your consistency.

The search actually returns 1 more character than the stem reference e.g. 'John 1:1-' and we use the last character to determine what we need to do next.

Last character of found text is
;.) or space then a complete reference has been found
- then we need to find ending verse and assume a following abbreviated reference
, then an abbreviated reference is next

Once you've devined these rules its just a case of encoding the logic (without getting youself in a tangle) whilst avoiding words little foibles.
Reply With Quote
  #14  
Old 10-10-2017, 09:26 AM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

Hi...I have tried to run the VBA in MS Word 2007, but it fails saying...Compile error: Ambiguous name detected: insert_authority_hyperlinks...Any ideas please?

Thanks again...maybe I should do the Soduku!!
Reply With Quote
  #15  
Old 10-10-2017, 12:02 PM
hullsquashboy hullsquashboy is offline Windows 10 Office 2007
Novice
 
Join Date: Oct 2017
Posts: 17
hullsquashboy is on a distinguished road
Default

It works great...in fact brilliant...;-)

Just a couple of things from my testing!! Seems that in the hyperlink it does include a full-stop or a bracket ) sometimes, eg. Books\Titus.htm#chpt_2_3) when the particular ref is in brackets or at the end of a sentence, invalidating the hyperlink.

Also I know that I included about 4 different books ie Matthew, Mark, Luke, John, but there are 66 books so adding them into the part of the script stops it running. So I split it up but cannot get it to fit in the script. Plus for some bizarre reason some refs are left untouched, yet same ref in the same paragrah gets hyperlinked.

Sorry to be a pain, but the work you have already done will save me hours of work!!
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to embed hyperlink to another document into an ActiveX Control Text Box kenboy21 Word VBA 3 02-24-2017 09:41 PM
Macro (or something) to run Spell Check within rich text content boxes in lock document NMBELL Word 8 12-21-2015 04:09 PM
Moving from hyperlink to hyperlink in a document Bengt Word VBA 2 12-21-2015 12:42 AM
Hyperlink: open the document only once, quit & reopen PP, hyperlink doesnt work anymore quanghuynguyenhua PowerPoint 0 10-10-2015 06:17 PM
Macro for word to add page break and specific text to end of document pizzaman1 Word VBA 6 11-14-2014 11:25 PM


All times are GMT -7. The time now is 02:34 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft