PDA

View Full Version : Reaching into word from excel



jkaufman
10-05-2011, 07:31 AM
I want to open a word document from excel vb and find the number of times a phrase appears in that document and bring that information back into excel.

Thoughts?

Kenneth Hobs
10-05-2011, 08:05 AM
Welcome to the forum!

I normally solve those problems in MSWord first and then adapt that code into Excel. So, step one is to get the count. Try the method at: http://gregmaxey.mvps.org/Count_Text_Occurrences.htm

For step 2, adapt that into Excel. Here are some examples.
'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html

mancubus
10-05-2011, 08:12 AM
hi
here is what i have...
replace FindMe with the string to be searched


Sub FindInWordDoc()

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim fStr As String, fPath As String, fFile As String
Dim cCount As Long, y As Long

fPath = "C:\MyFiles\MyDocs"
fFile = "MyFile.docx"
fFile = fPath & "\" & fFile
fStr = "FindMe"

Set wordApp = CreateObject("Word.Application")
wordApp.Visible = True
Set wordDoc = wordApp.Documents.Open(fFile)
With wordDoc
With .Content.Find
Do While .Execute(FindText:=fStr, Forward:=True, Format:=True, _
MatchWholeWord:=True) = True
y = y + 1
Loop
cCount = y
End With
.Close
End With
wordApp.Quit

Set wordDoc = Nothing
Set wordApp = Nothing

MsgBox cCount

End Sub

jkaufman
10-05-2011, 08:15 AM
Thanks for your quick response. I am getting a "This command is not available" error for "Do While .Execute". Seems like this is a version issue because I have run into this previously. I am using Word 2007.

mancubus
10-05-2011, 08:20 AM
oops...

create a reference to Microsoft Word 12.0 Object Library
VBE / Tools / References...

Kenneth Hobs
10-05-2011, 08:21 AM
Early binding was used so you have to set Reference to Word's Object in Tools > References.

jkaufman
10-05-2011, 08:32 AM
Microsoft Word 12.0 Object Library is checked as referenced.

jkaufman
10-05-2011, 08:34 AM
Mancubus -

I am working with both your suggestion and Mr. Hobs. On your code I am getting "Object Variable or With Block Variable Not Set" regarding "With .Content.Find" is this the same settings problem?

jkaufman
10-05-2011, 08:44 AM
Okay - figured out the "With .Content.Find" issue - back to the setting reference with ".Execute".

By the way - thanks for working with me on this. I have been struggling for awhile and can use the help.