Excel VBA Macro searching Word document for keyword

Excel4Dummy

New Member
Joined
May 12, 2019
Messages
2
Hello guys,

This is my first post. Please let me know if I should post in a different thread.

I want to run a VBA Makro that searches for a keyword in several Word documents and generates a list of document names when the keyword is found.

- lets say we have 50 word documents, all in one folder. If all file names need to be in the code that would work as well but scanning a complete folder is more elegant.

- Maybe more than a keyword or even a phrase would be nice.

Thank you very much for your help,
Greetings,
Excel4Dummy
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,327
Re: Excel VBA Makro searching Word document for keyword

Try something like:
Code:
Sub GetDocData()
'Note: this code requires a reference to the Word object model.
'See under the VBE's Tools|References.
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, WkSht As Worksheet
Dim strFolder As String, strFile As String, r As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
'Disable any auto macros in the documents being processed
wdApp.WordBasic.DisableAutoMacros
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    With .Range.Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .MatchWholeWord = True
      .MatchCase = True
      .Wrap = wdFindStop
      .Text = WkSht.Cells(1, 1).Text
      .Execute
      If .found = True Then
        r = r + 1
        WkSht.Cells(r, 1) = strFile
      End If
    End With
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = 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 above code assumes the key word or phrase is in cell A1.
 

Excel4Dummy

New Member
Joined
May 12, 2019
Messages
2
Re: Excel VBA Makro searching Word document for keyword

Thank you very much, this looks very good. I managed to adapt it slightly and it works, although I don´t understand everything by now. Will comment on this the next days.

Thank you!
Greetings,
Excel4Dummy
 

Forum statistics

Threads
1,086,246
Messages
5,388,677
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top