Have Excel 2003 do a Find in a Word document and return the number of occurences

Question

I have an Excel document that has a single column of strings (around 400 rows). I also have a Word document that may or may not have those strings in the Excel document. How can I have Excel do a Find in that Word document for each row in that single column and retrieve the number of times the given string appears? I only have Office 2003.

Thanks!

Answer 1

Here's a sample Excel macro that counts the number of matches and writes it down next to the sought-for strings. I tried it with Office 2007, but it should work with 2003 as well. The macro uses regular expressions, so you need to add a reference to the "Microsoft VBScript Regular Expressions" library to your VBA project (Visual Basic Editor -> Tools -> References).

Sub GetMatchCount()
  Dim Text, i, re

  ' Replace with your Word document name
  Const WordFileName = "C:\Test.doc"

  With CreateObject("Word.Application")
    .Documents.Open (WordFileName)
    Text = .ActiveDocument.Range.Text
    .Quit
  End With

  Set re = New RegExp
  re.Global = True

  With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
      re.Pattern = .Cells(i, 1).Value
      .Cells(i, 2).Value = re.Execute(Text).Count
    Next
  End With
End Sub