Deleting content between two bookmarks in Word via Excel VBA

[karoti99]

Hello! First post, after years of visiting this site and finding a lot of solutions to my VBA challenges.

I have a workbook that generates a word document.

Part of the code populates content controls with data successfully

'populate content controls
With wordApp
.ActiveDocument.SelectContentControlsByTitle("Client").Item(1).Range.Text = Sheets("Data").Range("C1").Value
.ActiveDocument.SelectContentControlsByTitle("Total_Hours").Item(1).Range.Text = Sheets("Data").Range("B1").Value
.ActiveDocument.SelectContentControlsByTitle("Rate1").Item(1).Range.Text = "$" & Sheets("Data").Range("B2").Value
.ActiveDocument.SelectContentControlsByTitle("Rate2").Item(1).Range.Text = "$" & Sheets("Data").Range("B3").Value
End With

However the code immediately after fails. When I try to delete data between bookmark ranges, I get the dreaded 424 object required error

'delete unnecessary content within bookmark range
With wordApp
.ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("InvestmentBegin").Range.Start, ActiveDocument.Bookmarks("InvestmentEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("OrderSummaryBegin").Range.Start, ActiveDocument.Bookmarks("OrderSummaryEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("TermsBegin").Range.Start, ActiveDocument.Bookmarks("TermsEnd").Range.End).Delete
End With
End If

But, if I copy and paste that code into a macro-enabled word document (sans the with wordApp parameters) and run it, it works perfectly and deletes the data within those bookmark ranges successfully.

Any ideas? I need to get Excel VBA to initiate the deletion of the data within the listed bookmark ranges.

 

[karoti99]

*disregard the 'end if' at the tail end of the code
*the 424 is generated at the line .ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete

 

[Skyybot]

Try this: (your code slightly modified)

With wordApp
.ActiveDocument.SelectContentControlsByTitle("Client").Item(1).Range.Text = Sheets("Data").Range("C1").Value
.ActiveDocument.SelectContentControlsByTitle("Total_Hours").Item(1).Range.Text = Sheets("Data").Range("B1").Value
.ActiveDocument.SelectContentControlsByTitle("Rate1").Item(1).Range.Text = "$" & Sheets("Data").Range("B2").Value
.ActiveDocument.SelectContentControlsByTitle("Rate2").Item(1).Range.Text = "$" & Sheets("Data").Range("B3").Value
.ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("InvestmentBegin").Range.Start, ActiveDocument.Bookmarks("InvestmentEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("OrderSummaryBegin").Range.Start, ActiveDocument.Bookmarks("OrderSummaryEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("TermsBegin").Range.Start, ActiveDocument.Bookmarks("TermsEnd").Range.End).Delete
End With

 

[Skyybot]

*disregard the 'end if' at the tail end of the code
*the 424 is generated at the line .ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete

Is there an IF statement? If so, could you show the code in it's entirety?

 

[karoti99]

Sure. Here is the entire code block. The IF statement checks another value on the excel document to qualify it to run.
It runs the 'populate content controls' part with no issue, but bombs out at the 'delete unnecessary content' part.

This was all one statement and I broke it into two in an attempt to isolate the problem. Recombining it as you suggested above resulted in the same 424 on the same line. Bizarre

'populate content controls
If Range("outputworddoc").Value = "Yes" Then
With wordApp
.ActiveDocument.SelectContentControlsByTitle("Client").Item(1).Range.Text = Sheets("Data").Range("C1").Value
.ActiveDocument.SelectContentControlsByTitle("Total_Hours").Item(1).Range.Text = Sheets("Data").Range("B1").Value
.ActiveDocument.SelectContentControlsByTitle("Rate1").Item(1).Range.Text = "$" & Sheets("Data").Range("B2").Value
.ActiveDocument.SelectContentControlsByTitle("Rate2").Item(1).Range.Text = "$" & Sheets("Data").Range("B3").Value
End With
'delete unnecessary content within bookmark range
With wordApp
.ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("InvestmentBegin").Range.Start, ActiveDocument.Bookmarks("InvestmentEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("OrderSummaryBegin").Range.Start, ActiveDocument.Bookmarks("OrderSummaryEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("TermsBegin").Range.Start, ActiveDocument.Bookmarks("TermsEnd").Range.End).Delete
End With
End If

 

[Skyybot]

Sure. Here is the entire code block. The IF statement checks another value on the excel document to qualify it to run.
It runs the 'populate content controls' part with no issue, but bombs out at the 'delete unnecessary content' part.

This was all one statement and I broke it into two in an attempt to isolate the problem. Recombining it as you suggested above resulted in the same 424 on the same line. Bizarre

'populate content controls
If Range("outputworddoc").Value = "Yes" Then
With wordApp
.ActiveDocument.SelectContentControlsByTitle("Client").Item(1).Range.Text = Sheets("Data").Range("C1").Value
.ActiveDocument.SelectContentControlsByTitle("Total_Hours").Item(1).Range.Text = Sheets("Data").Range("B1").Value
.ActiveDocument.SelectContentControlsByTitle("Rate1").Item(1).Range.Text = "$" & Sheets("Data").Range("B2").Value
.ActiveDocument.SelectContentControlsByTitle("Rate2").Item(1).Range.Text = "$" & Sheets("Data").Range("B3").Value
End With
'delete unnecessary content within bookmark range
With wordApp
.ActiveDocument.Range(ActiveDocument.Bookmarks("OverviewBegin").Range.Start, ActiveDocument.Bookmarks("OverviewEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("InvestmentBegin").Range.Start, ActiveDocument.Bookmarks("InvestmentEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("OrderSummaryBegin").Range.Start, ActiveDocument.Bookmarks("OrderSummaryEnd").Range.End).Delete
.ActiveDocument.Range(ActiveDocument.Bookmarks("TermsBegin").Range.Start, ActiveDocument.Bookmarks("TermsEnd").Range.End).Delete
End With
End If

Thanks. Try running the code in Post#3. Let me know what happens.

 

[karoti99]

Thanks for the help. Unfortunately it generates the same 424 on the same line mentioned when inserted into my excel macro.

BUT

If I copy your code from post 3 to a macro in the actual word doc (sans the with wordApp parameters) and run it from word, works perfect!

The issue seems to be somehow a problem with excel initiating the command.

 

[Skyybot]

How are you defining, and where are you initializing wordApp?

 

[karoti99]

At the beginning

' check if Word is open, if so, use it
On Error Resume Next
Set wordApp = GetObject(, "Word.Application")
Set excelApp = GetObject(, "Excel.Application")

' if Word is closed, open an instance
If err <> 0 Then
Set wordApp = CreateObject(Class:="Word.Application")
End If

 

[karoti99]

I figured it out. I didn't have the word object library loaded in Excel. This tool is over ten years old and I managed to build it without requiring the library to be loaded. Guess those days are over!
Thanks again Skyybot. Your question about 'defining' sent me in this direction.