問題描述
讓 Excel 2003 在 Word 文檔中進行查找並返回出現次數 (Have Excel 2003 do a Find in a Word document and return the number of occurences)
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!
‑‑‑‑‑
參考解法
方法 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
(by Dan Appleyard、Helen)