Help with display text for URLs in template

[noeyeddeer]

Hi there,



Disclaimer: I know almost nothing about technical computer things or coding! I have been charged with updating a bunch of things to make some of our work processes more efficient. Hopefully i can give the information you need.

Our process:
We produce media reports in MS Word, using templates, and inserting content from XLSX files through a macro. Each set of data (news story) has two different URLs after it(one to a PDF link, one to a text link). Clients don't want 'ugly' URLS - they want to have 'Print Item' or 'Text Link' displayed.

Problem:
It takes a damn long time to do that manually, so I am sure there is a way to automate it.

The templates and macros look pretty complex to my uneducated eyes. This is what we have:

EXTRACT
Selection.Style = ActiveDocument.Styles("DB_Content")
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Summary"
Selection.TypeParagraph

' URL
Selection.Style = ActiveDocument.Styles("DB_Content")
Selection.TypeText Text:="Press Clip: "
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Website_URL"
Selection.TypeParagraph

' TEXT LINK URL
Selection.Style = ActiveDocument.Styles("DB_Content")
Selection.TypeText Text:="Text Link: "
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Text_Link"
Selection.TypeParagraph


Is someone able to help? Sorry for the really vague information...this is my first attempt at this kind of thing.

[macropod]

Links to URLs don't use Mergefields as your code suggests - they use Hyperlinks. Accordingly, you could use a macro like the following to loop through all hyperlinks in a document so you can change (or not) the display text:

Sub Demo()
Dim HLnk As Hyperlink
For Each HLnk In ActiveDocument.Hyperlinks
  With HLnk
    .Range.Select
    Select Case InputBox("Please input the code for the text to display:" & _
      vbCr & vbTab & "1. 'Print Item'" & _
      vbCr & vbTab & "2. 'Text Link'" & _
      vbCr & vbTab & "3. URL", "Update Display Text", "3")
    Case 1: .TextToDisplay = "Print Item"
    Case 2: .TextToDisplay = "Text Link"
    Case Else: .TextToDisplay = .Address
    End Select
  End With
Next
End Sub

An alternative would be to have separate macros for each kind of conversion you want to do, so you can assign each to a different keyboard shortcut. The drawback is that you'd have to manually select each link before changing it.

[noeyeddeer]

Great, thanks! I will give that a go.

[noeyeddeer]

Thanks, with a few little tweaks it's great!

Related - is there any way to get the two links to be displayed as the following:

Print Item / Text Link

instead of the current:

Print Item
Text Link

If not, no worries. Thanks for the help with it!

[macropod]

The only reason the links aren't displaying as you want them is because someone has inserted a paragraph break between them instead of a '/'. Cannot the process that creates the links in the first place manage putting both on the same line with a '/' between them? If not, another macro could be used to handle it after the previous macro has been run.

[noeyeddeer]

Thanks - I am not sure where/how to insert the forward slash? Current code looks like this:

' URL
Selection.Style = ActiveDocument.Styles("DB_Content")
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Website_URL"
Selection.TypeParagraph

' TEXT LINK URL
Selection.Style = ActiveDocument.Styles("DB_Content")
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Text_Link"
Selection.TypeParagraph

[macropod]

It would be helpful if you posted more of the code you're using. It appears you're trying to use code to generate some sort of mailmerge process, but not a very efficiently.

FWIW, it's quite possible for a mailmerge to generate working hyperlinks with both different display text and different hover text.

[noeyeddeer]

Thanks, I will see what I can do. Unfortunately I'm not authorised to actually edit any of it - it looks like a total mess to me. I just have to get results! This is part of a very long set of macros and VBA coding...

(I know...frustrating).

[macropod]

Try replacing the code you posted with:

ActiveWindow.View.ShowFieldCodes = True
With Selection
  ' URL
  ActiveDocument.Hyperlinks.Add Anchor:=.Range, Address:="|", TextToDisplay:="Print Item"
  .Move Unit:=wdCharacter, Count:=-4
  .End = .End + 1
  ActiveDocument.MailMerge.Fields.Add Range:=.Range, Name:="Website_URL"
  .EndKey Unit:=wdLine
  .TypeText " / "
  .EndKey Unit:=wdLine
  ' TEXT LINK URL
  ActiveDocument.Hyperlinks.Add Anchor:=.Range, Address:="|", TextToDisplay:="Text Link"
  .Move Unit:=wdCharacter, Count:=-4
  .End = .End + 1
  ActiveDocument.MailMerge.Fields.Add Range:=.Range, Name:="Text_Link"
End With
ActiveDocument.Fields.Update
ActiveWindow.View.ShowFieldCodes = False

Note: This should also obviate the need for the macro I posted before.

[noeyeddeer]

Oh wow. That is fantastic!!! Thank you!

I bow down.

[noeyeddeer]

Sorry to be a pain, but I'm getting two issues. The first is that the 'press clip' and 'text link' are not displaying the correct style - where can I insert something like:

