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

 

[James006]

Hi Jeff,
For a solution or for inspiration ...
Would recommend to take a look at what Greg MAXEY has brilliantly designed:

VBA Find & Replace

VBA Find & Replace — A Word template add-in for using VBA to perform advanced find and replace operations.

gregmaxey.com

 

[jagrenet]

Hi Jeff,
For a solution or for inspiration ...
Would recommend to take a look at what Greg MAXEY has brilliantly designed:

VBA Find & Replace

VBA Find & Replace — A Word template add-in for using VBA to perform advanced find and replace operations.

gregmaxey.com

Thanks for reaching out James and the suggestion. This still does not solve being able to process through the dynamic nature of this problem. If I knew exactly how many words or characters are in the "find" text, this would be a walk in the park.
The design concept is to automate the process of creating Monthly Reports which means, there will be no user input required, .... other than a couple of "button clicks".

 

[James006]

Hi again,

Could you come up with some kind of Unique differentiator element such as Bold or Underline or Italic or Font ... easy to handle and not visually shocking for the document ...

 

[jagrenet]

Hi again,

Could you come up with some kind of Unique differentiator element such as Bold or Underline or Italic or Font ... easy to handle and not visually shocking for the document ...

That is a thought for sure. Hadn't considered that. I may end up investigating that approach, if nobody out there comes up with any other solution. I feel like there MUST be a way to do what I am looking for, I simplly have yet to stumble across it. Thank you for the idea James !!

 

[NdNoviceHlp]

Using the InStr function to locate the word(s) to be replaced would give you the location of the insertion point. Maybe with a bit more string play you could ensure the correct replacement. Something like this would work if you can figure out how to get Word to play nice. HTH. Dave

Sub Test()
Dim TestText As String, SearchText As String, ReplaceText As String, NewStr As String, TInt As Integer
TestText = "I am compiling and sending this report to the Something County Sheriff for their review."
SearchText = "Something County Sheriff"
ReplaceText = "Bob's County Sheriff"

TInt = InStr(1, TestText, SearchText, 1) - 1
NewStr = Left(TestText, TInt) & ReplaceText & Right(TestText, Len(TestText) - TInt - Len(SearchText))
MsgBox NewStr
End Sub

 

[jagrenet]

Using the InStr function to locate the word(s) to be replaced would give you the location of the insertion point. Maybe with a bit more string play you could ensure the correct replacement. Something like this would work if you can figure out how to get Word to play nice. HTH. Dave

Sub Test()
Dim TestText As String, SearchText As String, ReplaceText As String, NewStr As String, TInt As Integer
TestText = "I am compiling and sending this report to the Something County Sheriff for their review."
SearchText = "Something County Sheriff"
ReplaceText = "Bob's County Sheriff"

TInt = InStr(1, TestText, SearchText, 1) - 1
NewStr = Left(TestText, TInt) & ReplaceText & Right(TestText, Len(TestText) - TInt - Len(SearchText))
MsgBox NewStr
End Sub

I had considered using InStr ..... it's been a long time though. I haven't looked into it yet but it may end up being the only viable route to getting this done. I will review what you have sent when I get a little more time and let you know how it goes. Thank you for chiming in !!!

 

[NdNoviceHlp]

I gave this some limited testing and it seemed to work. Adjust the file path to suit. Dave

Sub Test()
Dim SearchText As String, ReplaceText As String, NewStr As String, TInt As Integer
Dim WdApp As Object, Opara As Object, FName as String
'TestText located in document
TestText = "I am compiling and sending this report to the Something County Sheriff for their review."
SearchText = "Something County Sheriff"
ReplaceText = "Bob's County Sheriff"

'change file path to suit
Fname = "C:\TestFolder\Tester.docx"
'open file
Set WdApp = CreateObject("Word.Application")
WdApp.Documents.Open Filename:=Fname
WdApp.Visible = False

For Each Opara In WdApp.ActiveDocument.Paragraphs
'if not blank para
  If Opara.Range.Text <> Chr(13) Then
  If InStr(1, Opara.Range.Text, SearchText, 1) Then
  TInt = InStr(1, Opara.Range.Text, SearchText, 1) - 1
  Opara.Range.Text = Left(Opara.Range.Text, TInt) & ReplaceText & _
                     Right(Opara.Range.Text, Len(Opara.Range.Text) - TInt - Len(SearchText))
  Exit For
  End If
  End If
Next Opara

WdApp.ActiveDocument.Close savechanges:=True
WdApp.Quit
Set WdApp = Nothing
End Sub

 

[jagrenet]

I gave this some limited testing and it seemed to work. Adjust the file path to suit. Dave

Sub Test()
Dim SearchText As String, ReplaceText As String, NewStr As String, TInt As Integer
Dim WdApp As Object, Opara As Object, FName as String
'TestText located in document
TestText = "I am compiling and sending this report to the Something County Sheriff for their review."
SearchText = "Something County Sheriff"
ReplaceText = "Bob's County Sheriff"

'change file path to suit
Fname = "C:\TestFolder\Tester.docx"
'open file
Set WdApp = CreateObject("Word.Application")
WdApp.Documents.Open Filename:=Fname
WdApp.Visible = False

For Each Opara In WdApp.ActiveDocument.Paragraphs
'if not blank para
  If Opara.Range.Text <> Chr(13) Then
  If InStr(1, Opara.Range.Text, SearchText, 1) Then
  TInt = InStr(1, Opara.Range.Text, SearchText, 1) - 1
  Opara.Range.Text = Left(Opara.Range.Text, TInt) & ReplaceText & _
                     Right(Opara.Range.Text, Len(Opara.Range.Text) - TInt - Len(SearchText))
  Exit For
  End If
  End If
Next Opara

WdApp.ActiveDocument.Close savechanges:=True
WdApp.Quit
Set WdApp = Nothing
End Sub

Hi Dave,
I quickly tested your code using your "TestFolder" and "Tester.docx" .... and it works beautifully !!
Would you mind explaining a bit further though - please? Your comments make perfect sense to me but where my ignorance begins to shine is -

1) What is "TInt" counting? I believe it is counting the characters of SearchText. Then moves the cursor to the beginning of that string with "Left" by "TInt" places. Subsequently, it replaces SearchText with ReplaceText ...... and that's where it gets a bit fuzzy for me.

2) There will be 20+ Support Techs using this program nationwide so ... inherently, "SearchText" is not going to be the same for everybody - every time. We support well over 100 Law Enforcement Agencies. If that little caveat has been covered by the code, I am simply not understanding it fully and unsure what string will ultimate, actually occupy SearchText, in the production environment.

Thanks Dave !!!
Jeff

 

[NdNoviceHlp]

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