Beyond Excel: VBA and Database Manipulation

Sep 24 2009   4:18PM GMT

Automating Queries

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

We’ve been looking at using MS Query from XL to get at data stored on mainframes, servers, or you PC.  Hopefully you have experimented with MS Query.  MS Query represents what I refer to as a “manual method” because it involves no coding.  So let’s look at how we can automate what we’ve learned so far.

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

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

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`=?))

The “?” tells Query to ask for this value.  Hit Alt-F11 again to return to XL (or click the little XL icon in the upper left corner of the VBE tool bar).  Click the Select All box and delete everything again.  Hit Alt-F8 to bring up the Macro dialog and run Macro 1 just as you did before.  This time the “Enter Parameter Value” dialog box appears.  Enter “NY” (not the quotes) and click OK (or press Enter).  New data appears.  Now we’re getting somewhere!

References:
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

 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: