Microsoft Word - Create Hyperlinks from words, using a reference list

[rG-tom]

Hi all,

I quite often need to have words in a document replaced with hyperlinks, which currently means manually going through to where they are, and adding a hyperlink by copying the URL from a reference table. This is fine if there's ~5 links to do, but some times there can be 50-100+ of these. Ideally, I need to be able to search an entire document, and wherever a word that is in my reference table exists, add the hyperlink from the hyperlink field

ie ref table would be two columns:

WORD | HYPERLINK
wordA | "Z:\folder\folder\wordA.docx"
wordB | "Z:\folder\folder\wordB_rev2.xlsx"

and I'd want "WordA" in the doc to have a hyperlink added to "Z:\folder\folder\wordA.docx", but still retain other formatting and still just say "WordA"


Is there any software that can achieve this, or someone that understands macros a lot better than I able ot point me in the right direction?

Thanks!

 

[rG-tom]

Vba this is really simple in vba. I'm guessing it's for work so this depends on how locked down your office install is.

VBA is fine, I just don't know what VBA to use

I've managed to get a semi-bodge working by getting excel to inject the call parameters and i then copy and paste the bold text from excel into word, and run.

This is slightly clunkier than looking at a list directly, it's still a lot better than manual, but it only works on the main text and footnotes get ignored:

Sub run()

Call FindAndHyperlink1("WordA", "Z:\folder\folder\wordA.docx")
Call FindAndHyperlink1("wordB", "Z:\folder\folder\wordB_rev2.xlsx")



End Sub


Sub FindAndHyperlink1(strsearch As String, straddress As String)
    Dim rngSearch As Range
    Set rngSearch = ActiveDocument.Range
     
    With rngSearch.Find
        Do While .Execute(findText:=strsearch, MatchWholeWord:=False, Forward:=True) = True
            With rngSearch
                ActiveDocument.Hyperlinks.Add Anchor:=rngSearch, Address:=straddress
            End With
            rngSearch.Collapse Direction:=wdCollapseEnd
        Loop
    End With
End Sub

Any ideas what needs changing to get it to look in footnotes? I understand they're a different story range or something and can find plenty of examples of how to change for find and replace, but can't seem to get any of them to work for this.

 

[rG-tom]

Only just in and having some grub but later on tonight I'll build a little demo script. I think I've got what you are trying to do personally I'd probably approach it slightly differently and write all the vba in a word macro rather than starting in excel. Either that or in excel have everything and then a field that is changeable and allows you to select a target document.

I've written a ton of vba that we use in outlook and pulls in from databases all over the show so looking at this it sounds like it should be easy.

Cheers!

The starting data set is in excel, ie the table with the two columns, which given i don't know how to get word to pull data from a spreadsheet, it seemed a logical bodge to get the spreadsheet to give me the VBA

 

[rG-tom]

Ok so you need system.io and some other classes to find, open etc the document. Then read the two ranges (not cells) into an array then loop through the doc comparing every word to the array. Then replace with the corresponding hyperlink in the second array. I think that is what I would do.

That has to be more efficient than looping through the document multiple times. Let me have a look a bit later, I have something pretty similar so i can probably steal a load of the code to make it a bit easier.

Sounds like what i want ideally be able to just point it at an excel format (via the open dialogue) with the format as per here (though without the VBA column obviously )


http://s000.tinyupload.com/?file_id=37169605464810461665

 

[rG-tom]

Thanks, really appreciated!

I will try this now...does it work with footnotes/footers etc or just main body text? That was the big "must have" missing from my own code, it only worked on main body.

 

[rG-tom]

Hey, got it working for body text (inc replacing the row count with some code check the last cell in range), but it doesn't change footnotes i'm afraid. If it helps, 99% of the links i need to add are actually in footnotes, so it wouldn't be the end of the world if this ONLY looked at footnotes (and would probably make it more efficient?).

 

[rG-tom]

Let me add the foot notes into the code. Might need a little bit as I have a dude just arrived to sort out some of my AV.

Cheers!

Just thinking about practical use of this and have changed a few things such as adding an open dialogue to choose where the xlsx file is kept:

Sub ReplaceLinks()

Dim sTXT As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Excel file with data"
        .InitialFileName = Options.DefaultFilePath(wdDocumentsPath)
        .Filters.Clear
 
        .ButtonName = "Select"
        If .Show = -1 Then 'if OK is pressed
            sTXT = .SelectedItems(1)
    

Dim myexcel As Object
Dim myWB As Object
Set myexcel = CreateObject("Excel.Application")
'Set myWB = myexcel.Workbooks.Open(ActiveDocument.Path & "\WordHyperlinkTest1.xlsx")
Set myWB = myexcel.Workbooks.Open(sTXT)
Dim NameIndex As Variant
NameIndex = myWB.Sheets("Data").Range("Names").Value
LinkIndex = myWB.Sheets("Data").Range("Links").Value
Dim lRow As Variant
     'Find the last non-blank cell in column A(1)
    lRow = myWB.Sheets("Data").Range("Names").Rows.Count + 1

