Sharing macro across multiple users/documents

[Mikey]

Setup: Multiple users need the ability to search hundreds of various documents for specific words/terms (at last count there were 22). They would need to open each document, do the search and get the results.



Solution: I created the macro to do the job within a test document.

Question: the macro needs to be run against the content in the other (hundreds) of docs. How do I get the macro into all of the other documents where it doesn't exist?

Systems are a mix of Win 10/11. Word versions are either 2019 or 365.

Thanks!

[macropod]

For some code to process all documents in a selected folder, see:
https://www.msofficeforums.com/45854-post2.html
and, to extend the processing to the selected folder's sub-folders:
https://www.msofficeforums.com/47785-post14.html
There is no need to add the code to more than a single document or template - all you need to do is ensure the document or template is accessible by all who need to use it (e.g. by storing it in a workgroup folder).

Since you haven't said what you're doing with the results of all this processing - or even posted the code you're using - I can't help with that at this stage.

[Mikey]

Thanks, Paul. Appreciate the response.

I work in a training environment where all of our content (storyboards, scripts, transcripts, etc.) is located in Word docs spread across several departments. A recent mandate came down that requires us to search every document for key terms. If one or more is found, the document must be flagged for review.

Documents are spread out across the organization, so searching within a folder won't do. Every doc needs to be opened and searched. Since the docs already exist, I thought a macro would be a quick solution. I can't share the code, but it seems to work fine as far as flagging and highlighting any key term it finds.

The issue I have is that my macro is local to my document. All other existing documents (built using the standard Normal.dot) are in different locations and folders and even on SharePoint sites, won't have the macro. So, if I tell 'Mary' to review the ExampleContent.doc file and search for these key terms, Mary won't find the macro in that doc.

My question is, what is the process to get access to my macro from within the other documents? I can't simply email the macro, and I don't want people going into VBA and pasting code. That would be a nightmare.

Do I have each reviewer update their Normal.dot templates with my macro? Or is it the Normal.dotm template? Or am I out of luck?

[macropod]

At most, you need a single document in a workgroup folder for each department. I wouldn't recommend using Word's Normal template for this.

If you add the following ExtractStats macro & associated code to a docm document containing a list of your key terms with single paragraph breaks between them, then run the macro, it will generate an Excel report giving a frequency count for each key term in each document in the selected folder. And, if the selected folder has subfolders, those will be included in the processing. The document can be stored in any folder, but storing it in a workgroup folder makes it accessible to all users. The output workbook will in any case be output to the user's own Documents folder.

You should be able to see the progress on the Word Status bar as Word opens and processes each document in the background.

Option Explicit
Dim FSO As Object, oFolder As Object, StrFldrs As String, strDocNm As String, FndArray() As String, StrOut As String
 
Sub ExtractStats()
Application.ScreenUpdating = False: Application.DisplayAlerts = wdAlertsNone: Application.WordBasic.DisableAutoMacros True
Dim TopLevelFolder As String, TheFolders As Variant, aFolder As Variant, i As Long
Dim xlApp As Object, xlWkBk As Object, StrTmp As String, r As Long, c As Long, StrXlNm As String
TopLevelFolder = GetFolder: StrFldrs = vbCr & TopLevelFolder: If TopLevelFolder = "" Then Exit Sub
strDocNm = ThisDocument.FullName: FndArray = Split(ThisDocument.Range.Text, vbCr)
StrOut = vbCr & vbTab & "Folder" & vbTab & "Filename" & vbTab & Join(FndArray, vbTab)
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
'Get the sub-folder structure
Set TheFolders = FSO.GetFolder(TopLevelFolder).SubFolders
For Each aFolder In TheFolders
  RecurseWriteFolderName (aFolder)
Next
'Process the documents in each folder
For i = 1 To UBound(Split(StrFldrs, vbCr))
  Call ProcessDocuments(CStr(Split(StrFldrs, vbCr)(i)))
Next
Application.WordBasic.DisableAutoMacros False: Application.DisplayAlerts = wdAlertsAll: Application.ScreenUpdating = True
StrXlNm = "\Documents\KeyWordStats (" & Format(Now, "YYYYMMDDhhmm") & ").xlsx"
Application.StatusBar = "Generating Output Workbook: " & StrXlNm
'Output the results to a new Excel workbook
Set xlApp = CreateObject("Excel.Application")
With xlApp
  .Visible = False
  Set xlWkBk = .Workbooks.Add
  ' Update the workbook.
  With xlWkBk.Worksheets(1)
    For r = 1 To UBound(Split(StrOut, vbCr))
      StrTmp = Split(StrOut, vbCr)(r)
        For c = 1 To UBound(Split(StrTmp, vbTab))
          .Cells(r, c).Value = Split(StrTmp, vbTab)(c)
        Next
    Next
    .Columns.AutoFit: .Rows.AutoFit
  End With
  xlWkBk.SaveAs FileName:="C:\Users\" & Environ("Username") & StrXlNm
  xlWkBk.Close: .Quit: Set xlWkBk = Nothing: Set xlApp = Nothing
End With
MsgBox "Finished. Results in: " & StrXlNm
End Sub
 
Sub RecurseWriteFolderName(aFolder)
Dim SubFolders As Variant, SubFolder As Variant
Set SubFolders = FSO.GetFolder(aFolder).SubFolders: StrFldrs = StrFldrs & vbCr & CStr(aFolder)
On Error Resume Next
For Each SubFolder In SubFolders
  RecurseWriteFolderName (SubFolder)
Next
End Sub

Sub ProcessDocuments(StrFldrNm As String)
Dim StrFlNm As String, wdDoc As Document, x As Long, y As Long
StrFlNm = Dir(StrFldrNm & "\*.doc", vbNormal)
While StrFlNm <> ""
  If StrFldrNm & "\" & StrFlNm <> strDocNm Then
    StrOut = StrOut & vbCr & vbTab & StrFldrNm & vbTab & StrFlNm
    Set wdDoc = Documents.Open(FileName:=StrFldrNm & "\" & StrFlNm, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      On Error Resume Next
      If .ProtectionType = wdAllowOnlyFormFields Then .Unprotect
      On Error GoTo 0
      If .ProtectionType = wdAllowOnlyFormFields Then StrOut = StrOut & vbTab & "Unable to Process - Protected": GoTo Prot
      'Get the stats for each keyword
      For x = 0 To UBound(FndArray) - 1
        With .Range
          With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = FndArray(x)
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindStop
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
          End With
          On Error Resume Next
          Do While .Find.Execute
            y = y + 1
            .Collapse wdCollapseEnd
          Loop
        End With
        StrOut = StrOut & vbTab & y: y = 0
      Next
Prot:
      .Close SaveChanges:=False
    End With
  End If
  DoEvents: StrFlNm = Dir()
Wend
Set wdDoc = Nothing
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

If generating the report is made the responsibility of one person in each department, only that person should need access to the document containing the macro.