Excel VBA Code to Target an Already Opened Word Document

[Fromlostdays1]

Good evening!

I have been working on an Excel booklet that manipulates a Word template I created based on the value of the Excel cells. Basically, the code opens a copy of my Word template (leguinst.docx), replaces some text with cell values, and deletes some bookmarks based on cell values. The problem is, it's an enormous project and I think I hit some kind of limit. If I try to run the code all in one command button now, it errors out saying it can't compile this much.

As a workaround, (I'm hoping) I can just split the code across 2 (or more) command buttons. Please let me know if this is not going to work, haha.

But in doing so I have the problem of targeting the newly created word document with the second command button. Heres my code as it is:

Dim wdapp As Object
Dim wddoc As Object
Dim planadmin As String
planadmin = Range("D7").Value
Dim capitaladmin As String
capitaladmin = UCase(Range("D7").Value)
Dim adminaddress As String
adminaddress = Range("D8").Value
Dim plantype As String
plantype = Range("D9").Value
Dim capitalplantype As String
capitalplantype = UCase(Range("D9").Value)
Dim plancompany As String
plancompany = Range("D10").Value
Dim plannetwork As String
plannetwork = Range("D11").Value
Set wdapp = CreateObject("Word.application")
wdapp.Visible = True
Set wddoc = wdapp.Documents.Open(ThisWorkbook.Path & "\leguinst.docx")

'Replace Plan Administrator with Cell Value
With wdapp
With wddoc.Content.Find
.Text = "[Plan Administrator]"
.replacement.Text = planadmin
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.Execute Replace:=wdReplaceAll
End With
End With

End Sub

Private Sub CommandButton2_Click()
Dim wddoc As Object
Set wddoc = GetObject(, "Word.Application")

Select Case Range("E23").Value
Case "ISBLANK"
Case "Yes"
Case "N/A"
If wddoc.Bookmarks.Exists("pstsn1") = True Then
wddoc.Bookmarks.Item("pstsn1").Range.Delete
End If
End Select

So the first command button opens LegUINST.docx, and replaces all instances of [Plan Administrator] correctly with whatever value I type in Excel.
.
I'm running into all kinds of trouble with the second command button, though. At this point, with the above permutation, I dim wddoc as object and I then I set wddoc as Get Word Application, but to odd behavior. Firstly, the debugger is now catching the erase bookmark section of the code, that has always worked previously and flagging it yellow, and secondly I'm not sure if its even trying to run the code in the now already opened LegUINST.docx document. So again, for the second command button, how would I code it so that it looks for an already opened

 

[NdNoviceHlp]

Hi Fromlostdays1. There are a few things wrong and/or missing with your code. You are not actually using a template document which would end with a .dot file extension (.dotx or .dotm in newer Word versions). Copying a dotx file does sort of mimic a template. You also need to save your document, close the document, quit your Word application and then set your doc and application object variables to nothing. I'm also not that certain that your delete bookmark contents code is correct? For your purposes here, you can remove the Getobject code and your Dim wddoc code from where it is. Then place this code at the top a a module. This will then let U pass the worddoc variable between your command buttons...

Public wddoc As Object

U will get a lot of mysterious results if U have more than 1 Word application running. Go to your task manager processes select the Word application(s) and select End Task if U end up with more than 1 Word application running. HTH. Dave

 

[Fromlostdays1]

Hi Dave! Thanks so much for responding. I tried the above code, but the second command button still gives Run Time Error 424 Object Required. I think though if you'll bear with me I can simplify my question.

I want 2 activex command buttons on Excel sheet1.

[Button 1] should open a copy of my word document called leguinst.docx (I call it a template because its a contract template I made, not because its a word template file, sorry for the confusion). For button 1, here is my code under module sheet1

Public Sub LeguinstOpen_Click()


Dim wdapp As Object
Dim wddoc As Object
Set wdapp = CreateObject("Word.application")
wdapp.Visible = True
Set wddoc = wdapp.Documents.Open(ThisWorkbook.Path & "\leguinst.docx")
End Sub

2. [Button 2] is just to run some code to delete some bookmarks and replace some text in the word document, which should already be created and open after pressing button one.

Public Sub CleanDocument_Click()

'Employee Termination Date
Select Case Range("E42").Value
Case "ISBLANK"
Case "Day of"
If wddoc.Bookmarks.Exists("etermeom1") = True Then
wddoc.Bookmarks.Item("etermeom1").Range.Delete
End If
Case "End of Month"
End Select
End Sub

So I gather what's happening is that wddoc is no longer an object by the time I click button 2. And I gather that your suggested code (Public wddoc As Object) is supposed to make wddoc be recognized even on separate command buttons. So I added that as code to the first button, first right at the top, then in the middle, then very last, but no matter where I put it (if that matters at all) I'm getting a Run Time Error 424 Object Required whenever I click button 2, and the error will be on the line (If wddoc.Bookmarks.Exists("etermeom1") = True Then), so basically the first time the code attempts to call wddoc. Its just not finding it.

I hope that makes my question a little more clear, and I appreciate any further information you could share to help!

Thanks!

 

[NdNoviceHlp]

"Then place this code at the top a module." You need to add a module in the VBE and place the code there... not in your command button code. Dave

 

[Fromlostdays1]

Sorry, I should have said I tried that first. I posted your code at the top of Module 1. It didn't work, but it did change the error. With that I get Run-Time error '91' Object Variable Not set on the same line If wddoc.Bookmarks.Exists("etermeom1") = True Then.