VBA Code to Open a Word Template and Delete Bookmarks and Enclosed Text Based on the Value in Excel Cells

[Fromlostdays1]

Preamble:
I write legal contracts that are mostly the same between customers, but have variables the customer can elect, which in the contract will take the form of a sentence or paragraph change. I created a Word template with EVERY POSSIBLE permutation of language, and then bookmarked the variable sections. To create a contract now, I just open the template and manually delete all the variable language which does not apply to a particular customer. I also created a GUI in Excel that captures the Group's elections in a simple format. It looks like Customer Elected This: Yes.

The next logical step for me is to use that GUI to, once all the elections are made in Excel, to automatically open the Word template, and read each election from Excel and automatically delete the bookmarks (and enclosed text within them) based on those elections. Because I have every permutation of language in the Word template, this would (maybe thankfully) only be a process of deleting text and not adding any. I've tried to sort this out myself but beyond the little GUI I haven't been able to piece together exactly what I need from searching.

Requirements:
A button at the end of the Excel GUI called "Finalize" that when pressed will:

1. Open the Word Template. (Extra) It would be excellent if the code duplicated the Word template and opened the duplicate so I never have to worry about losing the original, but I can make sure to keep a fresh one filed away so this isn't completely necessary.
2. Search for a specific cell in Excel and match with particular bookmark and either ignore or delete the bookmark and enclosed text depending on what it finds

e.g.
(A1: Yes) = Ignore Bookmark1
(A1: No) = Delete Bookmark1

Save both the Excel Document with all the elections and the Word document with the relevant text deleted. That's basically it. It feels like a lot to ask but I've been trying to sort this out myself for a couple weeks now and I can't seem to get it right. Also, if anyone reading this has an idea for better methodology in general, I'm open to that too.

Thanks so much for any advice!

 

[Colo]

Your idea looks feasible. In order to get a concrete reply such as a sample code from members here, show us concrete samples of Excel spreadsheet and Word document. Here is my sample.

Sub sample()
    'Make a form button then name "Finalize". Apply this sample to it.
    Dim wdapp As Object
    Dim wddoc As Object
    Dim Path As String
    Set wdapp = CreateObject("Word.application")
    wdapp.Visible = True
    Path = "C:\Users\excel users\Documents\OfficeTemplate\sample.dotx"
    Set wddoc = wdapp.Documents.Open(Path)

    Select Case Range("A1").Value
    Case "Yes"
        '        Ignore Bookmark1
    Case "No"
        '        Delet Bookmark1
        wddoc.Bookmarks.Item("Bookmark1").Range.Delete
    Case Else
        '    another action let's say...
        wddoc.Bookmarks.Item("Bookmark1").Range.Text = "Hello World"
    End Select
End Sub

 

[John_w]

See if the code here helps to get you started:

VBA - Delete Bookmark Text in Word based on Excel cell values

Hi New to VBA and trying to find a code that will Open an Word Template document from within Excel (creating a new doc) (The template document is populated with Bookmarks) Remove bookmarks (and related text) depending on cell value So if Excel column A and B has Column A Bookmark1 Bookmark2...

www.mrexcel.com

 

[Fromlostdays1]

Your idea looks feasible. In order to get a concrete reply such as a sample code from members here, show us concrete samples of Excel spreadsheet and Word document. Here is my sample.

Sub sample()
    'Make a form button then name "Finalize". Apply this sample to it.
    Dim wdapp As Object
    Dim wddoc As Object
    Dim Path As String
    Set wdapp = CreateObject("Word.application")
    wdapp.Visible = True
    Path = "C:\Users\excel users\Documents\OfficeTemplate\sample.dotx"
    Set wddoc = wdapp.Documents.Open(Path)

    Select Case Range("A1").Value
    Case "Yes"
        '        Ignore Bookmark1
    Case "No"
        '        Delet Bookmark1
        wddoc.Bookmarks.Item("Bookmark1").Range.Delete
    Case Else
        '    another action let's say...
        wddoc.Bookmarks.Item("Bookmark1").Range.Text = "Hello World"
    End Select
End Sub

Wow, it works! Thank you so much!

I just have one more question, if you'd be so kind. Is there anyway to define a relative path to the word document, as opposed to the absolute path here:

Path = "C:\Users\excel users\Documents\OfficeTemplate\Leguinst.docm"

For reference the Folder name is /LegUI
The Excel Document is Legui.xlsm
The Word template is Leguinst.docm

Basically I want make this whole thing portable. I've read that I can use this.workbook to get the path to the Excel document, but I can't find information on how I would write a relative path to the Word template in this situation.

Either way thank you so much! You have no idea how much time and stress you saved myself and my coworkers when I finish this and make it available to them.

 

[Colo]

Good to hear that we could help you out.

To get a path for a special folder such as Documents, or Desktop, you can use WshShell object. I added a way how to use WshShell to get the Documents folder so that the variable Path fits all the users. It means that no need to change the Path in the code for the Word template.

