Automating Queries

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.

XL Select All Box

Welcome back!
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.

Visual Basic Editor

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:

((Customers.`State/Province`=?))