Selection.Expand/Extend - I'm stuck again ......

[jagrenet]

Hello Gurus,
I am using VBA within Excel to manipulate a Word Document.
So far everything I have written is working very well but, I have now hit a brick wall. I have a sentence in which I am replacing some text in a specific area which, is simple enough. Find the text, set the Insertion Point, .... replace the text. However, .... the text I intend to replace is largely dynamic and never consistent. It could be one word, three words, eight words.

At this point I have used -
Selection
.StartIsActive = False
.Extend Character:="f" ....... which works pretty well, until I get a word with 2 "f"s on the end of it such as Sheriff.
An example sentence would be something like;
"I am compiling and sending this report to the Something County Sheriff for their review". The "Something County Sheriff" is the dynamic range that I need to select and replace.
Using the code above, the selection is "trying" to get to the "f" in the word "for" but, stops at the first "f" in Sheriff.
If the sentence read - "I am compiling and sending this report to the Local State Police for their review", this code would work as it is intended. What I am searching for is a way to use "the" as my Start point and "for" as my End point but again, ...... being dynamic in the Agency's name, the "Start/End" method will ultimately be unreliable.
I have studied the Start/End Method which uses (Unit:=wdCharacter, Count:=x) however, I have no way of determining "x" at Runtime, due to the dynamic nature.

I have also considered using an Array but again, that won't work either because I don't know exactly where the Selection is going to end every time.
Any suggestions ??
Thanks in advance,
Jeff

 

[jagrenet]

Hi Jeff. The code loops through all of the paragraphs in the document. It ignores blank paragraphs and searches each paragraph for the search text (not case sensitive). The Instr function returns the position of the first letter of the search text in the paragraph string as a number (TInt aka: Temp Integer) from which 1 is subtracted to give the position of the right side of the left chunk of the searched paragraph. The original searched paragraph is replaced by 3 "chunks". The left side of the original paragraph before the search text, the replacement text, and the right side of the original paragraph after the search text. The code only changes the first found search text (Exit For) but could change every instance of the found search text if you remove the "Exit For" line of code. The "SearchText" and corresponding "ReplaceText" can be anything. You just need to develop an entry method for user (Ie. input boxes, userform, etc.) and then assign the input to the "SearchText" and "ReplaceText" variables. Test the code with the search text at the start of a paragraph and with the search text being the last part of a paragraph before distribution. It may need some adjustment as I didn't test it that thoroughly. Dave

Awesome explanation. That is "mostly" .... exactly what I am trying to accomplish. I say mostly because, this program is designed to be completely autonomous in that, I designed it to be totally automated in order to not require any user input or interaction at all. I have a UserForm strictly to show the user that "some" progress is being done (warm and fuzzy). I also already have a variable for the "ReplaceText" .... it's really only that pesky "SearchText" that continues to stand in my way - LOL. I LOVE the principle design that you have offered. Is there a way to enhance it to not "require" input by the user? - By The Way, ..... I did use Debug.Print on "TInt" just to see what that value is ..... it consistently returns 46 so, that's good to know. I also tried to Debug.Print Opara but, it didn't return a value. - Jeff

 

[NdNoviceHlp]

Jeff the TInt consistently returns 46 because the 1st "S" in the SearchText ("Something County Sheriff") is at position 47 in the Test text ("I am compiling and sending this report to the Something County Sheriff for their review."). If you change up the Test text, then the TInt value will be different.

Msgbox Opara.Range.Text

will give you the original paragraph or the replaced paragraph, dependent upon where you place the line of code. You could add the "ReplaceText" to an array within the code and then loop the array. I think I've finally caught on to the unknown "Searchtext" component of your request. It would have been better to set up the Word doc with bookmarks for the values that may change. Perhaps, for each paragraph, you could loop all the ReplaceText array to see if a "fuzzy match" exists which would then identify your Search text. I may be wrong, but it seems that using James006's suggestion would require the opposite "fuzzy match"... you would know the search text but not what replacement text is needed. Either way, it doesn't seem like an exact science. Dave

 

[jagrenet]

Jeff the TInt consistently returns 46 because the 1st "S" in the SearchText ("Something County Sheriff") is at position 47 in the Test text ("I am compiling and sending this report to the Something County Sheriff for their review."). If you change up the Test text, then the TInt value will be different.

Msgbox Opara.Range.Text