Sub sample2()
'Make a form button then name "Finalize". Apply this sample to it.
    Dim wdapp As Object
    Dim wddoc As Object
    Dim wsh As Object
    Dim Path As String

    'Get the path for Document folder
    Set wsh = CreateObject("WScript.Shell")
    Path = wsh.SpecialFolders("MyDocuments")
    Path = Path & "\OfficeTemplate\Leguinst.docm"
'    Debug.Print Path '

    Set wdapp = CreateObject("Word.application")
    wdapp.Visible = True
    Set wddoc = wdapp.Documents.Open(Path)

    Select Case Range("A1").Value
    Case "Yes"
        '        Ignore Bookmark1
    Case "No"
        '        Delete Bookmark1
        wddoc.Bookmarks.Item("Bookmark1").Range.Delete
    Case Else
        '    another action let's say...
        wddoc.Bookmarks.Item("Bookmark1").Range.Text = "Hello World"
    End Select
End Sub

 

[Fromlostdays1]

Good to hear that we could help you out.

To get a path for a special folder such as Documents, or Desktop, you can use WshShell object. I added a way how to use WshShell to get the Documents folder so that the variable Path fits all the users. It means that no need to change the Path in the code for the Word template.

Sub sample2()
'Make a form button then name "Finalize". Apply this sample to it.
    Dim wdapp As Object
    Dim wddoc As Object
    Dim wsh As Object
    Dim Path As String

    'Get the path for Document folder
    Set wsh = CreateObject("WScript.Shell")
    Path = wsh.SpecialFolders("MyDocuments")
    Path = Path & "\OfficeTemplate\Leguinst.docm"
'    Debug.Print Path '

    Set wdapp = CreateObject("Word.application")
    wdapp.Visible = True
    Set wddoc = wdapp.Documents.Open(Path)

    Select Case Range("A1").Value
    Case "Yes"
        '        Ignore Bookmark1
    Case "No"
        '        Delete Bookmark1
        wddoc.Bookmarks.Item("Bookmark1").Range.Delete
    Case Else
        '    another action let's say...
        wddoc.Bookmarks.Item("Bookmark1").Range.Text = "Hello World"
    End Select
End Sub

Good evening!

I just want to thank you again for helping me. It's taken me months (and 33 pages of code!) but I did it with your help. I don't know if I should start a new thread, but I have a couple more ideas I'd like to implement if you have a minute. Ignore if I'm asking too much. Before I ask those questions, in case anyone comes behind me, I wanted to share the exact code I landed on (abbreviated with just one example):

'Plan Type, GF vs. NGF
Select Case Range ("E16").Value
Case "ISBLANK"
Case "Grandfathered"
If wddoc.Bookmarks.Exists ("ngf1") = True Then
wddoc.Bookmarks.Item. ("ngf1") .Range.Delete
End If
Case "Non-Grandfathered"
If wddoc.Bookmarks.Exists ("gf1") = True Then
wddoc.Bookmarks.Item. ("gf1") .Range.Delete
End If

If wddoc.Bookmarks.Exists ("ngf1") = True Then This line is required if there are bookmarks within bookmarks, as if one gets deleted it errors out looking for any bookmarks that may have been inside of it. This is basically just: check if bookmarks exist, ignore if not. and you then have to add "End If" at the end.

So if you made it this far,

1: What would be the code to have it read the text in an excel cell, and replace the entire bookmark and text inside it with whatever text is in Excel?
Example:
-if I had "This Is Company Name, Inc." in Cell "E16"
-if I had a bookmark in the word document over the generic word "Company" and the bookmark was named "company1"

I would want This Is Company Name, Inc." to be read in the Excel Document and basically replace the "company1" bookmark with the text from the cell.
Is this possible?

2: Is it possible to have the word document it copies autorename to something else? Can cause a little confusion as its named the same as the template.

3: To consolidate the code and future proof it, some sections of text basically have two different bookmarks on the same text. A paragraph may only appear if the Plan is Grandfathered, AND if the product is a High Deductible Health Plan. I worked around this by creating TWO bookmarks that read two different cells in Excel. What would future proof and I think clean up the code is if I could have ONE Bookmark read 2 different cells. Basically:

Select Case Range ("E16").Value
Select Case Range ("E17").value
Case "Non-Grandfathered"
Case "HDHP"
If wddoc.Bookmarks.Exists ("ngfhdhp1") = True Then
wddoc.Bookmarks.Item. ("ngfhdhp1") .Range.Delete
End If

But I'm not sure what the actual code would look like. This isn't strictly necessary and I'm not sure if its possible but as I was coding there are a bunch of instances where this ability would minimize how much code and how many bookmarks I need.

Again thanks for everything! I did not think this was possible or going to work, but even as it is, it does enough to be extremely useful and more than I thought I could pull off when I started. haha.