PHP Code:

Selection.Style = ActiveDocument.Styles("DB_Link") 


Into the code to make it format properly?

The second is that I can't seem to insert a paragraph break between the 'text link' and the 'header' of the next section:

Print Item / Text LinkMagnetite project expands on business' solid industry standing
######, Advertising feature 2, 29/10/14, page 10
Building on...operator for the Iron Bridge joint venture.
Print Item / Text Link'Welfare card key to stemming drug funds' as ice threat grows
######, General News, 29/10/14, page 4
THE drug ice is becoming...was not exempt.
Print Item / Text LinkNot joining China-led bank a mistake: Raby

When it should look like this:


Magnetite project expands on business' solid industry standing
######, Advertising feature 2, 29/10/14, page 10
Building on...operator for the Iron Bridge joint venture.
Print Item / Text Link

'Welfare card key to stemming drug funds' as ice threat grows
######, General News, 29/10/14, page 4
THE drug ice is becoming...was not exempt.
Print Item / Text Link



Here is the full code for this operation:

PHP Code:

Sub InsertMergeFields()
'
'   Inserts the mediaportal data source MergeFields into the document, with formatting
'
'
    
    On Error Resume Next
    
    ' HEADLINE
    Selection.Style = ActiveDocument.Styles("Heading 3")
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Headline"
    Selection.TypeParagraph
        
   
    ' META DATA
    Selection.Style = ActiveDocument.Styles("DB_metadata")
     Selection.Font.Bold = True ' turns bold on
     Selection.Font.Color = RGB(0, 0, 0) = True 'turns black on
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Media_Outlet"
    Selection.TypeText Text:=", "
    
    ' SECTION
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="ProgramSection_Name"
    Selection.TypeText Text:=", "
    
    ' DATE
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Date Date \@ ""dd/MM/yy"""
    
    ' PAGE
       
    ActiveDocument.MailMerge.Fields.AddIf Range:=Selection.Range, MergeField:= _
        "Page_Number", Comparison:=wdMergeIfIsNotBlank, CompareTo:="", _
        TrueAutoText:="MailMergeInsertIf1", TrueText:=", page ", FalseAutoText:= _
        "MailMergeInsertIf2", FalseText:=""
        Selection.Font.Bold = True ' turns bold on
        Selection.Font.Color = RGB(0, 0, 0) = True 'turns black on
        ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Page_Number"
        Selection.Font.Bold = False ' turns bold off
    
    ' AUDIENCE FOR BROADCAST ITEMS
    Selection.TypeText Text:=", audience: "
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Broadcast_Audience_All_People \# ""#,###"""
    
    Selection.TypeText Text:=" (m:"
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Broadcast_Audience_Male \# ""#,###"""
    Selection.TypeText Text:=" / "
    Selection.TypeText Text:="f:"
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Broadcast_Audience_Female \# ""#,###"""
    Selection.TypeText Text:=")"
    
    Selection.TypeParagraph
    
    ' EXTRACT
    Selection.Style = ActiveDocument.Styles("DB_Content")
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Summary"
    Selection.TypeParagraph
    
    ' LINKS
    ActiveWindow.View.ShowFieldCodes = True
With Selection
  ' URL
  ActiveDocument.Hyperlinks.Add Anchor:=.Range, Address:="|", TextToDisplay:="Print Item"
  .Move Unit:=wdCharacter, Count:=-4
  .End = .End + 1
  ActiveDocument.MailMerge.Fields.Add Range:=.Range, Name:="Website_URL"
  .EndKey Unit:=wdLine
  .TypeText " / "
  .EndKey Unit:=wdLine
  ' TEXT LINK URL
  ActiveDocument.Hyperlinks.Add Anchor:=.Range, Address:="|", TextToDisplay:="Text Link"
  .Move Unit:=wdCharacter, Count:=-4
  .End = .End + 1
  ActiveDocument.MailMerge.Fields.Add Range:=.Range, Name:="Text_Link" 


[macropod]

After:
With Selection
insert:
.Style = ActiveDocument.Styles("DB_Content")

[noeyeddeer]

That doesn't seem to make any difference I'm afraid

[macropod]

It works for me, with character and paragraph Styles alike. Indeed, you could even simplify the line to:
.Style = "DB_Content"

Do note that, apart from any paragraph-level attributes applied by your Style (e.g. alignment, space before/after, font name & size), hyperlinks have their own character Styles (basically, blue underlined text), so those will take precedence over your Style. You can, of course, re-apply the Style's font attributes such as bold & italics to the paragraph, so as to re-format the hyperlinks, after applying the Style, using code like:

  With .Paragraphs(1).Range.Font
    .Bold = ActiveDocument.Styles("DB_Content").Font.Bold
    .Italic = ActiveDocument.Styles("DB_Content").Font.Italic
  End With

before the 'End With'.