will give you the original paragraph or the replaced paragraph, dependent upon where you place the line of code. You could add the "ReplaceText" to an array within the code and then loop the array. I think I've finally caught on to the unknown "Searchtext" component of your request. It would have been better to set up the Word doc with bookmarks for the values that may change. Perhaps, for each paragraph, you could loop all the ReplaceText array to see if a "fuzzy match" exists which would then identify your Search text. I may be wrong, but it seems that using James006's suggestion would require the opposite "fuzzy match"... you would know the search text but not what replacement text is needed. Either way, it doesn't seem like an exact science. Dave

The original Word doc was inhereted many years ago and I saw no real need to "re-invent the wheel" at that point in time. Also, I have been manually manipulating this report for 14 years. This is mainly what spawned the idea to fully automate this process. There are A LOT of moving parts to this humble little program ..... with A LOT of code involved to make it all happen. To be halted by this particular tiny little problem doesn't resonate with me - LOL. I appreciate what TInt is doing ... I was just pointing out that it is very beneficial to know that count - identifying the insertion point. It just dawned on me, .... maybe I can try having the "Application.Selection.Find" look for a different character. Something that none of our Agencies would NOT have at the end of their name. For instance, ...... the "o" in the word "for"; "I am compiling and sending this report to the Something County Sheriff for their review." ..... and then, the code could "back the cursor" to the end of the last word. Along with that, ..... I could use "TInt" to assess the insertion point, then have Selection.Find take me to the "o", then move the cursor back 2 spaces - highlight that area (.select) - plop the variable "ReplaceText" there and move on. Sound viable ???

 

[NdNoviceHlp]

I am compiling and sending this report to the Something County Sheriff for their review... seems like it might not work every time. It's hard to find something when you don't know what you're looking for. Is there any chance that the search text is similar across docs. For example, is it only the "Something County" that needs replacement and the "Sheriff for review" remains the same. Or maybe even if "Sheriff" was always there. It really depends on what the possible search texts and replacement texts are. Dave

 

[jagrenet]

I am compiling and sending this report to the Something County Sheriff for their review... seems like it might not work every time. It's hard to find something when you don't know what you're looking for. Is there any chance that the search text is similar across docs. For example, is it only the "Something County" that needs replacement and the "Sheriff for review" remains the same. Or maybe even if "Sheriff" was always there. It really depends on what the possible search texts and replacement texts are. Dave

Great Point Dave ! - No, the names are not consistent in that, not every Agency is a "Sheriff's" office. Some end with "Police", others with "Department" or "Authority" so, .... the entire name is dynamic. However, ...... I DID figure out a solution !!
The Application.Selection Method has a nice little feature (really a "sub" method) that allows you to utilize "Character" for search criteria, when moving the selection, insertion point or cursor.
With that, I decided to use the period(".") at the end of the sentence as my "aiming point", which worked out perfectly. From there I tell the code to "backup" 3 "words" which, conveniently puts us at the end of the Agency's name ..... every single time. It does this because "for their review" "." - NEVER changes. Keep in mind, ..... while all of this is happening, all of the text remains "highlighted" or selected. So, once you move the cursor back 3 words, it's a simple matter of placing the "ReplaceText" variable (PSAP_Name in my case) neatly in the place of the selected text, ..... and you're done.
Ultimately though ..... I ended up "wiping out" the last portion of the sentence from the beginning of the SearchText - and putting it back afterward.
Here's the code ;

With doc.Application.Selection

.Next(Unit:=wdWord, Count:=3).Collapse <-- Un-highlights the selection but leaves cursor at the beginning of the word (SearchText)
.Next(Unit:=wdWord, Count:=3).Select <-- Selects the next 3 words of "SearchText"
.Extend Character:="." <-- Extends the Selection all the way to the end of the sentence - "."
.Text = PSAP_Name & " " & "for their review." <-- Replaces everything that is highlighted and appends the last 3 words and period.
.Next(Unit:=wdWord, Count:=1).Select

End With

This may not be the most glamorous approach to getting this done but, it works very well and is solid. Doesn't take very much code either.
Can't believe it took me this long to figure out something so simple. I do believe you BOTH (James006 and Dave (NdNoviceHlp)) were instrumental in helping me to think this through. I cannot thank you both enough for jumping in, .... rollin' up your sleeves and tackling this one head on. I appreciate you guys for being my sounding board and helping to hash this thing out !!!!

Cheers !!
Jeff

 

[NdNoviceHlp]

Jeff that's great that you were able to arrive at a solid solution. Hopefully your 14 years of manual toil is over. Happy that we were able to assist you. Thanks for posting your outcome. Have a nice day.

 

[jagrenet]

Jeff that's great that you were able to arrive at a solid solution. Hopefully your 14 years of manual toil is over. Happy that we were able to assist you. Thanks for posting your outcome. Have a nice day.

Thanks Dave !
Good day and God Bless