This serves as a field guide for those Backstop practitioners in the wild.
We will illustrate a use case by grabbing Meetings/Calls, format the raw data with Excel macros and Visual Basic Application (VBA), and most importantly have fun with it.
Are you buckled up?The PlotMOM Capital is a fictitious fund of funds (FOF).
Eli, Jim, and Kenneth are in the midst of raising for MOM VIII Venture Fund.
ACME Ventures and MOM Capital have been in talks past few months.
Each interaction and meeting is meticulously logged in Backstop.
We are to automate the generation of weekly meeting logs.
The raw data natively out of Backstop Report Builder, although complete, could be prettier.
The ask is toAdd an eye-catching report title and current date stampDisplay dates in MMM-DD format (eg May-12)Abbreviate attendee names with their initialsAbbreviate activity tagsDelineate past and future meetings with a subtle visual clueThe Excel file template is freely available on Github.
Technical ApproachAt a high level, we build two Backstop reports, invoke VBA code to mash and format the raw data, and crank out a visually-appealing masterpiece.
Easy peasy.
Build an ETK report to pull Meetings/Calls out of Backstop > _meetingsBuild an ETK report to pull MOM Capital employees and their initials >_employeesCreate a worksheet (aka tab) to house mappings between Activity Tags to display values >_tagsCreate a worksheet to house the gorgeous and curated output >MeetingsBefore deep-diving into any VBA code, be forewarned this might feel a little overwhelming and over-engineered.
Well, we are planting the seeds for even more legendary scenarios in upcoming blog posts.
Stay tuned!Under the HoodThe code is a little long.
A brief anatomy overview adds clarity.
The subroutines and functions are broken down to maximize readability and maintainability.
It isn’t as complex as it looks!Main SubroutineThe primary function that calls a number of subroutines and functions.
This is wired to run every time ETK refreshes data.
InitializeTo ensure a clean slate, we remove pre-existing data and formatting options in the destination worksheet before each ETK refresh.
SetCopyClone raw data to the destination worksheet.
SetShortHandAbbreviate values to enhance report readability based on a Dictionary object.
SetColumnNameRename column names.
This could also be achieved globally within Entity Definition Manager or Report Builder.
The benefit of renaming it here leaves everything else intact, except this Excel file.
SetTimelineFind the threshold between the latest past event and nearest future one, and insert a black line as a quick visual clue.
SetColumnFormatSet numeric and date formatting options.
SetDeleteColumnETK returns both visible and hidden columns from the Report Builder.
This function removes those columns not used in the final printable version.
SetTitleAdd a report title and date stamp.
SetPrintAreaSet printing options such as margins, orientation, scaling, and gridlines.
Helper FunctionsThese helper functions streamline the repetitive low-level tasks.
GetSheetInstantiate and return a Worksheet object based on a friendly name.
GetDictionaryTagUsing a Dictionary object to map activity tags to display values.
GetDictionaryAttendeeUsing a Dictionary object to map attendee names to initials.
Instead of being hardcoded, initials are stored as Other ID and pulled in using ETK.
GetDictionaryHeadingUsing a Dictionary object to map columns headings.
We could also easily revise the column names in Report Builder.
However, some occasions mandate consistent naming conventions; this gives us more flexibility.
GetDictionaryDelColUsing a Dictionary object to map columns to deleted from the final output.
I wish ETK does not dump all columns indiscriminately onto the report.
GetActiveRangeIdentity the non-blank cell range (e.
g.
A1:K18)GetLastColumnIdentity the last non-blank column (e.
g.
K).
GetLastRowIdentity the last non-blank row (e.
g.
18).
Source CodeOriginally published at http://klopmp.
com on May 21, 2019.
.