Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2011, 09:40 AM
njcloud njcloud is offline Convert html links into embedded hyperlinks Windows 7 64bit Convert html links into embedded hyperlinks Office 2010 64bit
Novice
Convert html links into embedded hyperlinks
 
Join Date: Jun 2011
Posts: 3
njcloud is on a distinguished road
Default Convert html links into embedded hyperlinks

Hello,

I am using mail merge to import large text strings from an Excel document into Word. The final Word document needs to have embedded hyperlinks, however, which presents an interesting challenge.

One cannot use concatenate to create an embedded hyperlink within an excel cell, so I thought I could include the hyperlinks using identifying tags, and then use a macro to process the mail-merged document to convert the tagged links into embedded links.

I am using <a href...></a> tags simply because it's logical to me, but any unique identifier would do. Basically, after the merge I will get a paragraph much like this:

"Savings increase with the size of the network; however, <a href="http://www.hsp-central.net/Power_Study.pdf"> one study </a> demonstrated energy savings between 30 and 60% over conventional computers..."

And I want to turn the <a href> identified hyperlink and enclosed text (in this case "one study") into a clickable hyperlink. I would like to process the entire document to detect <a href="> tags and make the change in each case.



I imagine that the macro would take the text from between the > and </a>, and apply a hyperlink field code to it, using the <a href=" "> address.

I do not know how to do this on my own, or where to start. The most complex macro I have programmed prior to this was to change table background colours.

Any guidance or pointers would be much appreciated. I'm not expecting a fully coded solution (although that would be nice)! I'm sure I will learn a lot from this.

Thank you for your help!
Reply With Quote
  #2  
Old 06-19-2011, 08:50 PM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 64bit Convert html links into embedded hyperlinks Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi njcloud,

For your purposes, you can dispense with the <a href...></a> tags and use Word's HYPERLINK field instead. No macros required.

As you've already discovered, one of the limitations of mailmerging hyperlink addresses is that they’re ordinarily rendered as plain text in the output document. You can overcome that by embedding the MERGEFIELD inside a HYPERLINK field. There is a catch, however; unless you create the HYPERLINK field with a 'Click Here' kind of display text, all the merged fields will display the first link’s address as the HYPERLINK field’s display text.

Here's how to insert a HYPERLINK field with a 'Click Here' prompt into a mailmerge:
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. Run your mailmerge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-20-2011, 08:49 AM
njcloud njcloud is offline Convert html links into embedded hyperlinks Windows 7 64bit Convert html links into embedded hyperlinks Office 2010 64bit
Novice
Convert html links into embedded hyperlinks
 
Join Date: Jun 2011
Posts: 3
njcloud is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-26-2011, 07:36 PM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 64bit Convert html links into embedded hyperlinks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi njcloud,

Try the following:
Code:
Sub ConvertHyperlinks()
Dim RngFld As Range, RngTmp As Range, oFld As Field, StrTmp As String, HLink As Hyperlink
' Turn Off Screen Updating
Application.ScreenUpdating = False
ActiveDocument.ActiveWindow.View.ShowFieldCodes = True
Set RngFld = ActiveDocument.Range
With RngFld
  'Convert HREF codes to {HYPERLINK \1}«\2» format
  With .Find
    .Text = "\<[Aa] href=([!\>]{1,})\>([!\<]{1,})\</a\>"
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Text = "{HYPERLINK \1}«\2»"
    .Forward = True
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
  End With
  'Validate content
  If Len(Replace(.Text, "{", vbNullString)) <> Len(Replace(.Text, "}", vbNullString)) Then
    MsgBox "Unmatched field brace pairs in the document.", vbCritical + vbOKOnly, "Error!"
    Exit Sub
  End If
  If Len(Replace(.Text, "«", vbNullString)) <> Len(Replace(.Text, "»", vbNullString)) Then
    MsgBox "Unmatched 'Text to display' tags in the document.", vbCritical + vbOKOnly, "Error!"
    Exit Sub
  End If
  'Convert Hyperlink fields
  Do While InStr(1, .Text, "{") > 0
    Set RngTmp = ActiveDocument.Range(Start:=.Start + _
        InStr(.Text, "{") - 1, End:=.Start + InStr(.Text, "}"))
    With RngTmp
      Do While Len(Replace(.Text, "{", vbNullString)) <> _
          Len(Replace(.Text, "}", vbNullString))
        .End = .End + 1
        If .Characters.Last.Text <> "}" Then .MoveEndUntil cset:="}", _
          Count:=Len(ActiveDocument.Range(.End, RngFld.End))
      Loop
      .Characters.First = vbNullString
      .Characters.Last = vbNullString
      StrTmp = .Text
      Set oFld = ActiveDocument.Fields.Add(Range:=RngTmp, _
          Type:=wdFieldEmpty, Text:="", PreserveFormatting:=False)
      oFld.Code.Text = StrTmp
    End With
  Loop
  ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
  'update the field display
  .Fields.Update
  'Update the hyperlink 'Text To Display' values
  For Each HLink In .Hyperlinks
    Set RngTmp = HLink.Range
    With RngTmp
      If .Characters.Last.Next = "«" Then
        .MoveEndUntil cset:="»"
        .MoveStartUntil cset:="«"
        .Characters.First.Delete
        .Characters.Last.Next.Delete
        HLink.TextToDisplay = Trim(.Text)
        .Delete
      End If
    End With
  Next
