Beyond Excel: VBA and Database Manipulation

Oct 10 2009   11:11PM GMT

Brain Surgery



Posted by: Craig Hatmaker
Tags:
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:

Sub Macro1()
'   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.

For more information on the LIKE keyword see: http://www.w3schools.com/sql/sql_like.asp
For more information about wildcards used in LIKE see: http://www.w3schools.com/sql/sql_wildcards.asp

Add Formatting
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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: