scan word document for list of cities in database

[dodge20]

Is it possible to scan a word document for any words that match a column in a database or excel file?

I have a database of city names. I would like to run a macro that would output all the city names that are within the word document that match the fields in the database.

Is this possible?

Thanks

Dodge20

 

[misterstick]

two ideas:

1. read through the table of names and check against the text of the document.
2. read through the words in the document and check them against the values on the table.

i'd go with number one if there's a lot of text and not many names.
number two would be better if there's not much text and loads of names.

which is it?

mr s. <

 

[dodge20]

2. There isn't much text, but there are thousands of cities in the database. Most of the documents are much longer than 2 pages.

Dodge20

 

[dodge20]

I typed are much longer, but of course meant aren't much longer. Sorry for the confusion.

Dodge20

 

[dodge20]

For any interested here is how it works. The cities are enclosed in quotes and seperated by commas so they can be pasted into a sql query.

Sub findcities()
Dim a As New Collection, xlApp As Object, xlWkb As Object, str1
Dim WorkBookName, SheetName, ColumnNumber
WorkBookName = "C:\cities.xls"
SheetName = "Sheet1"
ColumnNumber = 1
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open(WorkBookName)
For i = 1 To xlWkb.sheets(SheetName).Range("A65536").End(&HFFFFEFBE).Row
str1 = Trim(xlWkb.sheets(SheetName).Cells(i, ColumnNumber).Value)
If ActiveDocument.Content.Find.Execute(FindText:=str1, MatchWholeWord:=True) Then
On Error Resume Next: a.Add str1, CStr(str1): On Error GoTo 0
End If
Next i
xlWkb.Close 0: xlApp.Quit: str1 = ""
Open "C:\Cities.txt" For Output As #1
For i = 1 To a.Count
str1 = str1 & "'" & a.Item(i) & "',"
Next i
Print #1, Left(str1, Len(str1) - 1)
Close #1
Shell "notepad.exe c:\cities.txt", vbMaximizedFocus
End Sub

Dodge20