|
To help readers follow the
instructions in this
article, we used 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.
| |
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
. |