Beyond Excel: VBA and Database Manipulation

Sep 10 2009   7:28AM GMT

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.

[kml_flashembed movie="" width="480" height="292" wmode="transparent" /]
Click here for northwind database download: Northwind

Next Post: Data Sources

 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.

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:

Share this item with your network: