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!

 

[japers]

I forgot to mention that you need to enable Microsoft Word in references for this script to start working.

The error that I get is "Argument not optional". I have read a lot about this in the forums but I just don't understand how to overcome it here?

 

[aamailhot]

Regarding the Microsoft Word references, I strongly discourage enabling this option (called early binding) and rather going with a late binding strategy as in this way your code will be more robust and able to run on a wider range of environments (different versions of MS Office, etc). You can read up on this more here, but basically the two steps you need to do are as follows:

1) Define your Word objects differently in a more generic sense, which is called late binding. Namely:

Dim wrdApp As Object

2) Additionally (And I realize it is somewhat a pain), you will need to replace all the Word enumerations you have in your code with their literal number representations (I like to keep the 'wd' enumeration name close by in a comment for posterity too). To get these numeric values, you can either print/debug the code as you have it with the Word reference temporarily enabled, or run it right in Word, OR (my preference) simply look up the enumeration definitions in MS documentation (I find Google is easiest to find them).

Note also that if you do the late-binding approach, you will loose the nice editor features like 'intellisense' where it will auto-complete Word object properties while you type. Thus you are essentially making your life a bit more difficult up front in order to make the code more robust later .

 

[Macropod]

Regarding the Microsoft Word references, I strongly discourage enabling this option (called early binding) and rather going with a late binding strategy as in this way your code will be more robust and able to run on a wider range of environments (different versions of MS Office, etc).

What a load of nonsense. You clearly don't understand the benefits of early binding.

 

[Macropod]

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!

There are multiple problems with your code. For example, both:
Dim myrange As Range
and
Set myrange = Selection.Range
refer to an Excel range, but then you're trying to point it's end to the end of the Word document. Working with Slections is also very inefficient. Try:

Sub SelectRangeBetween()
Dim wrdApp As Word.Application, wrdDoc As Word.Document, wrdRng As Word.Range
Set wrdApp = CreateObject("Word.Application")
With wrdApp
  .Visible = True
  Set wrdDoc = .Documents.Add
  With wrdDoc
    .Range.Text = "Hello and Goodbye for now"
    With .Range
      With .Find
        .ClearFormatting
        .Text = "Hello"
        .Forward = True
        .Wrap = wdFindStop
        .Execute
      End With
      If .Find.Found = True Then
        Set wrdRng = .Duplicate
        With wrdRng
          .End = wrdDoc.Range.End
          .Start = .Start + InStr(.Text, "Goodbye") - 1
        End With
      End If
    End With
  End With
End With
End Sub

 

[aamailhot]

What a load of nonsense. You clearly don't understand the benefits of early binding.

Thanks for pointing out the error of my ways Paul. I'd be interested in knowing more details as to where I went wrong here. As I said in my post, I acknowledge that both early and late binding have their respective benefits.

 

[Macropod]

Your contention was that late binding makes the code "more robust and able to run on a wider range of environments (different versions of MS Office, etc)." Both statements are patently false.

You also calimed:

need to replace all the Word enumerations you have in your code with their literal number representations

which is also false. The simple expedient of declaring any Word constants that be used is sufficient.

 

[aamailhot]

Your contention was that late binding makes the code "more robust and able to run on a wider range of environments (different versions of MS Office, etc)." Both statements are patently false.

You also calimed:

which is also false. The simple expedient of declaring any Word constants that be used is sufficient.

Hmm, I guess I am just a little confused then because I myself have had experienced (i.e. not second-hand) the first point of "early-binding not working in some different MS Office environments". It was a while ago but my recollection is that I was writing VBA in an Excel .xlsm file and also manipulating Powerpoint files in that same code. Therefore I added the necessary PPT references to make early binding work, then ran into some breakage when trying to run the same tool on another computer. Rather than once again setup different PPT references on the 2nd computer, I opted to refactor the tool to utilize the more generic late-binding (as I support many users whom expect tools to work out-of-box and don't know how to setup VBA references themselves). Perhaps there is a way to programmatically have VBA code establish the necessary references in various environments? I believe the two environments I was working in were both some Office 20## variant.

And I see now on the 2nd point of "enumerations"; I concur that defining the necessary constants up front makes more sense to me too.

 

[Macropod]

Perhaps the problem you encountered was because you compiled your early binding code on a later version of Office than the one you subsequently encountered problems with. This has nothing to do with the robustness of the early-binding code or the range of environments it can be run on but everything to do with violating an early-binding maxim: always set the references and compile your code on the earliest version you intend to support.

When you compile early-binding code, the application references are forwards-compatible but not backwards compatible.

 

[Macropod]

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

Early binding is the preferred method. It is the best performer because your application binds directly to the address of the function being called and there is no extra overhead in doing a run-time lookup. In terms of overall execution speed, it is at least twice as fast as late binding.

Early binding also provides type safety. When you have a reference set to the component's type library, Visual Basic provides IntelliSense support to help you code each function correctly. Visual Basic also warns you if the data type of a parameter or return value is incorrect, saving a lot of time when writing and debugging code.
...
The advantages given to early binding make it the best choice whenever possible..