End With
Set RngTmp = Nothing: Set RngFld = Nothing: Set oFld = Nothing
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub
Note: The code does all the Find/Replace work and conversion in one go. As part of the Find/Replace work, the '{ }' pairs are created for the field code, along with some temporary '« »' pairs for the 'Text To Display'. If you use any of these characters elsewhere in the document, some different (unused) characters will be needed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 07-09-2011, 08:33 AM
njcloud njcloud is offline Convert html links into embedded hyperlinks Windows 7 64bit Convert html links into embedded hyperlinks Office 2010 64bit
Novice
Convert html links into embedded hyperlinks
 
Join Date: Jun 2011
Posts: 3
njcloud is on a distinguished road
Default

Wow, this works beautifully. Sorry for the long delay - but thank you Macropod!! I really appreciate all your help.
Reply With Quote
  #6  
Old 04-17-2014, 10:51 PM
Benble Benble is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Novice
 
Join Date: Apr 2014
Posts: 12
Benble is on a distinguished road
Default regarding the topic "Convert html links into embedded hyperlinks"

Hi Macropod!

Finally I found a script that is doing exactly what I am looking for. However I am running word2010 and I have document (xxx.doc) with embedded pictures/drawing and links like "
<a href="http://navigator.od.cssdom.com/qlm/ApplicationArchitectureDiagram/41b70962-78e3-460c-bd5d-f9d30ca6d639.html" target="_blank">Link to diagram in navigator</a>
" in the document that I would like to replace as hyperlinks (and in Italic preferably).

When I run you script I get an error on the line ".Execute Replace:=wdReplaceAll" saying "Run Time Error '5560': "The FIND What text contains a Pattern Match expression which is not valid". I have tried with another text pattern one ".text = "\<[Aa]*href=['""](*)['""]*\>(*)\</[Aa]\>" and that work but I run later into other problem in the code so I guess it must have the correct text pattern. Could the problem be that I have hyperlinks already in the document in terms of embedded pictures. See attached files. I would very much appreciate if you have time to look at my problem.
Kind regards
Benble
run time error 5560.jpg

codesnippet.jpg

part of doc.jpg
Reply With Quote
  #7  
Old 04-17-2014, 11:39 PM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You might need to change:
.Text = "\<[Aa] href=([!\>]{1,})\>([!\<]{1,})\</a\>"
to:
.Text = "\<[Aa] href=([!\>]{1;})\>([!\<]{1;})\</a\>"
or:
.Text = "\<[Aa] href=([!\>]@)\>([!\<]@)\</a\>"

Since there is no document attached to your post (only a picture of one), I can't really do any further diagnosis.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 04-18-2014, 09:26 AM
Benble Benble is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Novice
 
Join Date: Apr 2014
Posts: 12
Benble is on a distinguished road
Default regarding the topic "Convert html links into embedded hyperlinks"

Hi Paul

Many thanks for a prompt reply, really appreciate it.
Now, the code can pass the replace instruction using either of the recommended .Text string, but I get another error saying that this command is not available: Set oFld = ActiveDocument.Fields.Add(Range:=RngTmp, Type:=wdFieldEmpty, text:="", PreserveFormatting:=False) . See attachment. I have also attached the document I am working with. It is a document that has been create from a another program in RTF format. From this program I can call Word 2010 and run i with a few macros to save it as *.doc instead. Then I open it again and use some DDE connection to add some more info. It is when I open it the second time and run the macro you wrote it fails. I really hope you can help me. Please view the field codes in thewhere you can see a number of them and I guess the are comming from the RTF document. I am not a word guru nor a VBA expert so I do not know how to get ride of them via a VBA script....

Kind Regards
Benble
Attached Files
File Type: zip run time error 4605.zip (328.9 KB, 12 views)
Reply With Quote
  #9  
Old 04-18-2014, 03:35 PM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try the following revised code. I've both simplified it and improved its efficiency.
Code:
Sub ConvertHyperlinks()
Dim StrAddr As String, StrDisp As String
' Turn Off Screen Updating
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .Text = "\<[Aa] href=([!\>]@)\>([!\<]@)\</a\>"
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Text = ""
    .Forward = True
    .MatchWildcards = True
    .Wrap = wdFindStop
    .Execute
  End With
  Do While .Find.Found = True
    StrAddr = Replace(Split(Split(.Text, ">")(0), "=")(1), Chr(34), "")
    StrDisp = Split(Split(.Text, ">")(1), "<")(0)
    .Hyperlinks.Add Anchor:=.Duplicate, _
      Address:=StrAddr, TextToDisplay:=StrDisp
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 04-18-2014, 10:24 PM
Benble Benble is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Novice
 
