Microsoft Office Forums - View Single Post - VBA code to run macro across all documents in folder

macropod · 04-04-2018, 06:58 PM

Try running the following macro from the document containing your Find/Replace table. The code starts on row 2 (to allow for a header row) and assumes column 2 in the table contains the hyperlinks formatted as you want them to appear in the documents.

Code:

Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, r As Long, strFnd As String
Dim DocTgt As Document, FRDoc As Document, wdDoc As Document, RngStry As Range, RngHlnk As Range
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set FRDoc = ActiveDocument
strDocNm = FRDoc.FullName
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set DocTgt = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With DocTgt
      'Process each row from the F/R Table
      For r = 2 To FRDoc.Tables(1).Rows.Count
        strFnd = Split(FRDoc.Tables(1).Cell(r, 1).Range.Text, vbCr)(0)
        Set RngHlnk = FRDoc.Tables(1).Cell(r, 2).Range.Hyperlinks(1).Range
        For Each RngStry In .StoryRanges
          With RngStry
            With .Find
              .Text = strFnd
              .Execute
            End With
            Do While .Find.Found
              .FormattedText = RngHlnk.FormattedText
              .Collapse wdCollapseEnd
              .Find.Execute
            Loop
          End With
        Next
      Next
      .Close SaveChanges:=True
    End With
  End If
  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