-
Finding text strings in Word from an excel list of text strings
I have an immediate requirement to find text strings in several Word documents from a list of text strings in Excel. I would think VBA could handle this but I'm not sure how to start!
There are 400 cells (single column) of very short text strings (aircraft part numbers) in Excel and I need to search through 10 specific Word documents (each document is about 300 pages) to locate each text string. Once the text string is found (in one or more of the documents) I need the macro to list the Word document page and paragraph number in the Excel columns next to the text string.
Thanks in advance for any help.
-
A few questions
Are all the files in one folder, if so, are there other files in the folder?
Are the text strings "words" in the documents, or are they part of longer strings?
Do you want all the results to be returned on one sheet?
Is any "structure" required for the return?
Can you post a sample of what you would like the return to look like?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thanks for responding...
The files could be placed in one dedicated folder or I could list the file names and path in a spreadsheet column, whichever is easier...
...the strings are words and are not part of a longer string...
... yes, I would like the results on the same spreadsheet row adjacent to the column of the text string..
Here is what I would like the spreadsheet to look like:
Search String Document Page Number Document Paragraph
D145-1112 resulting page # resulting para #
L2233-1 resulting page # resulting para #
-
I'm stuggling with the syntax to run the Word search from Excel. Here's some code that I've got so far. The Word code contained within the asterisks works within Word to return the values you're looking for. The code requires a reference to Microsoft Word.
[vba]
Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\MyNewWordDoc.doc")
' example word operations
With wrdDoc
'**************************
'This section comes from Word and needs adapting
Dim MyPos As Range, Paras As Long, Chk As Long, Pg As Long
With Selection.Find
Do
.ClearFormatting
.Text = "stop"
.Execute
Set MyPos = ActiveDocument.Paragraphs(1).Range
MyPos.SetRange Start:=MyPos.Start, _
End:=Selection.Range.End
Paras = MyPos.Paragraphs.Count
Pg = Selection.Information(wdActiveEndPageNumber)
If Paras = Chk Then Exit Do
Chk = Paras
MsgBox "page " & Pg & " - " & Paras
Loop
End With
'**************************
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
You found the same problem I am having. When running from Excel I can't get the Word Find property to work...although it works fine when run from Word
Any suggestions?
-
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Yes, it seems more difficult than it needs to be...
If you apply Find to a range (like the whole content of a doc), it returns a boolean but modifies the selection to the found string.
If you use the selection object to get the position, it needs to be referred to from the document window.
Well, this seems to work:
(I've used the filesystemobject to go through the files in the folder so you'll need a reference to the Microsoft Sctipting Runtime as well as Microsoft Word. And I've named the range of cells "partnumbers")
[vba]Sub Main()
Const TARGET_FOLDER_PATH As String = "C:\TEMP\"
Dim fso As FileSystemObject
Dim oTargetFolder As Folder
Dim f As File
Dim appWD As Word.Application
Dim docSource As Word.Document
Dim oSearchRange As Word.Range
Dim rngPartnumber As Range
Set fso = New FileSystemObject
Set oTargetFolder = fso.GetFolder(TARGET_FOLDER_PATH)
Set appWD = New Word.Application
For Each rngPartnumber In Range("partnumbers")
For Each f In oTargetFolder.Files
Set docSource = appWD.Documents.Open(TARGET_FOLDER_PATH & f.Name)
Set oSearchRange = docSource.Content
With oSearchRange.Find
.ClearFormatting
.MatchWholeWord = True
.Text = rngPartnumber.Text
If .Execute Then
docSource.Range(docSource.Paragraphs(1).Range.Start, _
oSearchRange.End).Select
rngPartnumber.Offset(0, 1).Value = f.Name & " Page: " & _
appWD.ActiveWindow.Selection.Information(wdActiveEndPageNumber) _
& " Para: " & appWD.ActiveWindow.Selection.Paragraphs.Count
End If
End With
docSource.Close False
Next f
Next rngPartnumber
appWD.Quit False
End Sub[/vba]
Last edited by Killian; 03-01-2006 at 05:12 AM.
Reason: took out an unused variable
K :-)
-
Thanks Killian.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
I always find all that messing around with the selection object a bit strange but I thought I'd better stick with it - I've got a Word template project 'round the corner so I need to get used to strangeness.
K :-)
-
A couple of small adjustments to Killian's code, but which may not be necessary. This will ignore any files without a Doc extension and check for multiple instances of the search string.
Regards
MD
[VBA]
Sub Main()
Const TARGET_FOLDER_PATH As String = "C:\TEMP\"
Dim fso As FileSystemObject
Dim oTargetFolder As Folder
Dim f As File
Dim appWD As Word.Application
Dim docSource As Word.Document
Dim oSearchRange As Word.Range
Dim rngPartnumber As Range
Dim Rw As Long, Paras As Long, Chk As Long
Set fso = New FileSystemObject
Set oTargetFolder = fso.GetFolder(TARGET_FOLDER_PATH)
Set appWD = New Word.Application
For Each rngPartnumber In Range("partnumbers")
Rw = rngPartnumber.Row
For Each f In oTargetFolder.Files
If UCase(Right(f.Name, 3)) = "DOC" Then
Set docSource = appWD.Documents.Open(TARGET_FOLDER_PATH & f.Name)
Set oSearchRange = docSource.Content
With oSearchRange.Find
.ClearFormatting
.MatchWholeWord = True
.Text = rngPartnumber.Text
Do
If .Execute Then
docSource.Range(docSource.Paragraphs(1).Range.Start, _
oSearchRange.End).Select
Paras = appWD.ActiveWindow.Selection.Paragraphs.Count
Cells(Rw, 256).End(xlToLeft).Offset(0, 1).Value = f.Name & " Page: " & _
appWD.ActiveWindow.Selection.Information(wdActiveEndPageNumber) _
& " Para: " & Paras
End If
If Paras = Chk Then Exit Do
Chk = Paras
Loop
End With
docSource.Close False
End If
Next f
Next rngPartnumber
appWD.Quit False
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thanks so much for the help.
I am getting a Run-time error '1004' Method 'Range' of object'_Global' failed
at
For Each rngPartnumber In Range ("partnumbers")
any thoughts?
-
You need to create a Range Name called PartNumbers in your workbook for the list of these.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Making progress... however now it opens the Word document but then shuts down Excel... no error message given.
-
There's nothin in the code that should do this. Can you step through it to determine exactly when this occurs. Just for info, what version of Office/Windows are you using?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
The macro is shutting down in the "With oSearchRange.Find" statement
-
Office 2003
Windows 2000, Service Pack 4
-
The debugging code shows the following for oSearchRange.Find
<-The remote server machine does not exist or is unavailable
Is there a Reference that I need to turn on
-
Sorry, I can't replicate this behaviour.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
OK,
I found this behaviour when the code tries to open a file containing code or with links to other files. Try incorporating the following at "Set oSearchRange" to track down a problem file. I don't get 100% success with this, but it may show a way forward.
[VBA]
On Error Resume Next
Set oSearchRange = docSource.Content
If Err <> 0 Then
MsgBox Err.Description & " - " & f.Name
appWD.Quit False
Exit Sub
End If
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
mdmackillop:
I was able to get the code to work on my laptop. Works great! It apparently has something to do with my desktop's References or the module the code is running from.
When it runs it searches each document for each Rw. Is it possible to change the loops to first go down the list of all Rw's for document 1, then search document 2 for all the Rw's and so on. With 400 Rw's and 10 documents it will open/close 4000 times...which could take some time.
You have been extremely helpful in getting me this far. If you have time to continue helping me that would be great. If not, I understand and thanks so much for your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules