×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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]
[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][b][/b]

[/b]

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

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:

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 

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

combo

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

With late binding, that should get you started:

CODE

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

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

(OP)
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.

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

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

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

(OP)
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.

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

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

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

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?

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

(OP)
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)
MsgBox ActiveDocument.Name
'Call FindIt

objWordDoc.Close 0, 1
End If

Set objWordDoc = Nothing
objWordApp.Quit
Set objWordApp = Nothing

Next

End Sub

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

You don't have a Reference to Word object.

---- Andy

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

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

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

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

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

1. Set objWordApp = CreateObject("word.application")
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: Set objWordApp = New Word.Application.

2. wdOpenFormatAuto in Set objWordDoc = objWordApp.Documents.Open(FileName:=objFile.Path, ReadOnly:=True, Format:=wdOpenFormatAuto)
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. MsgBox ActiveDocument.Name
Uses Word instance with code first, so the result. You already have document assigned to variable, why not explicit: MsgBox objWordDoc.Name ?

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

combo

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

>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")?

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

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

---- Andy

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

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

(OP)
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

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

For Each docs in Word.Documents
docs is a document name somewhere in the list (in excel file) With For Each 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

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

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

CODE

Option Explicit

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

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

Do While wdDocName <> ""
    'Debug.Print MyPath & wdDocName
    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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close