Homework H07
Business Intelligence at SmallBank: Canned Queries
Goal
Learning how you can automate the gathering of business data from a cloud database and showing them in Excel.
Context
"There's got to be a better way to do that!"
You like your new job at SmallBank, but there's one thing you dread: every day at the end of business, you have to access the bank's database and create dozens of updated customer lists for management review. It's a boring, repetitive task and a bad way to end a good day.
So, you decide to automate the task using Visual Studio (VS) and Visual Basic for Applications (VBA). In no time, you create an
Excel-based app that connects to the database and produces the lists with the press of a button. It's a huge improvement!
Ms. Lendalot sees your work and immediately recognizes its business potential.
"This is A.MA.ZING! Sooooo much faster!"
"Thank you Ms. Lendalot! It automates my least favorite daily chore."
"You know what? We can do even better! We should give your app to the managers, so they can access updated customer reports at any
time they choose. The data will be more accurate, the managers will like it better, and you will not have to do your least
favorite daily chore. It is a true WIN-WIN-WIN!"
Task and Criteria
Create a VS project that implements the following requirements:
- At start, the Excel spreadsheets does not show any data (just buttons and empty list objects).
- pressing an orange button labeled "Get VA Customers" causes Excel to show all the available info on all the customers who live in VA in a banded white/orange list object. The button has black ink. Pick colors similar to the video.
- pressing a second button labeled "Get TX Customers" shows the same data for the customers who live in TX in a separate list object. Both button and list object must be light blue, black ink.
- Make sure that the buttons do not interfere with the list objects, causing errors or partially occluding data. Place the buttons out of the way of the list objects.
- Make sure that the column headers show the names of the columns in the tables when filled.
- Column size must not cut out data or headers (autofit).
The DB connection code mentioned in the video for you to copy and paste is here.
As always, the video is a just learning tool. It is not the solution.