Selection Find Style not Working 1

[CPForecast]

Hello,

I have a macro that is technically being written for Excel XP, but the part of the program that isn't working involves searching a word document for a particular style.

The program loops through several thousand file paths and names in an excel document. Among other things, the program then takes the path and file name and opens it. From there, the program is supposed to find and copy any "Heading 1" style text. That's the part that seems to be failing. Code below:

Static Function GetWordTitle(path As String, FileName As String) As String

Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open(path + "\" + FileName)
wdApp.Visible = True

wdDoc.Activate

'###################################### 
'FIND TEXT WITH HEADING STYLE NOT WORKING '######################################

With wdApp
    .Selection.Find.ClearFormatting
    .Selection.Find.Style = ActiveDocument.Styles("Heading 1")
    .Selection.Find.ParagraphFormat.Borders.Shadow = False
    With .Selection.Find
        .Text = "This is a test."
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    .Selection.Find.Execute
    .Selection.Copy
End With
'###################################### 
'FIND TEXT WITH HEADING STYLE NOT WORKING '######################################
                
'Copy title from clipboard
Dim MyData   As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
 
 
'Close the document
wdApp.ActiveDocument.Close (wdDoNotSaveChanges)
'Close out of Word
wdApp.Quit (wdDoNotSaveChanges)
                 
                 
GetWordTitle = strClip

End Function

The thing that's killing me is that i can do a

.Selection.TypeText "This is a test."

right at the beginning of the "With" and it'll add it to the beginning of the document just fine.

Any ideas on why this find might not be working would be much appreciated. Thanks!

 

[fumei]

1. Clearly there are parts missing from your posted code. For example, the variable "path"

Set wdDoc = wdApp.Documents.Open([b]path[/b] + "\" + FileName)

is neither declared, nor a value set.

2. You Set an object wdDoc...but never use it. You also do not destroy the object.

3. As you are using Selection.Find, put ALL relevant code within the same block, including the Execute.

 With wdApp.Selection.Find
    .ClearFormatting
    .Style = ActiveDocument.Styles("Heading 1")
    .Text = "This is a test."
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = True
    .Execute
 End With
wdApp.Selection.Copy

Note: setting the Find attributes separately sets those attributes for all subsequent operations. Generally, this is not a good idea. Set attributes for the specific operation.

4. Remove the line:

Selection.Find.ParagraphFormat.Borders.Shadow = False

To see the effect try executing with the line commented out, if you like.

5. It would be better to use Range, rather than Selection. As you are executing from Excel, make sure you declare the Range object as a Word.Range. Otherwise - if you just declare it As Range - VBA will assume it is an Excel Range. Quite a different beast.

Dim r As Word.Range

....

Set wdDoc = wdApp.Documents.Open(path + "\" + FileName)
Set r = wdDoc.Range
With r.Find
   .Style = wdDoc.Styles("Heading 1")
   Do While .Execute(Findtext:= "This is a test.", _
               Forward:=True)=True
      r.Copy
      ' this copies EACH found instance of Findtext
      ' whatever else you are doing
   Loop  ' goes on to the next Findtext
End With

6. Why are you destroying the wdApp each time? If you are going through multiple documents - as it seems you are - then keep the one instance of Word, and use it.

7. I am not quite following the use of your Function. It uses a DataObject to get the string out of the Clipboard. That string came from the Copy of the Selection in the Word file. But that is just a string. If you just need the string, then use the string.

Set wdDoc = wdApp.Documents.Open(path + "\" + FileName)
Set r = wdDoc.Range
With r.Find
   .Style = wdDoc.Styles("Heading 1")
   Do While .Execute(Findtext:= "This is a test.", _
               Forward:=True)=True
      GetWordTitle = r.Text
      '  this puts the string of the found Find as the
      ' value of GetWordTitle
   Loop  ' goes on to the next Findtext
End With

Hope this helps.


faq219-2884

Gerry
My paintings and sculpture

 

[CPForecast]

Problem has been solved at p2p wrox forums! Thank you!

http://p2p.wrox.com/topic.asp?TOPIC_ID=74060

 

[fumei]

Well no actually, it was solved here. See my #4 about removing:

Selection.Find.ParagraphFormat.Borders.Shadow = False

This was posted before the post at the other forum.

As for your issue regarding Set wdApp...it was never mentioned here, and I wonder how you are going to use an instance of Word if you never have an instance.

If you remove (as you say you did):

I have solved the Error 429 by removing:

'Set wdApp = GetObject(, "Word.Application")

how are creating/using an instance of Word??? If you have no wdApp, how are you doing anything at all with Word?


Unless of course you are creating and setting it some other place not mentioned.

Oh well. It seems you have something working for you, and that is good.


faq219-2884

Gerry
My paintings and sculpture

 

[CPForecast]

You're right, fumei. You did post the solution first. Thank you. I have wdApp created as an Object and then do a:

Set wdApp = CreateObject("Word.Application")

and everything seems to work now. Thanks again for your help.

 

[CPForecast]

fumei, using your suggestion above, I have a different sub where I would like to simply replace the first occurence of Heading 1 text with something else. I would need to do something like this, right?

Set wdDoc = wdApp.Documents.Open(path + "\" + FileName)
Set r = wdDoc.Range
With r.Find
   .Style = wdDoc.Styles("Heading 1")
   .Execute(Findtext:= "", _
            ReplaceWith:="SomethingElse", _ 
            Replace:=wdReplaceOneForward:=True)=True
End With

I'm also looking for a good online resource or book that explains how to work with word ranges. Almost everything I see out there has to do with Excel. Thanks in advance.

 

[fumei]

Right. Although...really...you will get better answers if you make yourself very very clear.

1. make sure your r Range variable is declared as a WORD range if you are executing from Excel. Not an Excel range.

Also,

2. I hope your posted code was typed directly into here, NOT copied from your code module...because there are syntax errors. Here is the correct way, IF you just want to replace the first found instance of Heading 1:

With r.Find
   .Style = ActiveDocument.Styles("Heading 1")
   .Execute Findtext:="", ReplaceWith:="Something Else", _
            Replace:=wdReplaceOne, Forward:=True
End With

3. the clarity I mention is this. You do NOT state if you wish to keep the replacement text "SomethingElse" as Heading 1.

If you DO wish to keep it as Heading 1, then no, the code is incorrect. It would have to be:

With r.Find
   .Style = ActiveDocument.Styles("Heading 1")
   [b].Replacement.Style = ActiveDocument.Styles("Heading 1")[/b]
   .Execute Findtext:="", ReplaceWith:="Something Else", _
            Replace:=wdReplaceOne, Forward:=True
End With

Notice that I removed the final True from your

  .Execute(yadda parameters) [b]=True[/b]

There is no need, or use, for it, as you are only doing ONE operation. If there is no Heading 1 found, the code simply moves on. Unless of course you wish to actually know that, in which case I would use the .Found property of .Find instead.

Using a = True on .Execute is VERY handy if you are doing multiple operations on a Range.Find.

I strongly approve of trying to understand the Range object in Word. Once you do, a lot of working within Word becomes much much easier.

Here is one hint. Range.Find literally resizes the range object for each .Found. If .Find actually finds the searched for string, the Range object itself BECOMES that found string.

This is extremely critical if you are performing certain operation on the found string. It does not matter so much in this case - except if you want to keep the Style - as you are only looking for the first .Found.

faq219-2884

Gerry
My paintings and sculpture

 

[CPForecast]

I'm starting to see how using Word ranges could be very helpful. I did want to keep the same style so that second example you posted worked perfectly. Thank you.

Do you know of a good online resource or book that has information on the Word Range object?

 

[fumei]

There is quite a lot even in Help.

faq219-2884

Gerry
My paintings and sculpture

 

[CPForecast]

Ok, and from what I can gather, I can simply reuse that range to do another search and replace, right? I need to do a bunch of hidden text searches after that initial "Heading 1" replace. I can just keep resuing r, right?

 

[fumei]

Whoa, hold on there. That is why I specifically used sentences like:

"Here is the correct way, IF you just want to replace the first found instance of Heading 1:"

" as you are only doing ONE operation."

and others. May I remind you that YOU wrote:

"I would like to simply replace the first occurence of Heading 1 text with something else. "

My bolding.

To answer your question - "I can simply reuse that range to do another search and replace, right? " - the answer is, absolutely YES.

Yes you can, but...you need to re-use r properly. Let me repeat...

Range.Find literally resizes the range object for each .Found. If .Find actually finds the searched for string, the Range object itself BECOMES that found string.

In other words, depending on precisely what you want to do, re-using r (the Range object) can be easy, or it can be a little tricky. Here is an easy:

... other stuff like declaring the
range object, etc.

Set r = wdDoc.Range

'  do your ONE Heading 1 replace
With r.Find
   .Style = ActiveDocument.Styles("Heading 1")
   .Replacement.Style = ActiveDocument.Styles("Heading 1")
   .Execute Findtext:="", ReplaceWith:="Something Else", _
            Replace:=wdReplaceOne, Forward:=True
End With

Set r = ActiveDocument.Range

Voila! The r variable is RESET to be the entire document range again, and you can use r.Find to do whatever operations you want.

One critical aspect of using the Range object is determining whether you need to Collapse it...or not. Often, although it depends on precisely what you are doing, Collapse IS needed. Sometimes though, it is NOT needed. The point being is that if you do need to use Collapse, then...well you do need it. The code will not work unless you do use Collapse. Here, let me show you.

Make a document so it looks like:

The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.

The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.

Hint: type =rand(2,5) - exactly like that - and then press Enter.

Now, let's say you want to replace (and add!!!) some text.

The quick brown fox jumps over the lazy dog.

into

The slow green fox strolls happily around the sleeping dog.

Multiple find/replace, right?

"quick brown" TO "slow green"
"jumps over" TO "strolls happily around"
"lazy" TO "sleeping"

I suggest you actually do this exercise.

Sub BunchOfReplace()
Dim r As Range
Dim StartWith()
Dim EndWith()
Dim j As Long

' fill arrays
StartWith = Array("quick brown", _
   "jumps over", "lazy")
   
EndWith = Array("slow green", _
   "strolls happily around", "sleeping")
 
' set initial Range object
Set r = ActiveDocument.Range
For j = 0 To UBound(StartWith())
   With r.Find
   ' keep replacing until there are
   ' no more StartWith(j) found
      Do While .Execute(Findtext:=StartWith(j), _
            Forward:=True) = True
         ' replace the found StartWith(j)
         ' with EndWith(j)
         r.Text = EndWith(j)
      Loop
   End With
   ' RESET r as the whole document again!
   Set r = ActiveDocument.Range
   ' go through entire doc and do replace
   ' with NEXT StartWith(j)
Next
End Sub

What happens? You get:

The slow green fox strolls happily around the sleeping dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.

It did go through the different multiple text changes - using the arrays - BUT, only once. Why?

Because, in this case, you DO need to use Collapse. Here it is again, with the collapse.

Sub BunchOfReplace()
Dim r As Range
Dim StartWith()
Dim EndWith()
Dim j As Long

' fill arrays
StartWith = Array("quick brown", _
   "jumps over", "lazy")
   
EndWith = Array("slow green", _
   "strolls happily around", "sleeping")
 
' set initial Range object
Set r = ActiveDocument.Range
For j = 0 To UBound(StartWith())
   With r.Find
   ' keep replacing until there are
   ' no more StartWith(j) found
      Do While .Execute(Findtext:=StartWith(j), _
            Forward:=True) = True
         ' replace the found StartWith(j)
         ' with EndWith(j)
         r.Text = EndWith(j)
         [b]r.Collapse Direction:=wdCollapseEnd[/b]
      Loop
   End With
   ' RESET r as the whole document again!
   Set r = ActiveDocument.Range
   ' go through entire doc and do replace
   ' with NEXT StartWith(j)
Next
End Sub

All the replaces are done. Now, just to confuse things...there is in fact a better way for this simple kind of find/replace.

Sub BunchOfReplace()
Dim r As Range
Dim StartWith()
Dim EndWith()
Dim j As Long

' fill arrays
StartWith = Array("quick brown", _
   "jumps over", "lazy")
   
EndWith = Array("slow green", _
   "strolls happily around", "sleeping")
 
' set initial Range object
Set r = ActiveDocument.Range
For j = 0 To UBound(StartWith())
   With r.Find
      .Text = StartWith(j)
      .Replacement.Text = EndWith(j)
      .Execute Replace:=wdReplaceAll
   End With
   ' RESET r as the whole document again!
   Set r = ActiveDocument.Range
   ' go through entire doc and do replace
   ' with NEXT StartWith(j)
Next
End Sub

The above has the exact same effect as the longer code. So, for simple find/replace, use something like it.

I used the longer code to try and show - and perhaps it would be helpful if you stepped through the code so you can actually SEE - that the range object is itself resized. It becomes the found string. AND, you can do anything (anything at all) with it. Collapse it and insert a field after; expand it to the whole paragraph in order to find out what the first word is (and say if the first word is whatever do something else!); move the range (either the Start or the End) until you find a different character, and then do something else...whatever.

Here is a silly example:

Sub Whatever()
Dim r As Range
Dim var
Set r = ActiveDocument.Range
   With r.Find
      Do While .Execute(Findtext:="quick", _
            Forward:=True) = True
         With r
            .Text = "yadda"
            .Collapse 0
         End With
         var = r.MoveEndUntil(Cset:="z")
         If var <> 0 Then
            With r
               .MoveEnd unit:=wdCharacter, Count:=2
               .Collapse 0
               .MoveStart unit:=wdCharacter, Count:=-4
               .Font.Bold = True
               .Collapse 0
            End With
         End If
      Loop
   End With
End Sub

The above - using the same "The quick brown fox...." - replaces "quick" with "yadda", AND hunts forward looking for a "z" (as in "lazy"), and if a "z" is found, moves the range +2 characters (so it includes the "zy" - Cset stops the range just BEFORE the Cset character), then moves its start BACK 4 characters (to get the whole "lazy"), makes THAT range - the range is now "lazy" - collapse....and goes on to the next "quick".

My point being is that for simple find/replace, sure use a simple .Execute Replace:=wdReplaceAll. It works. However, you can do all sorts of OTHER stuff with the range object with a .Find context. It just takes correct use of all the range properties available. But particularly Collapse.

Final note:

r.Collapse Direction:=wdCollapseEnd

can be written as

r.Collapse 0

They are the same. wdCollapseEnd is a constant. However, I would recommend using the text initially. It is clearer...you are collapsing the range to its END.

faq219-2884

Gerry
My paintings and sculpture

 

[CPForecast]

Originally, I was simply doing a single search and replace in several documents. However, as the scope of the project at work evolved over the past few weeks, my task has grown more complex to what it is now. So among other things, I'm now going through documents and replacing several things in each document including titles, dates, and other text in various styles and formatting (hidden, etc.)

Thank you for the very detailed examples. I am going to go through them right now.

It certainly makes sense to use an array and send it through the same replace every time. However, won't the differences in styles and formatting make a difference? (i.e, the first replace I'm doing is a "Heading 1" style and the second replace is a "NameDate" style and also hidden text.)

 

[fumei]

Well, no really. It is all a matter of logic, and writing it out. I strongly recommend that you actually write it, the logic I mean.

Create range object for doc.

Use range.find to find/replace style first found Heading 1 style

Reset range object for doc

Use range.find to find NameDate and do....

So no, you can do whatever you want, essentially. It is only a question of clear concise logic. If you have that solid, the coding part is secondary.



faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

If you have to Search/Replace in headers, footers, ... I suggest you to read the VBA help for NextStoryRange

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CPForecast]

Thanks again fumei. I think I've got enough to get going on this. You have been a terrific help.

And thanks, PHV. I'll be checking out NextStoryRange for sure!

 

[fumei]

While I would never think of truly disagreeing with PHV, I have to say that StoryRange is more of a shotgun approach, IMO. Why, because unless you code carefully using StoryRange goes through all the stories, including EndNotes, Footnotes, yadda yadda.

Further, NextStoryRange only functions by explicit type.

wdEvenPagesHeaderStory, wdPrimaryHeaderStory, wdFirstPageHeaderStory

NextStoryRange actions the next section's story of the same type. So, NextStoryRange of EvenPagesHeaderStory...then you have to change to PrimaryHeaderStory and use NextStoryRange...change to FirstPageHeaderStory and use NextStoryRange.

I am not saying there is no use for NextStoryRange.

However, if you need to use find and replace in the headers/footers, why not simply use Header and Footer objects directly? They are easy enough to use.

Dim r As Range
Dim oHF As HeaderFooter
Dim oSection As Section

For Each oSection In ActiveDocument.Sections()
   For Each oHF In oSection.Headers
      Set r = oHF.Range
         With r.Find
           ' do range.find stuff
   Next
Next

will use Range.Find through all headers, in all Sections.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

Gerry, did you read the help about NextStoryRange ?
It provides an example on how to use the Find.Execute method on the WHOLE document.

 

[fumei]

Ummm, yes PHV, I have read Help. And here is a quote from it...

This example searches each story in the active document

My bolding.

So...I will say it again.

EACH story.

That example will search the Endnotes story, the Footnotes story, the Comments story, etc.

So, yes, true, it searches throught the whole document. But it does it for EVERY story. Which was precisely my point. Your comment was:

"If you have to Search/Replace in headers, footers, ... I suggest you to read the VBA help for NextStoryRange "

Again, my bolding.

And I replied that if you want to search headers and footer, yes, you CAN use NextStoryRange, but it does go through everything, NOT just headers and footers. So if you want to search in headers and footer...why not use....headers/footers?

I will agree this is splitting hairs in a sense, with current fast machines. However, the fact is NextStoryRange either restricts to ONE type (in the first example in Help), or ALL stories (the second example in Help).

To reiterate, if you want to "search in headers and footers", WHY would you use a method that also searches Comments, Endnotes, Footnotes etc. etc.? Or, is restricted to only one type of header, until you change the type?

Of course you could add additional logic to check what story is currently being looked at, and if it is, say, Endnotes, jump to the next story. But that does require you to write that additional logic.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

OK Gerry, my apologies for my poor wording.
I'd have to say something like "If you want to search/replace something in the whole document, not knowing where the pattern could be in, then I suggest you to read the VBA help for NextStoryRange

 

[fumei]

Hey, as I mentioned, I would not think of truly disagreeing with you. And I am not doing so. No apology is needed at all. I will never have your depth of knowledge.

I was just pointing out that if one wants to perform explicit action (say search in headers), it may be better to use explicit objects (say...headers).

faq219-2884

Gerry
My paintings and sculpture