Selecting a Range in Word from Excel VBA

[japers]

Please bear with me everyone, it may look like a Word question but it is really to do with Excel so please read on! Basically, I have a series of complex text forms which simply cannot be adjusted in Word. I have therefore prepared them in Excel where they can be manipulated.

I have made a VBA macro which then copies the cells into Word, converts the tables to text, formats them, etc. My problem is that I need to select a range of text between two keywords and format them. This section can change in length so using fixed line ranges in VBA will not work.

I have found the following code which works perfectly within Word. My problem is that I cannot adapt this same code to work when it is running from within Excel's VBA and the whole aim is of course to have one file/macro to do everything!

Below is working demonstration code within Word. The first 2 lines type "Hello and Goodbye" for you and the remaining 'real' code selects everything between Hello & Goodbye, ie " and ". (in my real case there are up to several pages between both keywords)

Sub SelectRangeBetween()

' Types the text
Selection.HomeKey Unit:=wdStory
Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
    With Selection.Find
        .Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
        Set myrange = Selection.Range
        myrange.End = ActiveDocument.Range.End
        myrange.Start = myrange.Start + 5
        myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
        myrange.Select
    End With
End Sub

Now for the Excel conversion part! I have adapted the script to the best of my ability below:

Sub SelectRangeBetween()

Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")

With wrdApp
.Documents.Add
    .Visible = True

' Types the text
.Selection.HomeKey Unit:=wdStory
.Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
.Selection.HomeKey wdStory
.Selection.Find.ClearFormatting

   With .Selection.Find
      .Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
      Set myrange = Selection.Range
 ' Problem is here:
      myrange.End = wrdApp.ActiveDocument.Range.End
      myrange.Start = myrange.Start + 5
      myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
      myrange.Select
   End With

End With
End Sub

Any help very gratefully received!

 

[aamailhot]

See: Use early binding and late binding in Automation - Office | Microsoft Docs
According to Microsoft (and who am I to argue):

Aha, I think we have the answer to both our 'positions' on the link you gave; it is definitely a good detailed reference.

Namely, for my details I have provided regarding late binding for multiple MS Office versions, please see the section on that page entitled 'Maintaining compatibility across multiple versions'. The specific paragraph that seems to have some good guidance therein is as follows:

Microsoft Office applications provide a good example of such COM servers. Office applications will typically expand their interfaces to add new functionality or correct previous shortcomings between versions. If you need to automate an Office application, it is recommended that you early bind to the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Excel 95, Excel 97, Excel 2000, and Excel 2002, you should use the type library for Excel 95 (XL5en32.olb) to maintain compatibility with all three versions.

I.e. in my case, presumably I developed the (early-bound) code in a later version of MS Office whereas it then failed on an earlier version (i.e. it was infeasible for me to be aware of all MS Office versions in use at that time). I am also unsure how I would've gone about early-binding the macro to a (hypothetically known) older version of MS Office on my computer anyway; presumably this is something I could figure out with a bit of research though...

And just to push back a little regarding execution speed: I have rarely had customers get 'hung up' on the time it takes to run even relatively-complex late-bound macros (talking 10+ minutes). Certainly a good thing to inform them of up front for 1+ minute execution potential, and/or have some real-time 'progress' feedback...but on the whole I simply call this an acceptable 'coffee break' mentality . Certainly, a macro that has degraded performance is far-superior to a macro that doesn't run at all.

At the end of the day, to attempt to wrap up, I am hopeful we are reaching some consensus here to the effect of:

1) Early-binding (EB) is in general recommended. Easier development, better security, faster execution, etc.
a) Should you be writing code just for your machine, or have 'high confidence' in all the machines/environments it'll be ran on, use EB. See above discussion if running the code in multiple environments.

2) Should you not have sufficiently high-confidence on all the environments your code may run on (namely if it could be ran on older environments, or you just need to assure your macro 'always' runs), then Late-Binding is worth considering.

'Thoughts?

 

[Niko1295]

Please bear with me everyone, it may look like a Word question but it is really to do with Excel so please read on! Basically, I have a series of complex text forms which simply cannot be adjusted in Word. I have therefore prepared them in Excel where they can be manipulated.

I have made a VBA macro which then copies the cells into Word, converts the tables to text, formats them, etc. My problem is that I need to select a range of text between two keywords and format them. This section can change in length so using fixed line ranges in VBA will not work.

