Microsoft Access and Word VBA Document Assembly System Demo

Summary:

This demonstrates a methodology and some of the associated technologies I’ve used to create complex and flexible contract generators, proposal generators, and other document assembly systems.

Technologies:

  • Microsoft Access (SQL Server*)
  • Microsoft Word
    – Word Templates
    – Word Global Template w/AutoText Entries

* SQL Server: For simplicity, this demonstration uses Microsoft Access as the database. However, I normally run these systems on SQL Server or SQL Server Express. I’ve also built systems using the same logic that connect to Oracle, SAP, Quickbooks, Sage Accounting, and other systems.

I’ve created custom document assembly systems for several large insurance companies, a couple of the largest law firms in the world, and for a number of other clients. Most recently, a Payroll Company hired me to build a custom document solution to handle specially formatted reports to be sent to their clients.

I realized upon completing that system that I’ve never demonstrated document assembly on this blog. This remedies that.

I will be working to cleanup the code so I can upload the various components. I quickly hacked this together from a client’s system and want to ensure no vestiges or proprietary information remains before I publish anything.

Video Demo (7:30)

The following is a video example. Below that are the four system outline slides from the video. Finally, I go through some of the specific data elements and system process specifics.

Overview slides

Contract Assembly Pieces

The Database Tables

This is a simplistic database. I’m storing just the information needed to demonstrate the system and nothing else.

  • tblCustomers
    -Customer_uno (primary key)
    -Contact info (company name, contact name, address, email, phone)
  • tblTemplates
    -Template_uno (primary key)
    -Full path and descriptive name of the word template
  • tblAText
    -AText_uno (primary key)
    -Name and description of AutoText entries
  • tblATextGrp
    -ATextGrp_uno (primary key)
    -AutoText Group name
  • tblATextGrpLnk
    -ATextGrpLnk_uno (primary key)
    -ATextGrp_cd (links to ATextGrp_uno)
    -AText_cd (links to AText_uno)This table joins the AutoText Group table to specific AutoText entries – allowing entries to be included in as many groups as needed. 

Word Document Templates

The Microsoft Word templates contain base language and have a series of AutoText identifier codes that look like:

@:AA.010.00@:AA.010.02@:AA.010.05

Below I’ll describe how the system handles replacing codes with AutoText.

In this particular example I also include a code identifier of: “@STATE” for a single state specific block of text. There are also bracketed contact information fields such as:[CompanyName], [Street], etc.

Global Template w/ AutoText Entries

The global template for AutoText entries is stored in the Word Startup folder. It contains all the AutoText entries necessary for the system. In more complex systems, I’ve created multiple global templates and wrote a library/catalog routine to rebuild the entries after they are edited. In the insurance world, contract analysts maintain specific libraries. In the legal industry, I often work with associate attorneys and paralegals for this function.

As indicated, a naming convention is created that best reflects the library of language. In the insurance world, I used a two character prefix, a 3 digit section, and a two digit suffix specifying an iteration of language of the three digit section.

For instance:
BH.025.00 & BH.025.02 might both be the same section (025) of Behavioral Health (BH) language. With the suffix, (00) the base language and (02) an iteration with a slight change.

The Automation Process

When the process runs, the AutoText entries in the selected AutoText group are located and their code identifier prefix changed from “@:” to “@U:”.

So if the code: AA.010.02 is in the AutoText Group, the line above would change to:

@:AA.010.00@U:AA.010.02@:AA.010.05

After all included codes are identified as above, a global wildcard search and replace removes the unused codes.

Word wildcard Find/Replace searches for: “\@:??.???.??” and replaces with an empty string: “”

A second pass through the document searches for: “@U:” – deletes those characters, selects the following 9 characters (which highlights the auto-text entry), and inserts the associated AutoText.

The state specific code mentioned above is located and the associated AutoText inserted.

Finally, the bracketed contact information is replaced.

Conclusion

This is a considerably simplified but representative example, of how much more complex contract and proposal assembly systems have been implemented. Based on the business case and workflow, you can see how a highly flexible and maintainable system can be built.

If you have questions, do not hesitate to reach out.

Posted in Consulting, Microsoft Access, SQL Server, Tips and Tools, Video and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *