Every company has its own way of doing things. Their internal processes and procedures honed to perfection over the years, run like a well-oiled machine. Data feeds they get from each of their suppliers is the same format (*.CSV) with the same columns, in the same order. Internal QC Reports are flawless and easily produced using Microsoft Excel each week/month/quarter. The same goes for the external Client Reports. And the long-standing Supplier Scorecard format they publish each month is perfect for all the suppliers. Everything is in perfect order. You know – just like at YOUR company! 😁
And then one day IT happens – CHANGE!
Suddenly things start to crumble…
One supplier hired ‘that guy’ who needs to see their Supplier Scorecard data broken out in more detail, so they add a couple of new columns to their version of the CSV file.
Then, one client decided they need their Client Reports branded with their logo and color scheme, and they want a pivot table added on a new worksheet so they can ‘play’ with the data.
And finally, your own QC manager modified the company’s list of defect codes by grouping them into categories to make the QC Reports easier to digest.
Anxiety Builds…
What will the new columns in this ONE supplier’s CSV file do to your database (you DO have a real database, right?) Will you need a separate database for this one supplier? What if another supplier decides to make a different change to their CSV file?
You’ve always used a template for the Client Reports to make sure they look the same. Do you now need a new template each time a client decides they want to change their version of the report? That means more files to support, and versions to track. And you had SO HOPED to avoid those dreaded PIVOT TABLES! AAAarrgghhh!!!
And as for your QC manager’s changes, how many spreadsheets, charts and graphs will need to change, not to mention PowerPoint slide templates?
These are but a few of the scenarios that The PC Help Desk has helped clients work through over the past 20+ years. These challenges don’t require a new system or platform. They don’t need the latest AI technology. And they certainly don’t demand moving your data operations to ‘the cloud’! They don’t even require new software or licensing. They only require a bit of custom VBA (Visual Basic for Applications) programming. And while there are many ways to address these changes, they still have one thing in common – they can all be programmed using your existing Microsoft 365 products.
Let’s take a look at some possible approaches.
Microsoft 365: Product or Tool?
Businesses buy Microsoft 365 subscriptions because they want the best-in-breed products for spreadsheets, documents, and presentations. Most users will only ever learn/use a small fraction of the features in these products and will never realize just how powerful they are.
We at The PC Help Desk look at these offerings a bit differently. We see them as a powerful, programmable widgets that can be put together using VBA in an infinite number of ways to solve real-world challenges, big and small, without having to jump on the latest cloud/AI/platform bandwagon.
Using the Form Tools in Access
One way to address the changes to the Client Reports and Supplier Scorecard might be to build a small interface to allow a user to pre-select options prior to printing. Look at the simple example in the image at right.
This dialog box was built using Microsoft Access as the ‘host’ for the controls we need. In this case, we’re not using Access as a database; there is no data in this Access file, nor does there need to be in order to use it. We can just use the building blocks Access provides to build a user interface we want to program for our specific needs.
Build a UserForm in Excel or Word
We could achieve the same thing by building a UserForm in Microsoft Excel. To be clear, this could be a 1-sheet spreadsheet file used as a ‘host’ for a bunch of controls we want to create and program. We would just be using the toolset that Excel provides us to build a user interface that we can program.
The image at left looks slightly different than the Access version, but it would behave exactly the same way as the Access version. We could just as easily produce this same UserForm in a Word document.
I’m not advocating using a Word or an Excel file solely as an entry point for any kind of User Interface, but you should be aware that these programs can be used in ways that you probably haven’t imagined!
Include a Prompt
Still another option would be to have some sort of prompt appear when creating these reports so the user can make choices as the reports are being rendered. At right is an example of a popup dialog box that could be presented to a user to allow them to apply brand formatting (if it exists) to the report being run. This is a dialog box created in Access allowing for more creative control over the appearance. But the same functionality, if not the formatting, can be realized using custom UserForm objects in Excel or Word.
Any one of these options would allow for programming logic using VBA. That logic can understand the choices made on the forms and react accordingly. Let’s say we get the color scheme and logo files for this client who wants their reports branded. We can store that information in a table or file somewhere. When the time comes to produce the reports, the VBA logic can look to see if we have any ‘branding data’ available for the current client. If it exists, then the VBA code branches in a way that allows the files to formatted using that information. If not, then the default formatting is left in place.
We could also set up a table to define options for various reports and clients and have our VBA code read the options from that table to determine how to produce reports automatically. Look at the following example table:
This table allows us to tell the VBA code that when printing Rpt_B for Client 1, they don’t want a Pivot Table included. But if we are building Rpt_A or Rpt_C for Client 1, then the Pivot Table sheet should be included. We could have added another column to the table to indicate whether to use branding here as well. That still leaves us with the prospect of having to maintain 2 different report templates; one with the Pivot Tables and one without, right? Nope – we could just add the pivot table sheet to the original template. If the table says to include it, it’s already there. If the table says NOT to include it, the VBA simply deletes that worksheet before the file is saved! One file to maintain with all the options built into it ahead of time and we use VBA to discard what we don’t want to include on a case-by-case basis!
Don’t Wait! Seek VBA Assistance Immediately!
When you first start to exhibit the symptoms of anxiety after learning that your perfectly-tuned processes are suddenly disrupted by some clever supplier, client or manager, contact The PC Help Desk ASAP. We’ll treat your tech anxiety with a couple of doses of VBA and you’ll be feeling better in no time!