Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2011
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Word 2010 - Macro to search for wildcards, replace with hyperlink

    Hi guys,

    I'm hoping someone can help me with this. Ordinarily, I can start coding once I've found a few similar things, and then I just sort of merge stuff together and edit it until it does what I want. But none of my searches has turned up anything I can use as a starting point!

    So, I have a number of very large Word documents. These documents contain references to pdf documents in a set format:

    XXX.0000.0000.0000

    Where X is any letter, and 0 is any number. So, for example, we might have CAR.0456.4567.2588.

    Each of the referenced documents are kept in a particular directory, so that their path is always (for example) c:\Documents\xxx.0000.0000.0000.pdf

    What I need the macro to do is:

    1. Search a document to find the first reference
    2. Add a hyperlink to the reference to its path (using the prefix of c:\Documents\ and then adding in the particular reference sequence followed by .pdf)
    3. Repeat for all references in the document

    It seemed to me that this would be relatively easy, but I just can't find anything I can use as a starting point.

    TIA

    C

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 203 Times in 184 Posts
    Try:
    Code:
    Sub AddLinks()
    Application.ScreenUpdating = False
    Dim strFolder As String, strFile As String, wdDoc As Document
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    While strFile <> ""
      Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc.Range
        With .Find
          .ClearFormatting
          .Text = "[A-Z]{3}.[0-9]{4}.[0-9]{4}.[0-9]{4}"
          .Replacement.Text = ""
          .Forward = True
          .Wrap = wdFindStop
          .Format = False
          .MatchWildcards = True
          .Execute
        End With
        Do While .Find.Found
          .Hyperlinks.Add Anchor:=.Duplicate, Address:="C:\Documents\" & .Text & ".PDF", TextToDisplay:=.Text
          .End = .End + 1
          .Collapse wdCollapseEnd
          .Find.Execute
        Loop
      End With
      wdDoc.Close SaveChanges:=True
      strFile = Dir()
    Wend
    Set wdDoc = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
    End Function
    Note: The macro includes its own browser, so all you need do is select the folder to process. Don't save the file containing the above macro in that folder, though.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Lounger
    Join Date
    Dec 2011
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you so much, Paul! That worked a treat.

    And now just for an extra bit: if I wanted to change the wildcard to be "at least 3" for the letters in the reference, how can I edit the code for that? I put a comma after the 3, like in the usual wildcard find and replace (so:
    Code:
    .Text = "[A-Z]{3,}.[0-9]{4}.[0-9]{4}.[0-9]{4}"
    ), but it cut the first letter off the reference (I tested it on a reference that was DELC.0001.0002.0003 and it changed it to ELC.0001.0002.0003).

    I think because it searches through and finds the 4 letter letter ones and changes them, but then loops and finds the already linked 4 letter ones, and reads them as being 3 letter ones with an additional letter, so it links them again. I think I essentially just need to add a bit in to ignore already hyperlinked text. I tried changing the Do loop to this, but it didn't really work (I'm not that great with coding, sorry - still learning!):

    Code:
    Do While (.Find.Found = True)
        If (.Style = ActiveDocument.Styles("Hyperlink")) Then
           .Move Unit:=wdWord, Count:=1
        End If
        .Find.Execute
        If (.Style <> ActiveDocument.Styles("Hyperlink")) Then
          .Hyperlinks.Add Anchor:=.Duplicate, Address:="http://" & .Text & ".PDF", TextToDisplay:=.Text
          .End = .End + 1
          .Collapse wdCollapseEnd
          .Find.Execute
        End If
        Loop
           
      End With
    Thank you!

    C.
    Last edited by caracolesa; 2013-12-16 at 02:27. Reason: clarification

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 203 Times in 184 Posts
    Simply change:
    .Text = "[A-Z]{3}.[0-9]{4}.[0-9]{4}.[0-9]{4}"
    to:
    .Text = "<[A-Z]{3,}.[0-9]{4}.[0-9]{4}.[0-9]{4}"
    The < forces the range to start counting from the beginning of the text-string.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Lounger
    Join Date
    Dec 2011
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you so much, Paul! You're a champion!

Posting Permissions

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