How to prevent link formatting from changes when editing link using VBA?

Question

I'm currently using the following code to update all the links in my application:

Sub AddSources()
    Dim pubPage As Page
    Dim pubShape As Shape
    Dim hprlink As Hyperlink
    Dim origAddress() As String
    Dim exportFileName As String
    exportFileName = "TestResume"
    Dim linkSource As String
    linkSource = "TestSource2"

    For Each pubPage In ActiveDocument.Pages
        For Each pubShape In pubPage.Shapes
            If pubShape.Type = pbTextFrame Then
                For Each hprlink In pubShape.TextFrame.TextRange.Hyperlinks
                    If InStr(hprlink.Address, "http://bleaney.ca") > 0 Then
                        origAddress = Split(hprlink.Address, "?source=")
                        hprlink.Address = origAddress(0) + "?source=" + linkSource
                    End If
                Next hprlink
            End If
        Next pubShape
    Next pubPage
    ThisDocument.ExportAsFixedFormat pbFixedFormatTypePDF, "C:\" + exportFileName + ".pdf"
End Sub

The problem is that when I update the links, they lose their formatting. How can I preserve the formatting of the hyperlink? I tried looking at the Copy and Paste methods, but it seems like what I would really need is Paste Special, which doesnt exist on the Range property on the Hyperlink object.

Answer 1

Try adding the following lines to capture the color and underline and then set it back after the address change

Sub AddSources()
    Dim pubPage As Page
    Dim pubShape As Shape
    Dim hprlink As Hyperlink
    Dim origAddress() As String
    Dim exportFileName As String
      Dim undline AS Long
      Dim clr AS Long
    exportFileName = "TestResume"
    Dim linkSource As String
    linkSource = "TestSource2"

    For Each pubPage In ActiveDocument.Pages
        For Each pubShape In pubPage.Shapes
            If pubShape.Type = pbTextFrame Then
                For Each hprlink In pubShape.TextFrame.TextRange.Hyperlinks
                    If InStr(hprlink.Address, "http://bleaney.ca") > 0 Then
                        undline = hprlink.Range.Font.Underline
                        clr = hprlink.Range.Font.Color
                        origAddress = Split(hprlink.Address, "?source=")
                        hprlink.Address = origAddress(0) + "?source=" + linkSource
                        hprlink.Range.Font.Color = clr
                        hprlink.Range.Font.Underline = undline
                    End If
                Next hprlink
            End If
         Next pubShape
    Next pubPage
    ThisDocument.ExportAsFixedFormat pbFixedFormatTypePDF, "C:\" + exportFileName + ".pdf"
End Sub