Beyond Excel: VBA and Database Manipulation


September 24, 2009  4:18 PM

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

September 22, 2009  9:06 AM

Creating a Query

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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.

MSQuery

MSQuery

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:
Location: http://support.microsoft.com/kb/824265
Download: http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN
Useful Information: http://www.wilsonmar.com/northwind.htm


September 18, 2009  3:53 PM

MS Query – Example Use

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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.


September 17, 2009  6:44 AM

Data Sources

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
In my last post, I shared how you can explore just about any database using MS Query, a useful, friendly, and free tool included with every version of XL.  To use it with your database, you must point it to the appropriate data source. 
Choose Data Source

Choose Data Source

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. 

Create Data Source

Create Data Source

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.


September 10, 2009  7:28 AM

Manually Linking to Data – MS Query

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Before tackling automated methods for incorporating data into XL, we should look at a surprisingly useful and free component included with every version of XL, MS Query.  MS Query provides a graphical interface into databases.  You can use it to explore database tables, examine columns and contents, join tables, filter tables, sort tables, reveal the SQL statement built behind the scenes, and load results into XL.

MSQueryUnfortunately, MS Query is not always installed (Use XL’s Help and search for Install Query, or see Installing Microsoft Query) so you may have to find your XL installation disks for this one.  When we get to delivering applications to end users we won’t use MS Query, we will only use SQL.  So it doesn’t matter if MS Query is installed on their machines.  Having it on your machine helps you determine what information you need in your report or application and what the SQL needs to be.  If you’re new to SQL, you will find it immensely useful in learning how to join two or more tables and how to deal with certain field types such as dates.

MS Query is found (assuming it has been installed):

Office 2000: Under the “Data” menu, “Get External Data” option, select “New Database Query” sub-menu option.

Office 2002: Under the “Data” menu, “Import External Data” option, select “New Database Query” sub-menu option.

Office 2007: On the “Data” tab, in the “Get External Data” group, click “From Other Sources” icon, and select the “From Microsoft Query” option.

NOTE: If you’re using short menus, you may need to click the menu’s down arrows to see these options.

Before MS Query starts it asks you to select a data source and that will be the topic of our next post.

Preview:
[kml_flashembed movie="http://www.youtube.com/v/I1qvqMiGyhc" width="480" height="292" wmode="transparent" /]
Click here for northwind database download: Northwind 2003.zip

Next Post: Data Sources


September 10, 2009  6:59 AM

Forward

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Everyone knows Microsoft Excel is a powerful database manipulation engine, but data lives in databases and only a few Excel experts know how to exploit this natural synergy.

If you are one of tens of thousands of business developers asked each day to:

  • Produce reports from systems such as your ERP, EAM, HRIS, CPFR, CRM, TMS, etc.
  • Write small applications such as Time and Attendance Entry, Help Desk Requests and Time Tracking, PC Inventories, etc.
  • Create data extracts and transformations for system interfaces -

-you can exploit Excel to deliver paperless, interactive, graphical reports in minutes not hours or days and write small applications in hours, not days or weeks.  You will exceed your customers’ expectations.  You will save your customers time.  They will realize benefits sooner.

As developers our value is in the worth of the tools we provide.  When our customers get an idea for a tool to help them in their work, the last thing they want is to wait for it.  They want it delivered now.  They want to be able to use it now.  They want it to be flexible.  They want it to be graphical.  Microsoft Excel is often a great means to meet their ends.

Your customers already have XL so there’s no procurement cycle.  They already know XL so there’s no learning curve.  They already love XL so there’s no adoption hurdle.  All of this is valuable to your customers.  What’s left is for you to provide what only developers can – code.

Coding is essential to this process.  There are no end user based solutions here other than your final product.  Your skills are required.  Your skills need to include VBA (the macro language included free in just about all Microsoft applications).  If you don’t know VBA, don’t worry.  You won’t find it difficult and the examples posted here will make it easy.  If you don’t know SQL, don’t admit it.  It’s easy.  It’s powerful.  It’s standard.  It’s everywhere.  And there are many excellent, free resources on the web.  As we go, I will point out resources I use.  Perhaps others will share their favorites as well.

If you and your customers own Microsoft Excel (and who doesn’t these days) and your database provider included an ODBC driver (the most prevalent in the market do) then you are good to go.  There is absolutely nothing else to buy.

Everyone knows Microsoft Excel is a powerful database manipulation engine, but data lives in databases and only a few Excel experts know how to exploit this natural synergy.  Soon – you will be one of them.

Preview:
[kml_flashembed movie="http://www.youtube.com/v/G0gQcN76I_E" width="480" height="385" wmode="transparent" /]

 

Code Reference Table
Function or Subroutine Module/Class Located in Post
Add_XLFormula modGeneral Adding Excel Formulas – Part II
All Code and Properties for … frmSelect_Multiple Searching for Codes
All Code and Properties for … frmSelect Check Entry – Form Select – Theory and Demo
All Code and Properties for… frmPrompt Asking for It
All Code and Properties for… frmDatePicker Looking for a Date?
Build_SQL_Group_By modGeneral Group By and Aggregate SQL Functions
Build_SQL_ID modGeneral It’s Time to Play
Build_SQL_Insert_Fields modSQL Building a Library of Routines for Updating – #3
Build_SQL_Insert_Values modSQL Building a Library of Routines for Updating – #3
Build_SQL_Select_Fields modGeneral Adding Tables and Columns to the Fields Table
Build_SQL_Update_Values modSQL Building a Library of Routines for Updating – #2
Build_SQL_UpdDlt_Where_Clause modSQL Building a Library of Routines for Updating – #2
Cell_Checked modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Error modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Lock modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Unchecked modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Unlock modTableUpdate Check Entry – Marking Cells as having passed or failed
Chars_Last_Position modGeneral Building a Library of Routines for Updating – #4
ChartExists modGeneral Building a Library of Routines – ?Exists
Check_Entry Sheet1 Check Entry – The Controlling Routine
Check_For_Normal_Entry_Errors modTableUpdate Check Entry – Common Validations
Create_Easy_Button modGeneral Code for “easy” Button
Create_Named_Range modGeneral Creating the Fields Table
Cust_Edit Sheet1 Check Entry – Dealing with Field Level Exceptions
Date2Julian modGeneral Building a Library of Routines for Updating – #1
DB_Lookup modTableUpdate Check Entry – DB_Lookup
Field Definitions Table for Updates   Additions to the Fields Table for Update Spreadsheets
FieldColumn modGeneral Adding Tables and Columns to the Fields Table
Fields_Field_Column modTableLoad Check Entry – Marking Cells as having passed or failed
Fix_Name modGeneral Creating the Fields Table
Format_Results modGeneral Setting Column Widths or Hiding Them
Freeze_Pane modGeneral Preserving Column Headers and Key Values
Get_Data Sheet1 Drill Down Part 2
Get_Range_Dimensions modTableLoad Check Entry – The Controlling Routine
Initialize_Globals modTableLoad Check Entry – Initializing Globals
Julian2Date modGeneral Building a Library of Routines for Updating – #1
NameExists modGeneral Building a Library of Routines – ?Exists
Parse_XLFormula modGeneral Adding Excel Formulas – Part II
Pivot_Template modGeneral Wrapping things up
PivotTableExists modGeneral Building a Library of Routines – ?Exists
Post modTableUpdate Building a Library of Routines for Updating – #7
Post_Click Sheet1 Check Entry – Post_Click Event
Prompt Sheet1 Drill Down Part 2
Settings modGeneral Building a Library of Routines – Settings
Setup_Pivot modGeneral Building a Library of Routines – Setup_Pivot
Setup_PivotChart modGeneral Building a Library of Routines – Setup_PivotChart
ShapeExists modGeneral Building a Library of Routines – ?Exists
Sort_Data modGeneral Adding Sorting
SQL_Add_Update_Functions modSQL Building a Library of Routines for Updating – #1
SQLConnection modSQL Building a Library of Routines for Updating – #6
SQLLoad modGeneral Say Goodbye to QueryTables
SQLRead modGeneral Using ADO to Read a Database
Table_Lookup modGeneral Check Entry – Table Lookup
Table_Lookup_by_Name modGeneral Check Entry – Table Lookup
Update_Entries modTableUpdate Building a Library of Routines for Updating – #6
Update_Entry modTableUpdate Building a Library of Routines for Updating – #5
Worksheet_Activate Sheet1 Check Entry – Initializing Globals
Worksheet_BeforeDoubleClick Sheet1 Drill Down Part 2
Worksheet_Change Sheet1 Check Entry – Worksheet_Change – Code
WorkSheetExists modGeneral Building a Library of Routines – ?Exists
XL_Lookup modGeneral Check Entry – XL_Lookup & Dynamic Arguments


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: