Data is easier to understand when presented visually, like this:
This chart shows the top 20 products (by quantity) sold by state. This is the kind of chart most sales organizations require. “But wait! There’s more!”
Once a sales organization sees their data, often they want to “drill down” to understand it better. XL’s Pivot Tables support drill down and they make great source data from which to create charts like the one above. Here is the supporting Pivot Table.
From this Pivot Table, users can “double click” on any value to “drill down” and see the detail entries. Below are the results of double clicking the “55” at the intersection of “OR” and “Northwind Traders Chocolate Biscuits Mix”
So rather than build the chart first, I always build a pivot table then create a pivot chart from it. Now before attempting to build this chart, we need to add a little more data to our Query Table. Below is the expanded SQL statement. Add it to your macro.
.CommandText = Array( _ "SELECT O.`Order ID`, O.`Customer ID`, O.`Order Date`, C.`First Name`, " & _ "O.`Ship State/Province`, D.Quantity, P.`Product Name` " & vbCr, _ "FROM Customers C, Orders O, `Order Details` D, Products P " & vbCr, _ "WHERE O.`Customer ID` = C.ID " & vbCr & _ " AND O.`Order ID` = D.`Order ID` " & vbCr & _ " AND D.`Product ID` = P.ID " & vbCr & _ " AND C.`State/Province` LIKE '" & s & "'")
Now, to add the same Pivot Table and Chart to your macro you could:
- Click the “easy” button to bring in the expanded data set
- Turn on the Macro recorder
- Insert > Pivot Table > Pivot Chart
- Put Quantity in the Data area; State in the columns; and Product Description in the rows
- Click the down triangle on the Product Description header: select More Sort Options > Descending (Z to A) by: > Sum of Quantity
- Click the down triangle again: select Value Filters > Top 10: and change 10 to 20
- Right click the chart: Move Chart > New Sheet
- Go to the chart tab and change the chart type to columns stacked
- Turn off the Macro recorder
- Go into the VBA editor
- Cut and paste the newly recorded code just before the last END IF of Macro1.
You could use routines from my next posts to do the job and take care of some housekeeping problems you’ll discover when you try to rerun the macro you recorded.
In the last post we cleaned up what the macro recorder generated. Today we fix one more user interface (UI) problem. Today we will replace the generic “Enter Parameter Value” with a more meaningful input box. Here is what it will look like:
Dim s As String s = Trim( _ InputBox("Enter State Code:" & vbCr & vbCr & _ "'%' is a wildcard. By itself it will retrieve all states. " & _ "'N%' will retrieve all states beginning with 'N'" & vbCr & _ "'%Y' will retrieve all states ending in 'Y'", _ "State Code Prompt", "%") _ )
If s > "" Then
The Trim statement removes any blanks before or after the user’s input. The InputBox statement creates the form. If the user hits cancel, “s” will be empty and we shouldn’t do anything. If “s” is not empty, we must incorporate it in our SQL string. Add the red colored text into this existing line (from last post).
"WHERE O.`Customer ID` = C.ID AND C.`State/Province` LIKE '" & s & "'")
This next bit completes the if statement but it must be just before the End Sub
Congratulations, we just added a little polish to our spreadsheet.
In the last post we added an “easy” button to “easily” kick off our macro. Today we’re going to do a little brain surgery on the macro we recorded. We will:
- Change the way we clear the worksheet.
- Remove properties from the QueryTable statement that aren’t necessary
- Reformat the QueryTable statement to make its properties clearer
- Improve the SQL statement’s readability
- Add the SQL Operator “LIKE” to improve its functionality
- Add formatting to our results
Here is the final version:
' Clear the worksheet Cells.Delete Cells.ClearContents ' Get Data With ActiveSheet.ListObjects.Add( _ SourceType:=0, _ Source:=Array( _ "ODBC;" & _ "DSN=MS Access Database;" & _ "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"), _ Destination:=Range("$A$5")).QueryTable .CommandText = Array( _ "SELECT O.`Order ID`, O.`Customer ID`, O.`Order Date`, " & _ "C.`First Name`" & vbCr & _ "FROM Customers C, Orders O " & vbCr, _ "WHERE O.`Customer ID` = C.ID AND C.`State/Province` LIKE ?") .RowNumbers = False .ListObject.DisplayName = "Data" .Refresh BackgroundQuery:=False End With ' Format contents ActiveSheet.ListObjects("Data").ListColumns("Order Date"). _ DataBodyRange.NumberFormat = "m/d/yy" End Sub
Change the way we clear the worksheet
Previously we just cleared the cells’ contents and that was fine until we added a button. Shapes, like buttons, will sometimes distort (I’m not smart enough to know why – so maybe someone can help me out here) when we clear contents unless we also delete the cells with the QueryTable. Cells.Delete does the trick.
Remove properties from the QueryTable statement
When we record macros, XL throws everything into the macro, often far more than is needed. Compare the code posted here to what XL recorded for you to identify what I’ve cut from our patient. What we learn from this comparison will help us when we write macros from scratch.
Reformat the QueryTable statement
The macro recorder generates functional code, but it’s not pretty. I’ve indented pieces to make it a little clearer as to what is what. The “ActiveSheet.ListObjects.Add” is a very long statement which includes the connection string. I’ve colored a portion of the connection string in red. You will need to replace this portion with the location of your NorthWind database.
Improve the SQL statement’s readability
The SQL string generated by the macro recorder contains the database’s full path and uses the table’s full name as an alias. The full path is unnecessary because the connection string provides it. Once we remove the path from the table’s name, using the table’s full name as an alias serves no purpose. So to make the SQL string easier to read, I’ve used “O” as an alias for the Orders table and “C” for Customers.
For more about Aliases see: http://www.w3schools.com/sql/sql_alias.asp
I’ve also replaced the CHR(13) & CHR(10) with a constant provided by VBA, vbCR. The result is a much cleaner, shorter, easier to read SQL statement that functions identical to the recorded SQL statement except –
Add the SQL Operator “LIKE”
I’ve improved our SQL’s WHERE clause with LIKE. LIKE lets us use wildcard characters. So if I want all state codes that begin with “N”, I can enter “N%” (without the quotes) in the parameter to retrieve records for NC, ND, NY, NH, etc. Or I can retrieve all records by entering “%” by itself. If I just want records for “NY”, then I still enter “NY”. So LIKE in a WHERE clause is like “=” only more versatile.
Sometimes XL will represent dates as date serial numbers that mean nothing to us humans. We can fix that by adding formatting. The “ActiveSheet.ListObjects(1).ListColumns(“Order Date”).DataBodyRange.NumberFormat = “m/d/yy”” statement instructs VBA to apply the number format “m/d/yy” to the cells in the data range of the column with header “Order Date”.
One of the neat things about ListObjects, such as this, is you can refer to ranges within ListObjects in ways that make sense. A listObject is also known as a Table. Tables are always part of a worksheet. Tables have several ranges all set up for us: header rows, data rows, total rows, and columns. So to get to one of those ranges, we need to use the ListObject hierarchy: Worksheet Object > ListObjects Collection > Specific ListObject > then one of the following:
HeaderRowRange – Row with all column headers
DataBodyRange – Table’s data (Table without the Headers or Totals)
TotalRowRange – Row with totals (If ShowTotals is set to TRUE)
ListRows(n) – nth row in our table
ListColumns(n or Header) – nth column in our table, or colum with a specific Header
For more information on ListObjects see: http://msdn.microsoft.com/en-us/library/bb242354(v=office.12).aspx
Change your macro to look like the listing above (fixing the red colored code as instructed) and save your spreadsheet. Click your “easy” button (prior post) and play with different wildcard parameters. We’ve got a little more UI (User Interface) work to do and then we’ll start adding pivot tables and charts.
Put an “easy” button in your spreadsheets to facilitate launching any macro.
Welcome new readers. We’ve been talking about how to bring data into XL. This post introduces the first of many standard routines I use to make assembling real data analysis tools in minutes. It’s a little function to enhance the user experience by making buttons that look similar to Staple’s “easy” button. Who couldn’t use an “easy” button?
Global Const Success = False Global Const Failure = True
Function Create_Easy_Button(sText As String, sMacro As String, _ x As Long, y As Long) As Boolean
' Create_Easy_Button: Create a clickable button resembling ' Staples' "easy" button
' Parameters: sText: A short word for the button like ' "easy", "Load", "Post", "Save", or "New" ' sMacro: The macro name to attach to this button ' x: Button's horizontal position ' y: Button's vertical position
' Example: Create_Easy_Button "easy", "Macro1", 10, 8
' Date Init Modification ' 01/01/01 CWH Initial Programming Copyright Craig Hatmaker 10/07/2009
On Error GoTo ErrHandler ' Create_Easy_Button = Failure 'Assume the Worst
If ShapeExists(sText & "_Button_Base") Then _ ActiveSheet.Shapes(sText & "_Button_Base").Delete If ShapeExists(sText & "_Button_Text") Then _ ActiveSheet.Shapes(sText & "_Button_Text").Delete
If x = 0 Then x = 10 If y = 0 Then y = 8
With ActiveSheet.Shapes.AddShape(msoShapeOval, x, y, 35, 35) .Name = sText & "_Button_Base" .Fill.ForeColor.RGB = RGB(200, 0, 0) 'Dark Red center .Placement = xlFreeFloating .OnAction = sMacro With .Line 'White border .ForeColor.RGB = RGB(255, 255, 255) .Weight = 3 End With With .Shadow .Visible = True .OffsetX = 2 .OffsetY = 2 .Transparency = 0.5 .ForeColor.RGB = RGB(10, 10, 10) End With With .ThreeD .BevelTopType = 3 .BevelTopDepth = 20 'Rounded top .BevelTopInset = 19 'Rounded Top .ContourWidth = 0 'No line around the base .Depth = 2 .ExtrusionColorType = 1 .FieldOfView = 45 .LightAngle = 300 'Light from above and to the left .Perspective = 0 .PresetLighting = 15 .PresetMaterial = 6 'Plastic End With End With
With ActiveSheet.Shapes.AddTextbox( _ msoTextOrientationHorizontal, x - 1, y - 2, 35, 35) .Name = sText & "_Button_Text" With .TextFrame .MarginBottom = 0 .MarginLeft = 0 .MarginRight = 0 .MarginTop = 0 .HorizontalAlignment = xlHAlignCenter .VerticalAlignment = xlVAlignCenter .Characters.Text = sText With .Characters.Font .Bold = True .Size = 16 .Name = "Calibri" .Color = RGB(255, 255, 255) .Shadow = True End With End With .Line.Visible = False .Fill.Visible = False .TextEffect.PresetTextEffect = 2 .Placement = xlFreeFloating .OnAction = sMacro End With Create_Easy_Button = Success 'Successful finish
On Error GoTo 0 If Err.Number <> 0 Then MsgBox _ "Create_Easy_Button - Error#" & Err.Number & vbCrLf & _ Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
Function ShapeExists(sName As String)
' ShapeExists: See if a Shape Exists
' Parameters: sName - Shape Name to be checked
' Example: If not ShapeExists("EasyButton") then _ ' Create_Easy_Button "easy", "Show_Prompt", 10, 8
' Date Init Modification ' 01/01/01 CWH Initial Programming
On Error GoTo ErrHandler ShapeExists = False 'Assume not found Dim objName As Object For Each objName In ActiveSheet.Shapes If objName.Name = sName Then ShapeExists = True Exit For End If Next
ErrHandler: If Err.Number <> 0 Then MsgBox _ "ShapeExists - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext On Error GoTo 0
So far we’ve extracted data from a database, populated cells in XL, recorded a macro and written a bit of VBA. Today we address the user interface (UI) by adding something simple like a button. The button will be used to start our macro.
Until now, we started macros by pressing Alt-F8 to bring up the Macro dialog, select the macro we wanted to run, and then, run it. Now that’s not too difficult, but it’s more steps than it needs to be. We can reduce all that to one simple click of the mouse by placing a button on the spreadsheet and associating it with your macro.
To add a button, navigate XL’s menu to Insert > Shapes (or prior to Office 2007 Insert > Picture >AutoShapes) and select any shape you like. I selected a basic rectangle. Your cursor will change to a crosshair. Move the crosshair to the upper left corner of where you’d like your button, then click and drag down and to the right to extend the shape to a desired size. Format it as you like. Next, right click on the shape and select the Assign Macro option. Select Macro 1 and click OK. Now try out your new button. If everything went well, you will see the parameter prompt for the state, and after entering that parameter, data will fill your spreadsheet.
Congratulations! You have literally made pulling data from a database into XL as simple as one click of a mouse.
BONUS! If the office supply store “Staples” is in your area, then you’ll recognize their “easy” button. You can add your own “easy” button from code and change the wording to anything you want. I typically add three buttons to worksheets that update data in the database: “new”, “load”, “post”. But for spreadsheets that only pull data, I use “easy”. See “Code for the “easy” Button” next.
In our last post we looked at running a query that accepts user input to determine the result set loaded into XL. I want to emphasize that although we used an Access database as our source, these same tools and processes work with Oracle, SQL Server, AS/400/iSeries/IBM i/Power i/System i (however you prefer to call it) databases and many others. If you need help connecting to your database, post a comment with what you’re trying to connect to and I’ll try to address it or I’ll point you to good resources.
There are lots of opportunities for improvement on that last post including adding summaries, charts, filters, sorts, etc. The first improvement I’d like to make is to include the clearing of any previous query into Macro 1 and thereby, eliminate one more step. Using the spreadsheet from before (if you didn’t save it, just recreate it using the prior post’s instructions. It only takes a few minutes and it’s good practice), edit Macro 1 (use Alt-F11 to bring up the VBE). Add this line just before the first line of code (but after the Sub Macro1() line):
This line is equivalent to right clicking the “Select All” box and selecting Clear Contents (If you turned on the Macro Recorder and performed those actions you’d get this line of code in a new subroutine). If you’re new to VBA, this will probably look strange to you so let me break it down.
VBA manipulates XL’s objects . One of the most common objects in an XL spreadsheet is a Range. A Range object can contain one or more cells. Cells is a special Range object that contains ALL cells in a spreadsheet. Cells is the XL object in that line of code.
VBA can invoke XL’s object methods. Methods are actions that are predefined and exposed (made available to us) for a given object. To invoke any of an object’s methods, use the syntax: <Object>.<Method>. ClearContents is the Cells’ method in that line of code.
VBA can read XL’s object properties. To read an object’s property into a variable use the syntax: <Variable> = <Object>.<Property>. Example: One of the Cells object’s properties is AllowEdit. AllowEdit indicates if the range can be edited on a protected worksheet. So to read the Cells’ AllowEdit property into variable A, write:
A = Cells.Address
VBA can set some of XL’s object properties. To set an object’s property use the syntax: <Object>.<Property> = <Value>. Example: One of the Cells object’s properties is Value. Value is the contents of Cells. So to set the Cells’ Value property to “A”, write:
Cells.Value = “A”
Include the Cells.ClearContents line in your Macro 1 and then re-run it using Alt-F8 as explained in the prior post and observer the results. Congratulations, you’ve just reduced the amount of steps needed to retrieve different results.
XL makes this insanely easy through use of the Macro Recorder. If you don’t know how to do this, rather than me “reinvent the wheel” so-to-speak, and explain it myself, let’s leverage available resources on the net. At the bottom of this post are references for this. Find the “Recording Macros” reference that is appropriate for your version of XL, view the tutorial and come back here.
Now that you know how to record a macro, start the macro recorder. Now follow the steps in my prior post to start MS Query and collect some data. Once the data is placed into your spreadsheet, stop the macro recorder. Congratulations, you’ve just “written” a macro. Macros can be easily rerun. So let’s prove it to ourselves by clicking the “Select All” box (see the XL Select All picture) and then hit delete to wipe out the data we just downloaded. A message box will warn that you are about to delete a range that contains a query. Click “Yes”. Now use Alt-F8 to bring up the Macro dialog. Click on “Macro 1” then click “Run”. The data returns.
We have reduced 9 simple steps (see previous post) to just 1. While that’s efficient, it’s not useful because all it does is bring back the same data. To make it useful, we need it to respond to our changing needs. In our example we selected California’s records. What if we want to use the same query for a different state? Let’s fix our macro to do just that.
Hit F11 to see the code. Your screen should look something like the picture labeled Visual Basic Editor (VBE). If some of the windows are not displayed, use the menu’s View > Project Explorer (or whichever window is missing) to bring it up. Make sure you see “Module 1” in the Project Explorer (you may have to expand “Modules” to see it). Click “Module 1” to select it. If the code isn’t displayed use the menu’s View > Code to force it to the code window. There are two key pieces of code I want you to study.
The Connection String:
Connection strings in MS Query begin with “ODBC;”. In our example the full string on my PC is:
“ODBC;DSN=MS Access Database;DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;DefaultDir=C:\Users\chatmaker\Documents;DriverId=2”
Connection strings are critical to fully automating data with XL. They are often the most frustrating part. But they are also easily revealed by MS Query. We’ll discuss connection strings in later posts.
The SQL Statement:
Find your SQL statement in the code. Your SQL statement should look similar to:
“SELECT Orders.`Order ID`, Orders.`Customer ID`, Orders.`Order Date`, Customers.`First Name`” & Chr(13) & “” & Chr(10) & “FROM `C:\Users\chatmaker\Documents\Northwind 2007.accdb`.Customers Customers, `C:\Users\chatmaker\Documents”, _
“\Northwind 2007.accdb`.Orders Orders” & Chr(13) & “” & Chr(10) & “WHERE Orders.`Customer ID` = Customers.ID AND ((Customers.`State/Province`=’CA’))”
Change the state at the end of your SQL statement from “’CA’” to “?” so it looks like:
Online VBA Tutorials: www.totorialized.com/tutorials/MS-Excel
Recording Macros Prior to Excel 2007: http://www.youtube.com/watch?v=k_fWcffRAG4
Recording Macros Excel 2007: http://www.youtube.com/watch?v=KKJj78LoFaA
Creating a Query Using Query Wizard Prior to Excel 2007: http://www.youtube.com/watch?v=vkFQM-Fb3-Q
So far we’ve discussed MS Query and its uses. Today we’re going to see just how easy it is to use in 9 simple steps.
Step 1 – Find Data
You probably have a copy of a sample MS Access database called “Northwind” on your PC. We will use it for this exercise. The most fool proof method for finding that database is to use “search” to find “*.mdb” (Prior to Office 2007) or “*.accdb” (Office 2007 version) starting in C:\ and including all subdirectories. If “Search” doesn’t find it, download Northwind from Microsoft (See references below).
Step 2 – Launch MS Query
From XL’s Office 2007 menu: Click Data > From Other Sources > From Microsoft Query
Prior to Office 2007: Click Data > Get External Data (or Import External Data) > New Database Query
Step 3 – Select Database
Make sure “Use the Query Wizard to create/edit queries” is unchecked. Choose “MS Access Database” as your Data Source and click “OK”. Select the Northwind database from the location you identified in step 1.
Step 4 – Add Tables
Double click “Orders” and “Customers” to add the tables to MS Query then close the “Add Tables” dialog. To remove a table from MS Query click on a table and hit delete. To add tables click the “Add Tables” icon (grid with a plus sign) or from the menu select Tables > Add Tables.
Step 5 – Create Joins
In some versions of MS Query, obvious joins are created for you. In our example we need the “ID” field/column from the “Customers” table to be linked to the “Customer ID” field/column in the “Orders” table. To do this, simply click and drag either field to its match in the other table.
Step 6 – Select Fields/Columns
From the “Orders” table double click “Order ID”, “Customer ID”, and “Order Date”. From the “Customers” table double click “First Name”. (You don’t need every field in the picture).
Step 7 – Filter Results
From the menu select Criteria > Add Criteria
Select Field: “Customers.State/Province”; Operator: “equals”; and type “CA” into Value:
Click Add and Close.
Step 8 – View SQL
This isn’t necessary at this point, but I want you to see the SQL MS Query created for you. If you’re new to SQL, this is a great way to overcome any anxiety you might have about learning SQL as you can always use MS Query to build simple SQL statements for you (we will be using them exclusively later on). If you’re an SQL veteran you probably find the code verbose and ugly. You can change the SQL here if you like.
Step 9 – Return Results to XL
Click the “Return to XL” icon (open door with an arrow) or from the menu select: File > Return data to Microsoft Excel.
Congratulations! You’ve just put your data into the most widely accepted, most widely loved, and one of the most powerful database analytical tools available.
References: Northwind Sample Database:
Useful Information: http://www.wilsonmar.com/northwind.htm
So far I’ve talked about how to use MS Query. It’s easy. It’s useful. It’s free. It’s included with every version of XL. It works with just about any database provided you have the (usually free) ODBC driver. Though we won’t be using MS Query in any of the spreadsheets we could deliver to end users – it is still very useful in the hands of the right end users. Here’s an example.
Last week, our B2B (Business to Business) specialist was alerted to a problem. A payment from an important customer was delayed because an EDI 214 (a B2B document that conveys shipment status) failed to reach them. Our B2B team is very good at what they do so this was a big surprise, and though this was the first occurrence, our B2B specialist wasn’t about to let this incident go without a thorough investigation. Her first concern was damage control. She needed to know if there were other transactions like this. She’s not a developer, but she’s not afraid of technology. So using MS Query she connected to our IBM Server’s DB2 database, joined a few files, and quickly determined there was indeed only one transaction that failed to generate the proper 214.
She’s happy because she’s certain the problem is isolated. I’m happy because instead of two people, our B2B Specialist and a developer getting tied up, she was able to handle this quickly on her own using a simple, free tool included in her copy of XL.
Now some DBA’s might be shocked at the idea of letting anyone have SQL access to the database. After all, they could run a query that would bring the machine to its knees, right? Possibly. But in the last 10 years I haven’t seen that happen. Maybe we’ve just been lucky. Maybe our machines are oversized. Maybe our operating systems are smart enough to isolate CPU hogs. On rare occasions, I’ve seen long running, full table scan queries that frustrated the user who submitted them, but I’ve not seen those queries throttle the CPU and choke out other users. If that ever does happen, we know we can quickly kill the rogue session and restore CPU cycles to the masses. In the mean time, we will enjoy productivity improvements and end user job satisfaction increases – at no cost to the company at all.
In my opinion, the risk/benefit ratio for this approach is solidly in the favorable column.
When you start MS Query the first thing it asks for is the “Data Source” name. A “Data Source” name is required. At a minimum, a “Data Source” name points MS Query or ODBC to a driver that understands the application’s standards (such as Oracle, DB2, Access, Excel, or MS SQL Server) that your data conforms to.
If you don’t see your required database in the list, you can create it by selecting <New Data Source> and clicking OK. That brings up the “Create New Data Source” dialog.
Step 2 is perhaps the most daunting. Sometimes there are more than one driver listed that support your database. In that case, it usually doesn’t much matter which one you choose. Sometimes there are no drivers that support your database. In that case, you need to load that database’s ODBC driver on your PC.
If you don’t know where to find your ODBC driver, first contact your DBA or Systems Administrator. If they don’t have it, search the database provider’s website under product support for driver downloads. If it’s not there, contact the database provider’s sales or support team. If all else fails, Google your database name with “ODBC driver” to see if someone else provides it. I have found a few old, non-relational databases with third party ODBC drivers for sale; but, the dominant database providers don’t charge. Microsoft includes theirs and several once prominent PC databases with their operating systems. Oracle’s is freely available from their website. IBM’s comes with their PC client or server CDs. Several powerful free databases such as mySQL give the driver away.