I have found the following code which works perfectly within Word. My problem is that I cannot adapt this same code to work when it is running from within Excel's VBA and the whole aim is of course to have one file/macro to do everything!

Below is working demonstration code within Word. The first 2 lines type "Hello and Goodbye" for you and the remaining 'real' code selects everything between Hello & Goodbye, ie " and ". (in my real case there are up to several pages between both keywords)

Sub SelectRangeBetween()

' Types the text
Selection.HomeKey Unit:=wdStory
Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
    With Selection.Find
        .Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
        Set myrange = Selection.Range
        myrange.End = ActiveDocument.Range.End
        myrange.Start = myrange.Start + 5
        myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
        myrange.Select
    End With
End Sub

Now for the Excel conversion part! I have adapted the script to the best of my ability below:

Sub SelectRangeBetween()

Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")

With wrdApp
.Documents.Add
    .Visible = True

' Types the text
.Selection.HomeKey Unit:=wdStory
.Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
.Selection.HomeKey wdStory
.Selection.Find.ClearFormatting

   With .Selection.Find
      .Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
      Set myrange = Selection.Range
 ' Problem is here:
      myrange.End = wrdApp.ActiveDocument.Range.End
      myrange.Start = myrange.Start + 5
      myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
      myrange.Select
   End With

End With
End Sub

Any help very gratefully received!

Just replying to show my solution for this. I was looking to selecting a range between two keywords inside of a word doc and apply bulletpoints to that section.

Code:

Dim wordstart1 As Word.Range
Dim wordend1 As Word.Range
Dim wordrange1 As Word.Range
Set wordstart1 = newDoc.Range
Set wordend1 = newDoc.Range
Set wordrange1 = newDoc.Range

'First round of finding the specified words that we are looking for in the active document
wordstart1.Find.Execute "Minimum Qualifications" '***finds a specfic word

wordend1.Find.Execute "Preferred Qualifications" '***finds a specfic word

wordrange1.SetRange Start:=wordstart1.End + 3, End:=wordend1.Start - 1 'sets the range equal to end of first word +3 spaces and the start of the second word minus 1 space

wordrange1.Select

wordrange1.ListFormat.ApplyBulletDefault 'apply bullet points to all lines within the selected range

 

[Niko1295]

Just replying to show my solution for this. I was looking to selecting a range between two keywords inside of a word doc and apply bulletpoints to that section.

Code:

Dim wordstart1 As Word.Range
Dim wordend1 As Word.Range
Dim wordrange1 As Word.Range
Set wordstart1 = newDoc.Range
Set wordend1 = newDoc.Range
Set wordrange1 = newDoc.Range

'First round of finding the specified words that we are looking for in the active document
wordstart1.Find.Execute "Minimum Qualifications" '***finds a specfic word

wordend1.Find.Execute "Preferred Qualifications" '***finds a specfic word

wordrange1.SetRange Start:=wordstart1.End + 3, End:=wordend1.Start - 1 'sets the range equal to end of first word +3 spaces and the start of the second word minus 1 space

wordrange1.Select

wordrange1.ListFormat.ApplyBulletDefault 'apply bullet points to all lines within the selected range

Sorry i did forget to mention that i have dimmed "new.Doc further up in my macro. So my macro looks something like this

Sub Bulletpointsection()
    
    Dim wdApp       As Word.Application
    Dim newDoc      As Word.Document
    
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    Set newDoc = wdApp.Documents.Add(filepath)        ' adjust to be your specific file that you want added - i had a template filepath that i set as a variable and had it opened here
    With wdApp
        .Visible = TRUE
        .Activate
    End With
    
    Dim wordstart1      As Word.Range
    
    Dim wordend1        As Word.Range
    
    Dim wordrange1      As Word.Range
    
    Set wordstart1 = newDoc.Range
    
    Set wordend1 = newDoc.Range
    
    Set wordrange1 = newDoc.Range
    
    
    'First round of finding the specified words that we are looking for in the active document
    
    wordstart1.Find.Execute "Minimum Qualifications"        '***finds a specfic word
    
    wordend1.Find.Execute "Preferred Qualifications"        '***finds a specfic word
    
    wordrange1.SetRange Start:=wordstart1.End + 3, End:=wordend1.Start - 1        'sets the range equal to end of first word +3 spaces and the start of the second word minus 1 space
    
    wordrange1.Select
    
    wordrange1.ListFormat.ApplyBulletDefault        'apply bullet points to all lines within the selected range
    
End Sub