Dim wd As Range
Dim itemName As String
Dim wordName As String
Dim linkName As String
Dim lineCount As Integer
indexCount = 1
lineCount = 1
 Do While lineCount < lRow  'Set as number of lines in excel spreadsheet.
      For Each wd In ActiveDocument.Words
            wordName = wd
            itemName = (NameIndex(indexCount, 1))
            linkName = (LinkIndex(indexCount, 1))
            itemName = RemoveWhiteSpace(itemName)
            wordName = RemoveWhiteSpace(wordName)
            If StrComp(wordName, itemName) = 0 Then
            With Selection.Words
              ActiveDocument.Hyperlinks.Add Anchor:=wd, Address:=linkName
            End With
            End If
            Next wd
      lineCount = lineCount + 1
      indexCount = indexCount + 1
    Loop
 
myWB.Close False
Set myexcel = Nothing
Set myWB = Nothing
   Else
            MsgBox ("Cancelled by user")



End If
End With
End Sub

Public Function RemoveWhiteSpace(target As String) As String
    With New RegExp
        .Pattern = "\s"
        .MultiLine = True
        .Global = True
        RemoveWhiteSpace = .Replace(target, vbNullString)
    End With
End Function

Whilst doing this, I started thinking it might actually be way better if this was structured exactly the opposite way...rather than having a macro inside a word file that calls a list from excel, how much work would it be to do the exact opposite, IE have the code inside an excel file that you then point at the word document?

Would this need a complete re-write, or could it be switched around reasonably easily? Sorry for ridiculous scope change, I don't know why I hadn't thought of it this way around as it's the excel file that needs to be specific format where as you should be able to offer this up to any word doc and it should work!

 

[rG-tom]

I'll have a look, shouldn't be much different to be honest. Just as you say the other way around. Not sure on the functionality referencing word from excel but I guess we both might find out. When this engineer leaves I'll have a little look.

Cheers! Getting it working on footnotes is a higher priority for me, and I'm not really getting anywhere with it

 

[rG-tom]

indeed, thank you very much!

Just trying to get it working now. the code works fine when i run it on a document with just just a main body, but it crashes with no error when i run it on one with a footnote The no error bit is infuriating as I have no idea why!

 

[rG-tom]

If you haven't fixed it what I would do is set up a watch on all the variable in that last section, then run to cursor after the endnote section at the top of the section that deals with footnotes. Then run it a line at a time checking the variables make sense. when you say the code is crashing does it crash word entirely or run through and just do nothing?

Sorry been stuck in a meeting! It crashed word entirely, have to force close it

 

[rG-tom]

Very odd. Can you give me the word doc you are using? Or is that a bit cheeky?

I've so far been just creating a new one and adding a footnote...document has one word and a footnote with one word. I can send if you wish but it is literally just that so far!

 

[rG-tom]

both with my code added and just copy and pasted, and also both on my work machine with 2016 and my home machine with Word 365 build 1905.

It is happening after the body text, as i can see it hyperlink the word in the main body first

 

[rG-tom]

Brilliant, thanks again! Definitely owe you a few beers.

 

[rG-tom]

Oh jesus - I totally messed this up didn't I? It's perfect if the words are in order! Should be easy enough to undo my noob mistake. I would have done it on the train but the person beside me decided that nobody needed any peace and quite during the journey. Ill have something for you to play with a little later The reason it was crashing your word is simply because it got stuck in an infinite loop. Bit like life I guess. I will spend a little time with the wife and chill for a bit then ill give it a little poke.

Cheers, and please do this only when you can, I don't need it super urgently

 

[rG-tom]

fantastic, not tried it on a length document yet but this is certainly working on my two word test document! Seems more efficient even at this scale than before too.

Cracking effort! Thank you again.

 

[rG-tom]

Quick one...this seems to ignore words with hyphens and underscores, ie if the word is "ABC_012345" or "XYZ-00034123" it ignores them...can't see anything immediately obvious in the code, any thoughts?

 

[rG-tom]

Interesting. I'll see what it does with both of those and let you know. It's only possible that they are ignored because there isn't a match between words so should be easy enough to work out what it is doing.

Edit: ok so for whatever reason using the words method which is what we do because it is handy setting the anchor range seems to think that "word-1" is actually 3 words "word", "-" and "1", ill have to see if there is a method to set the separator value. So far I can see there are a couple of ways of fixing it if there isn't a way to do that but really that is the best way to do it

Another way using what we already have, we read the document and count the words into the string array called "result" and that actually works correctly giving us the correct details. The problem then is setting the anchor. Let me have a think about it and a play tomorrow. There must be a way of telling it what a word is.

Thanks! Being able to choose the separator would be ideal as it will likely need to be able to ignore commas and colons as delimiters