If you have been using Backstop Report Builder and Excel Toolkit (ETK) to extract actionable insight out of the Backstop CRM platform, life ought to be pretty rosy.
Have you ever looked up the night sky and considered automagically beautifying the above-mentioned intel though? Self-serviced users would instantaneously generate freshest and highly formatted reports, alongside gorgeous charts without you lifting a finger.
Are you interested yet?
Before proceeding further, be forewarned this encounter assumes an intermediate/advanced comfort level with Excel and Visual Basic for Applications (VBA).
If the last sentence sounds like Latin, you may want to consider an Excel/VBA refresher first. Backstop Support Portal also has an extensive library on this topic, or send a love note to your Relationship Manager. Ours is Karina, and she is world-class!
Roadmap
The code is a little long. A brief overview adds clarity. The functions (aka subrountines) are modularized to maximize readability and maintainability. It looks way more beastly than reality!
| Level1 | Level2 | Level3 | Key Purpose |
| Main | |||
| Initialize | Remove pre-existing content and formatting options | ||
| BotCopy | Copy ETK data to destination worksheet | ||
| BotFormat | Optimize formatting for on-screen viewing and printing | ||
| DeleteColumn | Remove selected columns exported from ETK | ||
| SetShortHand | Set abbreviations | ||
| SetColumnName | Replace column names | ||
| SetColumnFormat | Set column types, widths, and formatting options | ||
| SetTitleRow | Add and format a title row | ||
| Helper | |||
| GetSheet | Return Excel Worksheet objects | ||
| GetDictionaryHeading | Return new mappings of column headings | ||
| GetDictionaryClosing | Return new mappings of closing rounds | ||
| GetLastColumn | Return last non-blank column | ||
| GetLastRow | Return last non-blank row | ||
| GetLastCell | Return last non-blank cell | ||
| GetLastRange | Return non-blank cell range |
https://gist.github.com/klopmp/1560a29e0239064e9963695adc657b7f
Main
The primary function that calls three (3) other functions. This is wired to run every time ETK refreshes data.
Initialize
To ensure a clean slate, we remove pre-existing data and formatting options in the destination worksheet before each ETK refresh.
BotCopy
Bots are supposed to take away our jobs, right? Let them have the mundane chores then. This copies all refreshed ETK data to the destination worksheet.
BotFormat
The bulk of formatting chores are done within this function. It invokes the following functions.
DeleteColumn
ETK returns both visible and hidden columns from the Report Builder. This function removes those columns not used in the final printable version.
SetShortHand
Abbreviate values to enhance report readability based on a Dictionary object.
SetColumnName
Rename column headings. This could also be achieved globally within Entity Definition Manager or Report Builder. Doing so here spares us from affecting the spreadsheet anything else, except for this Excel file. Think global, shop local.
SetColumnFormat
Apply numeric and date format options.
SetTitleRow
Add a nicely-formatted report title rapidly in first row.
Helper
These helper functions streamline repetitive low-level tasks.
GetSheet
Instantiate and return a WorkSheet object based on a friendly name.
GetDictionaryHeading
Using a Dictionary to remap new column headings without affecting underlying data.
GetDictionaryClosing
Using a Dictionary to remap new Closing Round values (e.g. First > 1st) without affecting underlying data.
GetLastColumn
Identify the last non-blank column (e.g. K).
GetLastRow
Identify the last non-blank row (e.g. 168).
GetLastCell
Identify the last non-blank cell (K168).
GetLastRange
Identify the non-blank cell range (e.g. C2:K168)
Backstop User Conference (BUC) 2018
Want to see this live? This is a sneak preview of my upcoming BUC 2018 presentation: Client Success Showcase: Increase Efficiency in Backstop on June 7, 2018. I am extremely honored to be speakinh and looking forward to meeting the Backstop family and friends!