We can do that. Do you expect/need the facility for multiple occurrences in each document. It simplifies layout if not required, but no big deal.
We can do that. Do you expect/need the facility for multiple occurrences in each document. It simplifies layout if not required, but no big deal.
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, unfortunetly there may be multple occurences in each document.
So we need to bring the file loop outside the main loop (for each cell) and put it up front when we set up the Word app.
Then in the main loop, we can iterate through the Word app's "Documents" collection (all the open docs) rather than the files.
This is a better approach, provided you don't open up so many large documents you swallow up all the comp's resources.
So the re-ordered code (the declarations are the same)
[VBA]Set fso = New FileSystemObject
Set oTargetFolder = fso.GetFolder(TARGET_FOLDER_PATH)
Set appWD = New Word.Application
For Each f In oTargetFolder.Files
If UCase(Right(f.Name, 3)) = "DOC" Then
appWD.Documents.Open TARGET_FOLDER_PATH & f.Name
End If
Next f
For Each rngPartnumber In Range("partnumbers")
Rw = rngPartnumber.Row
For Each docSource In appWD.Documents
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 = docSource.Name & " Page: " & _
appWD.ActiveWindow.Selection.Information(wdActiveEndPageNumber) _
& " Para: " & Paras
End If
If Paras = Chk Then Exit Do
Chk = Paras
Loop
End With
Next docSource
Next rngPartnumber
appWD.Quit False[/VBA]
K :-)
As a slight departure from the requirement...
I was just going through this code to make a potential kb entry and found a possible enhancement that might be useful.
I did a kb entry a while back that, from Excel, scanned a Word doc for bookmarks - you could then select one and insert a hyperlink to it in the cell.
Well there I was, in the middle of our loop, with a Word range in one hand and an excel cell in the other and thought it was a good opportunity to link the two together.
I've attached an example (unzip to c:\temp and run macro "Main" in the XL workbook)
Any use?
K :-)