How to find a string from multiple Word files, which are listed on an Excel spreadsheet? 3

[feipezi]

Hello folks,

Long time no talk. Hope all is well with you!

I have about 100 Word files listed on an Excel spreadsheet. I don't want to go through the pain to search for some strings in each Word doc by opening and closing it if I find nothing.

I have a Word macro to find a particular string but I need to set up a macro to open the Word docs in Excel environment. Here is the starting part of the code but I cannot proceed.

Thanks.

[/b]
Sub tntt()
Dim wddoc As Object
Sheet1.Activate
totrows = Cells(1, 2).End(xlDown).Row
prefixx = "\\HOB2S01\aobusers$\P76212\Judy\"
sufixx = ".docx"
Range(Cells(2, "b"), Cells(totrows, "b")).Select
For Each rw In Selection
wddoc = prefixx & rw & sufixx
'wddoc.Open
Next
End Sub

[/b]

 

[combo]

You need Word application, document and path & file name. The last is a string and you already have in the loop.
With early binding, an example part of the code:

[pre]Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdDocName As String

Set wdApp = New Word.Application
' ...
For Each rw In Selection
wdDocName = prefixx & rw & sufixx
Set wdDoc = wdApp.Documents.Open(wdDocName)
' search wdDoc
wdDoc.Close
Next
wdApp.Quit[/pre]

See .Find.Execute for parameters to match your needs.



combo

 

[Andrzejek]

With late binding, that should get you started:

Sub tntt()
Dim wddoc As Object
Set wddoc = CreateObject("Word.Application")

With wddoc
    .Visible = True
    .Documents.Open ("C:\SomeFolder\WordFile.docx")
End With

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[feipezi]

Thanks for the quick return!

You can see from the attached that I have about 82 files (docx) for me to go through. But they are all on an Excel spreadsheet.

I can set up a FOR loop the loop through the 82 cells on Col B but that's in Excel. How can I link it to Word so your code can be execute and modified?

thanks again.

 

[combo]

Is it the file you intended to share?

The code I posted can be in Excel VBA module. You need a reference to Word library for intellisense and direct use of Word constants. wdDoc is the document currently processed.
If the files can be processed in loop, the missing code between With and End With can do it. If operator action is required, a message box can be added to break the code after successful find.

For me your initial code could have more precise references, working with Selection, active objects and ranges without worksheet/workbook path is hard to debug and may refer to unexpected ranges.


combo

 

[feipezi]

Okay. Sorry that I didn't make myself understood. Let me try again.

What I need is a For loop or While loop to loop thru a whole bunch of Word documents in a folder. And it has nothing to do with Excel. But the macro should be residing in a Word document's VBA editor. I have a small search module to be placed inside the loop. When run the macro, it will search the string I want in each and every document until all the docs are done.

Just a thought. What do you think? Any recommendations?

Thanks in advance.

 

[Andrzejek]

You are providing conflicting information:
>I have about 100 Word files listed on an Excel spreadsheet.
>a whole bunch of Word documents in a folder

Those 2 loops would be different, depending which one you want to do.

>I need to set up a macro to open the Word docs in Excel environment.
>the macro should be residing in a Word document's VBA editor

Where do you want it? Coding will be a little different in each

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Your first few posts strongly indicate that you have a list of Word files in an Excel spreadsheet, and that you wanted code in Excel to loop through those files ("I need to set up a macro to open the Word docs in Excel environment") and search for some strings in each of them.

Now you are saying "I need is a For loop or While loop to loop thru a whole bunch of Word documents in a folder. And it has nothing to do with Excel. The macro should be residing in a Word document's VBA editor"

These are different requirements. Which do you really mean?

Also, when you have found a string, what do you want to do? Are you simply trying to identify which files have the string in them? Or something else?

 

[feipezi]

Hey Combo,

I tried your way of placing the code in an Excel VBA editor and I got some errors shown in the attached. Could you give me more details about your 'reference'?

Thanks again.

 

[feipezi]

Hello Strongm,

Thanks for replying.

I don't really concern the code is in Excel or Word. I guess it's relatively easier to do it in Word because it is the Word documents that I need to find out about.

Here is why I am doing that.
I need to check some contents of one particular document, among 150 of them. Each document has a unique char string. From the name of the document, I am unable to know which document has the contents that interests me. But I do have the char string on hand. If I can find the char string in one of the docs, I will know the document that contains the string is the document I want. Other associates have strings so they can use the macro to trace the document they want. I need to sweep all 150 documents to find that out. If I needed to check a couple of docs, I wouldn't have had to go to this great length to get it, would I? That's all about it.

Here is something I need you guys' help. I copied a macro from the web. But the Activedocument is always the one that contains the macro, not one of the 150 docs I opened and tried to find the string on. Here is a part of the code. Is there any way to make the just-opened document (one of the 150) active document so I can call Findit to check the string.
thanks a lot!

Sub tjt()
Dim objWordApp As Object
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")

strFilePath = "\\HOB2S01\aobusers$\P12345\test\"

strCurFileName = Dir(strFilePath)

Set objFolder = FSO.GetFolder(strFilePath)

