Consulting

Results 1 to 6 of 6

Thread: VBA Word search and replace address of url

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Posts
    52
    Location

    VBA Word search and replace address of url

    I have been trying to get the best VBA code for this as regards speed and accuracy. 5000 links to check and change address

    I have tried exposing the code of Hyperlinks by selecting all and keypress Shift+F9. Seemed to work and then I noticed partial replacement of urls.

    So next try was


    Dim doc        As Document
    Dim link, i
    
    
    'Loop through all open documents.
    For Each doc In Application.Documents
        'Loop through all hyperlinks.
        doc.Hyperlinks(i).Address = Replace(doc.Hyperlinks(i).Address, "Gentiles", "Gentile")
        'Other links would be here.
    Next
    Next
    This seems a slow way of replacing... Plus there did not seem to be any wildcards that would work, so if there was a subsequent find and replace for example on the word "Gent" to replace with "Gentleman", "Gentile" would be changed to "Gentlemantile"

    If there is a quicker more robust way of replacing part of the url please help.
    p.s the address is custom like this "tw://[self]?Gentiles" if that makes any difference.

  2. #2
    Try the following which will replace the string strFind with the string strReplace (called from your loop) in both the address and the display text then update the fields.

    Sub ReplaceInLink(strFind As String, strReplace As String)
    Dim oStory As Range
        With Selection
            .HomeKey wdStory
            With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Text = strFind
                .Replacement.Text = strReplace
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = True
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                'replace in address
                ActiveWindow.View.ShowFieldCodes = True
                .Execute Replace:=wdReplaceAll
                'replace in display text
                ActiveWindow.View.ShowFieldCodes = False
                .Execute Replace:=wdReplaceAll
            End With
        End With
        'update the links
        For Each oStory In ActiveDocument.StoryRanges
            oStory.Fields.Update
            If oStory.StoryType <> wdMainTextStory Then
                While Not (oStory.NextStoryRange Is Nothing)
                    Set oStory = oStory.NextStoryRange
                    oStory.Fields.Update
                Wend
            End If
        Next oStory
    lbl_Exit:
        Set oStory = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Mar 2020
    Posts
    52
    Location
    Will try thanks. But note it is only the address that needs to be changed, it is the reason I am having to change the address. The word appears in a sentence with an asterisk at the head. This tells you to go look for the word in a glossary, and it is not always the exact word there as you can imagine.

  4. #4
    VBAX Regular
    Joined
    Mar 2020
    Posts
    52
    Location
    Quote Originally Posted by JPG View Post
    I have been trying to get the best VBA code for this as regards speed and accuracy. 5000 links to check and change address

    I have tried exposing the code of Hyperlinks by selecting all and keypress Shift+F9. Seemed to work and then I noticed partial replacement of urls.

    So next try was


    Dim doc        As Document
    Dim link, i
    
    
    'Loop through all open documents.
    For Each doc In Application.Documents
        'Loop through all hyperlinks.
        doc.Hyperlinks(i).Address = Replace(doc.Hyperlinks(i).Address, "Gentiles", "Gentile")
        'Other links would be here.
    Next
    Next
    This seems a slow way of replacing... Plus there did not seem to be any wildcards that would work, so if there was a subsequent find and replace for example on the word "Gent" to replace with "Gentleman", "Gentile" would be changed to "Gentlemantile"

    If there is a quicker more robust way of replacing part of the url please help.
    p.s the address is custom like this "tw://[self]?Gentiles" if that makes any difference.
    This is the way I need to go I think, because it confines the search to the address only. My only issue with it is I don't know if it is possible to use wildcard settings to the search.
    If anyone can assist on that area I would be grateful.

  5. #5
    If you remove the second
     .Execute Replace:=wdReplaceAll
    it will not change the display text.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Regular
    Joined
    Mar 2020
    Posts
    52
    Location
    the problem then is that if the word I want to replace in the address field exists in normal body text, then that gets replaces also.


    ok this has taken enough of your time and the issue is not solved.

    I will abandon this.

    thank you for your time. Much appreciated.
    Last edited by JPG; 06-11-2020 at 05:05 AM. Reason: Abandoned post

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •