Change color of hyperlinks in multiple documents

Marrick13 · #1 07-28-2023, 04:43 PM

I am trying to get a macro to change the font color of all hyperlinks in multiple Word documents in a folder. I also want to be able to use RGB colors rather than colorindex colors. I have been using base code posted by Macropod in this forum but the best it does it that it works sometimes. (And in Macropod’s model code was this code to open the files in the selected folder:

Set wdDoc = Documents.Open(FileName:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc

I found that the target docs not only did not ever get updated this way, but the macro also opened other documents as well that are on my PC (but not in the chosen folder). When I added this before ‘With wdDoc’:

Set wdDoc = ActiveDocument

Then the macro opened only the files in the target folder and updated them, but it did not update the files all the time. It seems when it works with one color, I have to close Word after the macro runs, change the color in the code, then run it again. And even then, it only sometimes changes the hyperlink color in the documents. When it works and formats the links, it runs through the 5 test documents in the folder within about 30 seconds. When the macro runs but is not updating the files, it runs much more slowly.

Inconsistency and having to close Word are unacceptable to me. Either it works or it doesn’t.

Any ideas on how to make this code apply any RGB color to all links in documents in the selected folder on demand?

The macro is in the attachment.

gmayor · #2 07-29-2023, 02:54 AM

Hyperlinks are colour formatted by the use of two styles 'Hyperlink' and 'FollowedHyperlink' If you modify the styles to reflect the colours you prefer they will be reflected across the document without attempting to format each link.
The speed issue relates to the time taken to update the links. Have you set the Word option > advanced > general > update automatic links on open?

Marrick13 · #3 07-31-2023, 07:21 AM

Thanks, Graham, but the reason I wanted a macro to do this was to avoid having to open some 200 documents and change their style. I am also working with documents produced by a company that does not use styles. I realize that creating a link in Word automatically assigns it the hyperlink style, but for some reason, when I created test links in a small test sampling of those documents, that style was not assigned to the links (in fact, all of those company documents showed only the 17 styles in the style panel that appear by default in new documents, and 'hyperlink' is not one of them.)

I was hoping to be able to run a macro that opens all Word docs in a folder and set the font color for all their links (preferably using RGB colors), regardless of whether those links have the hyperlink or followed hyperlink or any other style. I even tried adapting the code I included in my first post to apply the hyperlink style to all links in each file the code opens in that folder, but was unable to get that to work, even when I had the code show 'hyperlink' in the styles panel.

I also do not see that "Word option > advanced > general > update automatic links on open" (using Office 365). When I click Word Options, I see "General" and "Advanced", neither one of which has anything like "update automatic links on open".

gmayor · #4 07-31-2023, 07:37 AM

Without access to the documents it is difficult to advise, but if the hyperlinks are not properly inserted then the following should fix them

Code:

With Options
    .UpdateLinksAtOpen = True
    .AutoFormatReplaceHyperlinks = True
End With
ActiveDocument.Range.AutoFormat

Marrick13 · #5 07-31-2023, 03:36 PM

I added that with options code and I didn't see any difference. The hyperlink style was not applied to the links in the test documents. I have attached 2 of those 3 test files along with the latest macro ("Update Document Link Color").

There are other issues with this macro:

1. Sometimes it opens only the files in the selected folder, but mostly it opens those AND other files, possible some on One Drive. But since I select one folder (on my regular desktop), I expect it to look only there.

2. Various attempts to set the font color in the hyperlink style of each document opened by the macro have failed.

3. The macro does not apply the hyperlink style to the links in the document opened by the macro.

I have had success ON OCCASION with numbers 1-3 above, but each time I ran the macro again, it did not do what it is supposed to do. I don't know what else to do with this thing. I've spent hours and hours trying different things (and I am not a VBA developer) and coming up empty. Perhaps you can look at the code and give me some advice. It seems that, since Word is driven by style, it should be fairly easy to create a macro that opens Word documents, sets their hyperlink styles (followed hyperlinks as well), and applies those styles to the links in those documents, saves them and closes them.

gmayor · #6 08-02-2023, 03:25 AM

Your sample documents have manual formatting in the form of pink shading that is at the root of the problem. I would also recommend that you put the code in an add-in template or the normal template rather than a document. The following will remove the formatting from the hyperlinks and adopt the hyperlink styles that are present in the documents.

Code:

Option Explicit

Sub UpdateDocuments3()
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Aug 2023
Dim strFolder As String, strFile As String
Dim wdDoc As Document
Dim oLink As Hyperlink
    strFolder = BrowseForFolder("Select folder containing the documents to process")
    If strFolder = "" Then Exit Sub
    strFile = Dir(strFolder & "*.docx")
    While strFile <> ""
        Set wdDoc = Documents.Open(FileName:=strFolder & strFile, AddToRecentFiles:=False, Visible:=True)
        'Debug.Print strFile
        With wdDoc
            For Each oLink In .Hyperlinks
                oLink.Range.Select
                Selection.ClearFormatting
            Next oLink
            .Close SaveChanges:=True
        End With
        strFile = Dir()
    Wend
    Set wdDoc = Nothing
    MsgBox "finished.", vbOKOnly
End Sub
 
Private Function BrowseForFolder(Optional strTitle As String) As String
'Graham Mayor
'strTitle is the title of the dialog box
Dim fDialog As FileDialog
    On Error GoTo Err_Handler
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo Err_Handler:
        BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92)
    End With
lbl_Exit:
    Exit Function
Err_Handler:
    BrowseForFolder = vbNullString
    Resume lbl_Exit
End Function

Marrick13 · #7 08-02-2023, 03:51 PM

Graham,

Your version works - thank you!

The reason for the "pink shading" was I wanted to redact the test documents before posting here, because they are company files. I replaced the characters with 'x's' instead of blacking them out and enlarged the links and applied the pink shading so I could see them more easily while testing.

Whatever Word macros I work on and which I want to be used globally, I save as macro-enabled templates when finished testing and then store in the Startup folder. From what you said, it seems that some macros don't work properly UNLESS they are stored in an add-in template or the normal template. That is how I tested your version, and it worked even when I added a statement to set the font color of thew target links. But I always thought they should perform as expected in a regular macro-enabled document (.docm). Not true?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA to change multiple change drop down font color and make bold	bwhitlock	Word VBA	1	08-08-2022 12:18 AM
How to change multiple hyperlinks in word 2019	Zakko	Word	5	04-08-2019 08:04 PM
Removing Hyperlinks in Multiple Documents at Once	Jude24Joy	Word VBA	10	06-13-2017 05:38 AM
Change the color of multiple borders	Jim Robison	Excel	2	09-06-2016 11:08 AM
Change Multiple Documents from US to UK language	swifty2010	Word	1	03-08-2011 02:00 PM