Manually Linking to Data – MS Query

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.

Next Post: Data Sources

Forward

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.