November 2013: Automating Review Comment Management With Simple VBA Tools
On almost every engagement I write one or more on-the-spot Visual Basic for Applications (VBA) tools within Word or Excel to solve a particular problem or automate a particular labor-intensive process for my client.
It occurred to me while doing a pre-delivery review of about 1000 pages of CDRLs that the typical process of collecting/ retyping comments (ostensibly from marked-up Word documents) into an Excel worksheet or other medium, doing prioritization/triage, and tracking closures was ripe for process improvement. The rather simple solution resulted in a significant reduction in manual labor effort and human error.
As in a typical review, Word documents were reviewed electronically (i.e. track changes "on") and comments entered in the review copies. The more than a dozen commented Word files were posted to a single SharePoint folder. Subsequent triage determined which comments were prioritized and were to be implemented into the master documents. Of the 2500 comments dispositioned, approximately 1250 were identified as Critical or Substantive to be implemented over the usual short time period.
So how in a highly automated fashion were the comments to be implemented identified and tracked to closure?
Comments within the reviewed Word files were marked with priority to be implemented (e.g. Critical, Substantive, or Administrative; or priority 1, 2, 3)
When comments were put into the master documents by desktop pubs, the word "done" was added to the original comment in the reviewed document
This information was not manually transferred/copied to another medium, database or list for tracking to closure
In less than 2 hours, I wrote a simple Excel VBA routine (about 20 lines of code) that fully automated the tracking/ disposition of the review comments, that on demand repeatably:
Extracted the comments themselves (and subsequent prioritization and "done", if applicable) from each of the reviewed/marked up Word documents in the SharePoint folder
Transferred the comments, their source, disposition, and implementation status to an Excel spreadsheet
Determined whether the string "done" existed in the comment, and if so, identifying the comment as state "complete" in the spreadsheet
The realized benefits and cost savings of this approach, enabled by unique Black Dog Technology LLC expertise:
Completely removed the need for a separate comments tracking list
Eliminated the entire labor cost and time to manually keep the tracking list status
Set the human error factor on status updates to effectively zero
Captured the comments and their status in a standardized spreadsheet that could easily be transferred to a company-required repository, if required
Contact us to arrange your Proposal Navigator® system capability demonstration.
The Proposal Team's Mission
Synthesize and package the capture team’s vision into a compliant, convincing and compelling offering whose selection can be successfully defended ... without breaking the pursuit budget “bank”...