Converting All Plain Text Links to Hyperlinks

[kckay]

I have an Excel workbook that we input data into. once complete I run a macro that performs a mailmerge and some formatting of the text including changing plain text link addresses to blue/underline. This process worked great as we were a hard-copy shop. Due to the coronavirus, we are converting to electronic letter. I need to convert the plain text link to an active and correct link. I was unable to get any code to work in an Excel macro against the Word document, so now call this macro (thank you Techkie007) in the Word template (.dotm):

Sub Hyperlinker()
Dim Rng As Range

Set Rng = ActiveDocument.Range
With Rng.Find
    Do While .Execute(findText:="www.", Forward:=False) = True
    Rng.MoveEndUntil (" ")
        ActiveDocument.Hyperlinks.Add _
            Anchor:=Rng, _
            Address:=Rng.Text, _
            SubAddress:="", _
            ScreenTip:="", _
            TextToDisplay:=""
        Rng.Collapse wdCollapseStart
    Loop
End With

End Sub

This code does convert the plain text link to a hyperlink, but does so in the wrong information. It will place the full path directory of the Word document and append the link address into the hyperlink. For example: incorrect link is C:\this letter directory\wqcdcompliance.com where the correct link should be \wqcdcompliance.com.

How can I modify the code to only use the correct link?

Thank you for your assistance.

I did try:

Sub Hyperlinker()
    Word.Options.AutoFormatReplaceHyperlinks = True
   Selection.Range.AutoFormat
End Sub

It reformatted a couple of headings and a couple of sentences in the body, but the hyperlinks were correct.

 

[kckay]

It runs, but I still end up with file:///x:\wqcdcompliance.

I agree, I am doing something in the code somewhere to cause this. Or there is something in my environment that is causing this issue.

When I leave it as plain text and then use Ribbon->Insert->Hyperlink to do it manually, I get the same result (file:///x:\wqcdcompliance). BUT, interestingly, the window to insert the hypelink is filled with many directories and such junk. I am speculating that the native Word hyperlink code is picking up something in that window (attached) and using that. Would you happen to know how to clear these out?

 

[kckay]

To clear out that rats nest, I had to clear the history from IE.

I added http: to the link. I am thinking back to the DOS days of referencing a file. Without the http: it seems Word thought it was referencing a directory somewhere.

And with rvl01's code (thank you, thank you, THANK YOU!), it appears to be working!

I am doing further testing. Been a frustrating issue, and I really do appreciate your assistance.

 

[Macropod]

This is all so unnecessary!!! See Mailmerge Hyperlink ‘Click Here’ Insertion in the Mailmerge Tips and Tricks thread at:
Mailmerge Tips & Tricks

 

[kckay]

This is all so unnecessary!!! See Mailmerge Hyperlink ‘Click Here’ Insertion in the Mailmerge Tips and Tricks thread at:
Mailmerge Tips & Tricks

I am so confused by that write-up. How do I automate it? If it is a manual process to be done AFTER the letter is generated, then just using the normal Hyperlink ribbon button will be easier. If there is a way to automate it and do use mailmerge, how?

Are you saying to convert all of the simple text fields to mergefields within the template, then use mailmerge to bring in the hyperlinks? Won't that be the same issue I have now in that the hyperlinks don't because they are coming from Excel and are just simple plain text links?

The source text is coming from Excel. How do I create a mergefield in Excel text?

I am really sorry, but am confused. I really appreciate the help and want to learn. This look very promising for multiple users across the network.

 

[Macropod]

I'd have thought the linked content made it obvious it's all done in the mailmerge main document (i.e. before you do the mailmerge). It's a once-off change to the field coding there.

 

[kckay]

How do I create a Mergefield in a text word within an Excel cell?

 

[Macropod]

You don't - as is bleedingly obvious from the link (and as I've already repeated here) - the changes are made in the Word mailmerge main document.

 

[kckay]

Good, thank you.

So I:
1) copy all of the text from the Excel cells (that is just plain text) into the Word template location
2) turn on the Field Codes
3) search the document for the 'plain text links'
4) position the cursor inside of the 'plain text link'
5) replace the 'plain text link' with brackets and HYPERLINK and the correct hyperlink
6) repeat for each 'plain text link'
7) turn off field codes
8) run the mailmerge to convert the 'plain text link' to hyperlinks

 

[Macropod]

It would really help if you paid attention to the instructions in the link...

 

[kckay]

It would really help if you paid attention to the instructions in the link...

I appreciate your Mergefields information. It contains a lot of tips and hints and tricks that I may be able to use in the future.

I am too stupid to understand this and how to implement it with what I have tasked with an automated process:
"Mailmerge Hyperlink ‘Click Here’ Insertion
By default, if you insert a mailmerge field into a hyperlink field, the hyperlinks will all show the first record’s address as the 'Text to display' text. Here's how you can do get a mailmerge to display your preferred default 'Text to display' text instead:
1. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal way, choosing whatever 'Click Here' text you want in the 'Text to display' box.
2. Select the inserted hyperlink and press Shift-F9 to expose its field code.
3. Replace everything in the field after 'HYPERLINK' with your mergefield.
4. Select the field and press F9 to update the display.

In Word 2007 & later, you can make the display text variable also, by following these additional steps:
5. Position the cursor anywhere within the display text.
6. Insert a mergefield pointing to whatever data field you want to use for the display text (this could even be the same field as used at step 3 above).
7. Delete all of the previous display text either side of your last-inserted mergefield (note that this field will likely have updated already).
8. Execute the merge.
9. After merging to a new document, use Ctrl-A, F9 to update all fields. Without this, the mergefield hover text won’t update to the correct targets.

Note: The above is only for merged output sent to a new document; it does not work with merges to email or print. For merges to email, see: https://support.microsoft.com/en-us/kb/912679

Note: Hyperlink fields modified this way are liable to cease functioning once the merge has been executed. Accordingly, it's best to save mailmerge main document before doing the merge and not re-save it afterwards. If you need to make changes to the mailmerge main document, don't make/save them after doing a merge; make/save them beforehand."

I will leave and try to never bother again at all. I will lurk and use this site as a resource and do my best to never ask another question on it.