Find and Replace text with hyperlinks 1

[Dudder]

Hi all!

Brand new here and have been reading about VBA and macros in Microsoft Word. I'm trying to automate finding and replacing site URLs with hyperlinked text.

Problem:
When I import cells from Excel that contain URLs, the URLs import as plain text with angle brackets, for example: <

https://google.com/>

Goal:
Is there a way to find and replace the URL with a hyperlink automatically and then delete the <> symbols?

Any help would be greatly appreciated!

Thanks,
Dudder

 

[macropod]

Word won't do that automatically, but it could be done with a macro. For example:

Sub MakeLinks()
Application.ScreenUpdating = False
With ActiveDocument
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Format = False
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Replacement.Text = ""
      ' hyperlinks
      .Text = "htt[ps]{1,2}://[!^13^t^l ]{1,}"
      .Execute
    End With
    Do While .Find.Found
      If .Characters.First.Previous.Text = "<" Then .Characters.First.Previous.Text = vbNullString
      If .Characters.Last.Next.Text = ">" Then .Characters.Last.Next.Text = vbNullString
      .Hyperlinks.Add .Duplicate, .Text, , , .Text
      .Start = .Hyperlinks(1).Range.End
      .Find.Execute
    Loop
  End With
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Format = False
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Replacement.Text = ""
      ' email addresses
      .Text = "<[0-9A-ÿ.\-]{1,}\@[0-9A-ÿ\-.]{1,}"
      .Execute
    End With
    Do While .Find.Found
      If .Characters.First.Previous.Text = "<" Then .Characters.First.Previous.Text = vbNullString
      If .Characters.Last.Next.Text = ">" Then .Characters.Last.Next.Text = vbNullString
      .Hyperlinks.Add .Duplicate, "mailto:" & .Text, , , .Text
      .Start = .Hyperlinks(1).Range.End
      .Find.Execute
    Loop
  End With
End With
Application.ScreenUpdating = True
End Sub

For PC macro installation & usage instructions, see:

http://www.gmayor.com/installing_macro.htm

For Mac macro installation & usage instructions, see:

https://wordmvp.com/Mac/InstallMacro.html

Cheers
Paul Edstein
[MS MVP - Word]

 

[Dudder]

Hi macropod,

Thanks so much for jumping here and helping! I tried to save and run the macro you've suggested, but am getting a Compile error. Screenshot:

https://www.screencast.com/t/smbeHSqy

I tried following the "PC macro installation & usage instructions" in your signature. I'm not sure what I'm doing wrong. Could you please elaborate on how to setup and run this macro? Would be much appreciated if so.

Thank you!
Dudder

 

[Andrzejek]

Add:

Sub MakeLinks()
Application.ScreenUpdating = False[blue]
With [/blue]ActiveDocument
  With .Range
...

---- Andy

There is a great need for a sarcasm font.

 

[Dudder]

Hi Andrzejek,

Thanks for jumping in this thread as well! I added your suggested code to the top of the Macro. It appears to have executed further, but now has an error elsewhere. Screenshot:

https://www.screencast.com/t/DnrpuFsiDuqQ

When I press "End" the "<" character is deleted but the ">" is not. The URL does then convert to hyperlinked text, but it is failing to open in a browser (I think because of the undeleted ">" on the end).

Any thoughts on why this could be? Really appreciate all the help, thank you both.

Dudder

 

[macropod]

The forum software munged some of the code. Try it now.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Dudder]

Hi macropod,

I tried your edited macro again, but got the same result.

https://www.screencast.com/t/uhPzt6tHC

It seems to not delete the ">" at the end of the text string. So while the text hyperlinks, the link is broken :/
I don't know enough to be able to reverse engineer your macro and see where the issue is.

Any ideas? Really appreciate your help with this!

Thanks,
Dudder

 

[macropod]

Change both instances of:
If .Characters.Last.Next.Text = ">" Then .Characters.Last.Next.Text = vbNullString
to:
If .Characters.Last.Text = ">" Then .Characters.Last.Text = vbNullString

Cheers
Paul Edstein
[MS MVP - Word]

 

[Dudder]

Amazing! It works beautifully! Thank you both so much!
This will save me so much time and tediousness.