Microsoft Office Forums - View Single Post - [Solved] Convert html links into embedded hyperlinks

njcloud · 06-20-2011, 08:49 AM

Thank you for your response, macropod. That would indeed be a good solution in many cases, but I'm afraid it can't be extended to my situation. I think I need to explain it a bit better - basically, I have many cells in excel that contain a paragraph of text each, and within each paragraph are multiple embedded hyperlinks. As they are right now, since excel can't *actually* handle embedded hyperlinks within cell text, I have them surrounded by these <a href...></a> tags. In order to avoid manually formatting each hyperlink in Word after the mail merge, I would like a macro to do it for me in one fell swoop (if possible).

I managed to find some macro code for a find and replace of the <a href> tags, but still could use some insight into how I could extract the web address and apply it to the plain text in between the <a href></a> tags. The below code might be a good start, and I semi-understand it.. I just know this must be possible somehow!

Thanks for your help!

Code:

Dim text As String
   Dim URL As String

   Selection.Find.ClearFormatting
   Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .text = "\<[Aa]*href=['""](*)['""]*\>(*)\</[Aa]\>"
        URL = "\1"
        text = "\2"
        
        .Replacement.text = text
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = True
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll