#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Wow, this works beautifully. Sorry for the long delay - but thank you Macropod!! I really appreciate all your help.
|
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
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] |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
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] |
#10
|
|||
|
|||
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 |
#11
|
||||
|
||||
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] |
#12
|
|||
|
|||
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 |
#13
|
||||
|
||||
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] |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
@ 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> |
Tags |
a href, hyperlink, macro |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |