Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
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.