Click ... and the database loads into Excel: an easy way to import data into a spreadsheet.

If you're like most CPAs, you're more proficient with an Excel spreadsheet than a database. So when you're faced with the challenge of analyzing database information, you probably reluctantly bite the bullet and go through the tortuous steps of converting and importing it into Excel. Say good-bye to all that. We'll show you how to do the whole job with just a single mouse click.

Although we'll demonstrate the process with Microsoft Access, you can use the same basic technique when converting from any ODBC (Open Database Connectivity) driver, including dBase, Visual FoxPro, Oracle and SQL Server. In addition, you'll be able to download a database's table, parts of a table or parts of a query. If you want to follow along as we proceed, download our demonstration Access file from www.aicpa.org/pubs/jofa/ sep2006/mahoney.mdb.

JUST A CLICK

Before you can automate the process, you'll need to go through the manual process just one time. Start by opening a new Excel workbook and clicking on the cell where you want the database information you will be downloading to begin. Then click on Data, Import External Data (exhibit 1, below) and select New Database Query.

[ILLUSTRATION OMITTED]

If a box pops up with the message Microsoft Excel can't start MSQuery. This feature is not installed, would you like to install it now? click on Yes and follow the screen instructions.

Once you install MSQuery, again click on Data, Import External Data, New Database Query to open the Choose Data Source dialog box (exhibit 2, page 54).

[ILLUSTRATION OMITTED]

Click on the Databases tab and select the format of your database. Our demo uses MS Access Database. If your database product doesn't appear, click on Browse to locate it on your computer and then click on OK, engaging the Select Database dialog box (exhibit 3, page 54). If you need to create a data source from a database that is not listed in this tab, click on <New Data Source> and follow the instructions.

[ILLUSTRATION OMITTED]

Navigate to the location of the target database (under Directories: c:\) and then, under Database Name, click on demonstration.mdb and on OK to open the Query Wizard--Choose Columns dialog box (exhibit 4, page 54).

[ILLUSTRATION OMITTED]

Select the information you want in the Query Wizard--Choose Columns dialog box by scrolling down the Available To help readers follow the instructions in this article, we use two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer tables and columns box. If you can't find the data, then click on Options to reveal the Table Options dialog box (exhibit 5, below).

[ILLUSTRATION OMITTED]

Then click in the Views checkbox (queries are called Views in Access) and click on OK. Now you should see both tables and queries in the Available tables and columns box. Click on what you want to download. In our example, the information we want is qrySalesbyCustomer. Clicking on its > button transfers all available fields to the Columns in your query box. If you don't want to download all the fields in the query or table, click on the + (plus) box to the left of the query or table and all the fields will appear. Click on each field you want, one at a time, and then click on the > button. When done, click on Next and the Query Wizard--Filter Data dialog box will appear (exhibit 6, below).

[ILLUSTRATION OMITTED]

There you can select criteria for table/query records by clicking on your choice in the Column to filter box and then select an option under Only include rows where. We won't do a filter in our example so click on Next, opening the Query Wizard--Sort Order box (exhibit 7, page 55). Choose the name of the column(s) you want to sort in the Sort by box and click on either the Ascending or Descending button. In our example we want to sort the data in ascending order by the LastName column.

[ILLUSTRATION OMITTED]

Finally, click on Next, opening the Query Wizard--Finish dialog box (exhibit 8, below).

[ILLUSTRATION OMITTED]

Accept the default option button, Return Data to Microsoft Office Excel, and click on Save Query, which opens the Import Data dialog box (exhibit 9, below).

[ILLUSTRATION OMITTED]

Choose the Excel worksheet and the cell in which you want the data displayed. In this example we accept the default position of Sheet cell A1 and click on OK.

Your data are now in your Excel spreadsheet as shown in exhibit 10, below. If you want to return the data to a different location in that worksheet, type the cell address in the text box. If you want to import to another worksheet, click on New worksheet.

[ILLUSTRATION OMITTED]

GOING ON AUTO

Now we're ready to automate the process with a macro. Put your cursor inside the contents of the database query in your spread sheet; that should cause the External Data toolbar to pop up (exhibit 11, page 56).

[ILLUSTRATION OMITTED]

If it doesn't appear, click on View, Toolbars and External Data (exhibit 12, page 56).

[ILLUSTRATION OMITTED]

Now let's record the macro. Select an Excel cell on the same sheet outside the database query range--such as H 10. (If you fail to do this, and your cursor happens to be inside the data range, the macro won't work properly) Then turn on the macro recorder by clicking on Tools, Macro and Record New Macro, engaging the Record Macro dialog box (exhibit 13, page 56).

[ILLUSTRATION OMITTED]

Select a name (AutomaticUpdate) for the macro and type it in the Macro Name box and click on OK. That should engage the Stop Recording toolbar. Don't worry if it doesn't; there are others ways, which we'll describe later, to turn off the recording.

Select a cell in the database query range, such as BS, and click on the Refresh button (orange exclamation point) on the External Data toolbar (exhibit 11) and on the Stop button on the Stop Recording toolbar. If the toolbar doesn't pop up, do one of the following: Select either Tools, Macro, Stop Recording or View, Toolbars and Stop Recording. Then click on Stop.

ALMOST DONE

Now that we've recorded a macro, we need to attach it to a button in the spreadsheet. Though any type of button will work, we'll add one from Excel's Forms toolbar. Click on View, Toolbars, Forms (exhibit 14, below). Left-click on the button (it's circled) and then release it.

[ILLUSTRATION OMITTED]

Now move your cursor to where you want to place the button, left-click and hold down the mouse button as you draw the button icon to the size you want and release the mouse button. The Assign Macro dialog box (exhibit 15, below) will pop up. Click on the name of the macro you created (in this case AutomaticUpdate) and on OK. You have successfully attached the macro to the button. If you want to change the default label shown on the button, right-click over the button and edit the text label.

[ILLUSTRATION OMITTED]

Finally, to test the macro, go back to your original database and add some records. Then return to your spreadsheet and click on the new button. If you followed all the steps, the new records, as well as the old ones, will appear in your spreadsheet automatically

Although it took many setup steps, now you can save loads of time by updating your spreadsheet with new database information with just one click.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs.

Sans Serif type shows the names of files and the names of commands and instructions that users should type into the computer.

Lois S. Mahoney, CPA, PhD, CMA, is an assistant professor at Eastern Michigan University, Ypsilanti. Her e-mail address is [e-mail]. Judith K. Welch, CPA, PhD, is an associate professor at the University of Central Florida in Orlando. Her e-mail address is [e-mail].