Join Date: Apr 2014
Posts: 12
Benble is on a distinguished road
Thumbs up Regarding teh topic Convert html links into embedded hyperlinks

Hi Paul!
Again many thanks for the prompt answer. You saved my day! It works like a charm. As this was an obstacle for me, I am now able to finalize my work . What a great forum this is.

Kind regards

Benble
Reply With Quote
  #11  
Old 04-18-2014, 10:57 PM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

FWIW, the previous discussion in this thread has been predicated on a mailmerge outputting hyperlink string as <a href...></a> tagged text. The underlying assumption seems to have been that Word can't generate mailmerge hyperlinks with: (a) different targets; (b) different targets from their display text; and/or (c) a different display text for each hyperlink. For a mailmerge outputting its results to a document, neither of the first two assumptions is valid for any Word version and third isn't valid for Word 2007 & later. The assumption may valid be for emails (I haven't tested it) and doesn't apply for merges to print.

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, which is terribly useful when different hyperlink addresses are involved. That may be where the first two assumed limitations come from. 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.
None of the above requires a macro to implement.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 04-19-2014, 03:23 AM
Benble Benble is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Novice
 
Join Date: Apr 2014
Posts: 12
Benble is on a distinguished road
Default Regarding the topic Convert html links into embedded hyperlinks

Hi Paul!

Maybe I was little too quick to say that the proposed functions works for me. I did not actually tested the hyperlinks that where created in the Word document. I was really so happy to see that the code could be executed and the "human-friendly" links turned up. I have been using your first proposal (not the simplified one). I also realized the “target=_blank” is not needed in the HTML link. It was much easier to customize your function (also added ScreenTips):

StrSreenTip = Replace(StrTxt, "Link to ", "Click here to go to ", 1, -1, vbTextCompare)
If StrTxt <> "" Then
.Hyperlinks.Add Anchor:=RngTmp, Address:=StrTmp, TextToDisplay:=StrTxt, ScreenTip:=StrSreenTip, Target:="_blank"
Else
.Hyperlinks.Add Anchor:=RngTmp, Address:=StrTmp, ScreenTip:="Click here to go the Web Publication", Target:="_blank"
End If
.Find.Execute

I am not an VBA and Word expert so this could probably being fixed in a much better VBA coding, but I will use this for now…

Thanks again Paul!
before.jpg

after.jpg

error in hyperlink.jpg

link properties.jpg

Kind Regards

Benble
Reply With Quote
  #13  
Old 04-19-2014, 04:59 AM
macropod's Avatar
macropod macropod is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

With 'target=_blank', I must admit I hadn't appreciated it's relevance. Yes, it's true you can omit that and handle it directly in code, but only if it's always the same. If it's always blank, you don't even need it in the code. If the target varies, however, you'd need to include that in the output and the code would need revision to handle the additional parameter; the same goes for the Screen Tip text.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 04-19-2014, 07:01 AM
Benble Benble is offline Convert html links into embedded hyperlinks Windows 7 32bit Convert html links into embedded hyperlinks Office 2010 32bit
Novice
 
Join Date: Apr 2014
Posts: 12
Benble is on a distinguished road
Default

Hi Paul

Thank you , I will take your suggestion and put it to the code. You are right that I need to change the code in order to cover changes in the ScreenTip and Target but I think it is OK as it is now because it will always be the same.

Happy Eastern.

Regards
Benble
Reply With Quote
  #15  
Old 12-23-2019, 07:45 AM
noslenwerd noslenwerd is offline Convert html links into embedded hyperlinks Windows 10 Convert html links into embedded hyperlinks Office 2016 for Mac
Novice
 
Join Date: Dec 2019
Posts: 15
noslenwerd is on a distinguished road
Default

@ macropod

Example A below parses correctly, while example B does not. It seems that code isn't able to handle the quotation marks around the link.Would it be possible for me to modify the code so it can handle the quotation marks?

Example A:
<a href=https://www.msofficeforums.com/mail-merge/7803-convert-html-links-into-embedded-hyperlinks.html>MS Forums</a>

Example B:
<a href=”https://www.msofficeforums.com/mail-merge/7803-convert-html-links-into-embedded-hyperlinks.html”> MS Forums </a>
Reply With Quote
Reply

Tags
a href, hyperlink, macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert html links into embedded hyperlinks Embedded links spheon Word 1 06-07-2011 04:11 PM
convert html to text at opening etfjr Word 0 12-13-2010 11:14 AM
Can you actually write HTML and CSS in a word document and send it as an html page jackaroo Word 0 07-12-2010 07:49 AM
Word with frames, table of contents, and hyperlinks to html NHMC Word 0 12-09-2009 12:54 PM
Convert a file from HTML to WORD format weblayout view gtselvam Word 0 12-02-2008 03:53 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft