In our last post we looked at running a query that accepts user input to determine the result set loaded into XL. I want to emphasize that although we used an Access database as our source, these same tools and processes work with Oracle, SQL Server, AS/400/iSeries/IBM i/Power i/System i (however you prefer to call it) databases and many others. If you need help connecting to your database, post a comment with what you’re trying to connect to and I’ll try to address it or I’ll point you to good resources.
There are lots of opportunities for improvement on that last post including adding summaries, charts, filters, sorts, etc. The first improvement I’d like to make is to include the clearing of any previous query into Macro 1 and thereby, eliminate one more step. Using the spreadsheet from before (if you didn’t save it, just recreate it using the prior post’s instructions. It only takes a few minutes and it’s good practice), edit Macro 1 (use Alt-F11 to bring up the VBE). Add this line just before the first line of code (but after the Sub Macro1() line):
This line is equivalent to right clicking the “Select All” box and selecting Clear Contents (If you turned on the Macro Recorder and performed those actions you’d get this line of code in a new subroutine). If you’re new to VBA, this will probably look strange to you so let me break it down.
VBA manipulates XL’s objects . One of the most common objects in an XL spreadsheet is a Range. A Range object can contain one or more cells. Cells is a special Range object that contains ALL cells in a spreadsheet. Cells is the XL object in that line of code.
VBA can invoke XL’s object methods. Methods are actions that are predefined and exposed (made available to us) for a given object. To invoke any of an object’s methods, use the syntax: <Object>.<Method>. ClearContents is the Cells’ method in that line of code.
VBA can read XL’s object properties. To read an object’s property into a variable use the syntax: <Variable> = <Object>.<Property>. Example: One of the Cells object’s properties is AllowEdit. AllowEdit indicates if the range can be edited on a protected worksheet. So to read the Cells’ AllowEdit property into variable A, write:
A = Cells.Address
VBA can set some of XL’s object properties. To set an object’s property use the syntax: <Object>.<Property> = <Value>. Example: One of the Cells object’s properties is Value. Value is the contents of Cells. So to set the Cells’ Value property to “A”, write:
Cells.Value = “A”
Include the Cells.ClearContents line in your Macro 1 and then re-run it using Alt-F8 as explained in the prior post and observer the results. Congratulations, you’ve just reduced the amount of steps needed to retrieve different results.
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.
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.
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:
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
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.
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:
Useful Information: http://www.wilsonmar.com/northwind.htm
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.
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.
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.
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.
Unfortunately, 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.
[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
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.