Click…and the Database Loads Into Excel.

f 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 ) and select New Database Query .

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 ).

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 ). 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.

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 ).

Select the information you want in the Query Wizard – Choose Columns dialog box by scrolling down the Available tables and columns box. If you can’t find the data, then click on Options to reveal the Table Options dialog box ( exhibit 5 ).

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 ).

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 ). 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.

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

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 ).

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

Your data are now in your Excel spreadsheet as shown in exhibit 10 . 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 .

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 spreadsheet; that should cause the External Data toolbar to pop up ( exhibit 11 ).

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

Now let’s record the macro. Select an Excel cell on the same sheet outside the database query range—such as H10 . (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 ).

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 B5 , 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 ). Left-click on the button (it’s circled) and then release it.

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 ) 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.

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.

Lois S. Mahoney, CPA, PhD, CMA, is an assistant professor at Eastern Michigan University, Ypsilanti. Her e-mail address is lois.mahoney@emich.edu . Judith K. Welch, CPA, PhD, is an associate professor at the University of Central Florida in Orlando. Her e-mail address is jwelch@mail.ucf.edu .