For Each objFile In objFolder.Files

Set objWordApp = CreateObject("word.application")
objWordApp.Visible = True

If Right(objFile.Name, 5) = ".docx" Then

Set objWordDoc = objWordApp.Documents.Open(FileName:=objFile.Path, _
ReadOnly:=True, Format:=wdOpenFormatAuto)
[highlight #EF2929]MsgBox ActiveDocument.Name[/highlight]
'Call FindIt

objWordDoc.Close 0, 1
End If

Set objWordDoc = Nothing
objWordApp.Quit
Set objWordApp = Nothing

Next

End Sub

 

[Andrzejek]

You don't have a Reference to Word object.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Thanks, Andy.

@feipezi
You will open Excel (data in range) and Word (search files). So at least once you need to instantiate other application: either Word if the code is in Excel, or Excel if the code is in Word. If in excel, the code can sit in the workbook with file names. For Word, you need other document, as you open other documents. It is up to you what process is more convenient to handle this task.
For me it would be important what next, if one or more searched words are found: process the document, break and inform user, or mark it somewhere?


combo

 

[combo]

A few comments to the code in 8 Mar 23 21:31 post:

1. [tt]Set objWordApp = CreateObject("word.application")[/tt]
It instantiates object with late binding (from other application, no reference to Word). From Word you can work with current application and skip this line. From Excel with reference to Word you can: [tt]Set objWordApp = New Word.Application[/tt].

2. wdOpenFormatAuto in [tt]Set objWordDoc = objWordApp.Documents.Open(FileName:=objFile.Path, ReadOnly:=True, Format:=wdOpenFormatAuto)[/tt]
If the code is in Excel without reference to Word, Excel does not recognize enumerated value and assumes (properly in this case) that wdOpenFormatAuto=0 (or error if you have Option Explicit in module).

3. [tt]MsgBox ActiveDocument.Name[/tt]
Uses Word instance with code first, so the result. You already have document assigned to variable, why not explicit: [tt]MsgBox objWordDoc.Name[/tt] ?

4. [tt]objWordDoc.Close 0, 1[/tt]
With reference to Word library, it can be more readible: [tt]objWordDoc.Close wdDoNotSaveChanges, wdOriginalDocumentFormat[/tt] .

combo

 

[strongm]

>I don't really concern the code is in Excel or Word

Ok, fine. But that doesn't answer where the source of the list of Word documents is. Is it in an Excel spreadsheet ("about 100 Word files listed on an Excel spreadsheet" and "I have about 82 files (docx) for me to go through. But they are all on an Excel spreadsheet) or all in a folder ("I need {} a For loop or While loop to loop thru a whole bunch of Word documents in a folder")?

 

[Andrzejek]

Well, we may never get any straight answer from feipezi...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[feipezi]

Sorry folks that you feel that way.

Yes, all 132 files are on a spreadsheet of Excel but I will read them from a folder, because the filenames on the spreadsheet have no path or folder name. I tried using VBS to read the files and pick the file I need. Please check the code below.

Dim Word
Dim WordDoc
Dim var
Dim docs
Set Word = CreateObject("Word.Application")

'Open the Document
For Each docs in Word.Documents
Set WordDoc = Word.Documents.open("\\HOB2S01\aobusers$\P76212\Judy\" & docs)

NumberOfWords = WordDoc.Sentences.count
For i = 1 to NumberOfWords
If instr(WordDoc.Sentences(i),"ABCDEFG") Then
WScript.Echo WordDoc.Name
End If
Next
'Close Word
'WordDoc.Save
Next
Word.Quit
'Release the object variables
Set WordDoc = Nothing
Set Word = Nothing

 

[combo]

[tt]For Each docs in Word.Documents[/tt]
docs is a document name somewhere in the list (in excel file) With [tt]For Each[/tt] you can loop the list. In the code above you loop through documents open in newly created application, i.e. empty document, so likely no success.

combo

 

[Andrzejek]

I've tried this in Excel VBA, you provide your own [red]text to search[/red]

Option Explicit

Sub feipezi()[green]
'Needs a reference to Microsoft Word XX.X Object Library[/green]
Const MyPath As String = [blue]"\\HOB2S01\aobusers$\P76212\Judy\"[/blue]
Const LookFor As String = [red]"LOVE"[/red]
Dim wdApp As Word.Application
Dim wdDocName As String
Dim B As Boolean

wdDocName = Dir(MyPath)
Set wdApp = New Word.Application

Do While wdDocName <> ""[green]
    'Debug.Print MyPath & wdDocName[/green]
    wdApp.Documents.Open (MyPath & wdDocName)
    
    With wdApp
        .Selection.WholeStory
        With .Selection.Find
            .ClearFormatting
            .MatchWholeWord = True
            .MatchCase = False
            B = .Execute(FindText:=LookFor)
            If B Then MsgBox "Found " & LookFor & " in " & MyPath & wdDocName
        End With
    End With
    
    wdApp.Documents.Close
    wdApp.Visible = False
    wdDocName = Dir
Loop

wdApp.Quit
Set wdApp = Nothing

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson