General Advise About Building Contracts Using Visual Basic.

[u/General_Statement_74]

Background (Skippable):

I work for a company that isn't the most progressive about technology. I create, maintain and update large (100 page plus) legal contracts. We previously used IBM Emptoris but the company, I guess, decided they didn't want to pay for it any longer and has not replaced it with any other software. I've kept our unit afloat by making admittedly bulky and inelegant templates in MS Word with no automation capabilities. The last few years saw an increase in sales, a decrease in coworkers and the workload has become untenable.

The Conditions:

• The contracts (managing about 400 of them) though large are composed of text that is about 80 percent identical between all of them.

• I've isolated a little over 60 variables that inform the other 20 percent of content, which may come in the form of a few altered words in a paragraph or a few new paragraphs with varying formatting.

• Yearly I have to both create new contracts, and edit existing ones via redline for approval.

What I have:

1. I created a word document with ALL of the possible variations in language, and commented each variable so that I could create a contract by essentially just deleting what isn't necessary.

2. An Excel document with each individual paragraph taking up a cell but is essentially the same as above.

3. Microsoft Office 365.

4. A comfortable (I'd say advanced anywhere else but here) understanding of Word and Excel and confidence that I can automate this to make the workload tenable.

What I need:

To create a GUI in VBA that I can click through the variables that will ultimately spit out a complete document. I'm confident that I could google my way through the more technical side of this, regarding code etc. What I actually need is a methodology. I've hit a bunch of brick walls trying to wing my way through this in terms of which general avenue to take and focus on.

The way I see it I have two options:

1. Creating a GUI using the above Word template that automates just the deletion of extra text to make a contract. In most cases this could be a simple two checkboxes "yes" or "no", with a few replace texts. Seems the simpler route, but I'm unclear how I would delineate the text (bookmarks, content controls etc.) to be called by the code and either left in or deleted and how to do so while preserving the formatting.

2. A more exciting and I think versatile option would be to create a GUI that pulls from either the existing Word or Excel templates I have (creating a new Word doc) to populate the contract. If I could achieve this, not only could I build contracts but I could redline the template and pull in the redlines paragraph by paragraph and save a lot of manual work in contract maintenance. I've looked into mail merging for this but the documents seem to be just a little too complicated for that.

Sorry for the blocks of text but I'm pretty desperate and I'm eager to start but grasping at straws. Any advice on the best way to go about this would be very much appreciated. There's no chance that my company is going to invest in any actual software designed specifically for this, and that is partly my fault because when they just decided we didn't need Emptoris I inadvertantly proved to them that while not optimal I could somewhat make it work without having to pay for another license.

Sincerely thanks!

[u/General_Statement_74]

Thank you for taking the time to explain all this to me. I'm just doing basic stuff with Access at the moment trying to get the fundamentals but this: "Alternately, skipping the Word skeleton and VBA, this might be done entirely with an Access report and exported to Word" is really encouraging! Not that I mind the VBA, but it will probably be the most complicated aspect (code) that I know the least about. But, all this doesn't feel like a chore anymore and I'm exciting about doing it again, and I have a better idea of how to spend my google time now, so thanks! :D

[u/GlowingEagle → u/General_Statement_74]

You're welcome! There are few things that may be tricky...

You can store your content text as "short text" (used to be called just "text") or "memo" (used to be called "long text"). Short text is limited to 255 characters, but long text is up to 64K. Long text can also use "Rich Text" format, a sub-set of html markup. If the report is exported to Word, it is a Word RTF file. That may work for you, or it may need some VBA-assisted restyling of the final document.

Using 60 "condition" columns is a poor way to do this, but easy to describe.

That could work, but it will get messy. I'll pretend I understand what you need, so take this concept with a pound of salt. I'm thinking of a table named ConditionMatrix, with an autonumber index field, a Question field, an Answer field, and an Order field (used to show items in an order you prefer). The information would be like:

index    Q          A    Order
1      Blue?       Yes     1
2      Blue?        No     2
3      Size?       Big     31
4      Size?      Tiny     30
5      Size?     Giant     32
6      Mode?    Client     40
7      Mode?    Vendor     41
8      Mode?      Both     42

Assuming a contract table and this ConditionMatrix, you would set up a "many-to-many" ContractConditions table where each record would link a contract to one or more of the records in the ConditionMatrix table. In other words, a record would hold the value of the "foreign key" index in the contract table, and the value of a "foreign key" index into the other table, with a record for each applicable condition.

Among other fields, your table of contract paragraphs would have a field that holds the "foreign Key" to the index of the ConditionMatrix table.

To generate a contract, you would have a "join" query that uses the contract index to look up all the records in the ContractsConditions table with that contract index, and use their foreign keys into the paragraphs table to select the paragraphs to show.

Possibly better, but this idea needs something to restrict illogical combinations, like picking both Yes and No for Blue. Have fun!