Working with Database Data and Macros

Advanced Training for Microsoft Office Excel 2003

Locating and Retrieving Data from a Database

1	To select a range of cells, click the Tools sheet tab, click cell A4 and drag to cell C13.
2	To assign a name to the cell range, click in the Name box, and for the purposes of this exercise, press SPACEBAR to have the name typed for you, and then press ENTER.
3	To add the VLOOKUP function to your worksheet, click cell E6, press SPACEBAR to have the function typed for you, and then press ENTER.
4	Notice that because there is no value in cell E4, the #N/A error code is displayed in cell E6. To search the range for a product ID, click cell E4, press SPACEBAR to have the product ID typed for you, and press ENTER.
5	Notice that Nutcracker, which is the product name of TL0038, is displayed in cell E6. To search the range for another product ID, click cell E4, press SPACEBAR to have the product ID typed for you, and press ENTER.
6	Notice that Timer, Watering, which is the product name of TL0210, is displayed in cell E6. To save your changes, on the Standard toolbar, click the Save button.
7	To open ProductInfo.xls, on the Standard toolbar, click the Open button, double-click the Products folder, click ProductInfo and then click Open.
8	To begin defining a data source from which data needs to be imported, click the Data menu, point to Import External Data, and then click New Database Query.
9	To begin creating a new data source, verify that New Data Source is selected, and then click OK.
10	To assign a name to the data source, in the Create New Data Source dialog box, verify that the insertion point is visible in the first text box, and then press SPACEBAR to have the text typed for you.
11	To select a driver for the type of database you need to access, click the list arrow of the second box, and then click *Microsoft Access Driver (.mdb).**
12	Notice that the Connect button is now active. To select the target database, click the Connect button.
13	To begin selecting the target database, in the ODBC Microsoft Access Setup dialog box, in the Database area, click the Select button.
14	To select the folder containing the target database, in the Select Database dialog box, in the Directories list, click Products, and then click OK.
15	To select the target database, in the Database Name list, click Products.mdb, click OK, and in the ODBC Microsoft Access Setup dialog box, click OK.
16	To assign Products as the default table for this data source, in the Create New Data Source dialog box, click the list arrow of the text box next to 4, click Products, and then click OK.
17	In the Choose Data Source dialog box, verify that Product Information is listed as a new data source. To accept the changes, click OK.
18	To add the three available columns of the Products table to the query, on the Choose Columns page of the Query Wizard, click the > button.
19	To move to the next page of the wizard, click Next.
20	To begin filtering the Price column, on the Filter Data page of the Query Wizard, in the Column to filter list, click Price.
21	In the Only include rows where area, notice that the first comparison operator box is now active. To assign a comparison, click the list arrow of the first comparison operator box, and click is greater than or equal to.
22	Notice that the first value box becomes active. To assign a value for comparison, press SPACEBAR to have the value typed for you, and to move to the next page of the wizard, click Next.
23	To sort by the product ID, on the Sort Order page of the Query Wizard, click the Sort by list arrow, and then click ProductID.
24	Notice that the Ascending and Descending options and the Then by box are now active, and the Ascending option is selected by default. To accept the default settings and move to the final page of the wizard, click Next.
25	On the Finish page of the Query Wizard, notice that the Return Data to Microsoft Office Excel option is selected by default. To begin saving the query, click the Save Query button.
26	To assign a name to the query, press SPACEBAR to have the name typed for you in the File name box, and then click Save.
27	To close the wizard and finish applying the query, click Finish.
28	To display the query results in the current worksheet, in the Import Data dialog box, click OK.
29	Notice that the worksheet has been updated with the desired query results. To save your changes, on the Standard toolbar, click the Save button.

Summarizing List Data

1	To select the data to create an outline, click cell B3, hold down the SHIFT key, and click cell D4.
2	To create an outline, click the Data menu, point to Group and Outline, and then click Group.
3	To specify how to group the data, in the Group dialog box, verify that Rows is selected, and then click OK.
4	Notice that the Hide Detail button is displayed, as indicated. To hide the detail rows for March, click the Hide Details button.
5	Notice that the View Details button is displayed, as indicated, along with the total sales for March. To begin ungrouping the data, click the View Details button.
6	To ungroup the March sales data, click the Data menu, point to Group and Outline, and then click Ungroup.
7	In the Ungroup dialog box, verify that Rows is selected, and then click OK.
8	To begin summarizing the sales data, click the AllData sheet tab, click cell G1, and press SPACEBAR to have the text typed for you.
9	To begin defining the fields for summarization, click cell A1, hold down the SHIFT key, click cell G1, and then on the Standard toolbar, click the Copy button, as indicated.
10	To define another set of fields, click cell A3, and then on the Standard toolbar, click the Paste button, as indicated.
11	To define the fields for summarization, click cell A2, and press SPACEBAR to have the text typed for you.
12	To continue defining fields for summarization, click cell C2, and press SPACEBAR to have the text typed for you.
13	To define multi-part criteria, click cell E2, and press SPACEBAR to have the text typed for you.
14	To specify a summarization field, click cell H9, press SPACEBAR to have the text typed for you, and then press ENTER.
15	To specify a summarization field, click cell J9, press SPACEBAR to have the text typed for you, and then press ENTER.
16	To use the DSUM function to calculate the total sales for Tuesday between 1 P.M. and 4 P.M., click cell I9, press SPACEBAR to have the value typed for you, and then press ENTER.
17	To use the DCOUNT function to count the number of May hours in week 2 with sales over $200, press SPACEBAR to have the value typed for you, and then press ENTER.
18	To use the DMAX function to view the highest hourly sales in March, press SPACEBAR to have the value typed for you, and then press ENTER.
19	To use the DAVERAGE function to view the sales average in March, click cell K9, press SPACEBAR to have the value typed for you, and then press ENTER.
20	To use the DGET function to view the sales greater than $800 in March, press SPACEBAR to have the value typed for you, and then press ENTER.
21	To save your changes, on the Standard toolbar, click the Save button.

Introducing Macros

1	To change the current security settings, click the Tools menu, point to Macro, and then click Security.
2	To set the Medium macro security level, in the Security dialog box, on the Security Level tab, select the Medium option, and then click OK.
3	To begin opening a workbook that contains macros, on the Standard toolbar, click the Open button.
4	To open the MarchSales workbook, double-click the Sales folder, click MarchSales, and then click Open.
5	To run the macros in the workbook, in the Security Warning box, click Enable Macros.
6	To view a list of macros in the workbook, click the Tools menu, point to Macro, and then click Macros.
7	To view the code for the Highlight macro, in the Macro name list, verify that Highlight is selected, and then click Edit.
8	Notice that the macro code is displayed in the Microsoft Visual Basic Editor window. To close the editor and display the worksheet again, click the Close button of the Microsoft Visual Basic window.
9	To display the list of macros again, click the Tools menu, point to Macro and then click Macros.
10	To see how a macro works, in the Macro name list, verify that Highlight is selected, and then click Step Into.
11	Notice that the command that will be executed next is highlighted within the code. To display the code of all macros in the worksheet together, click the Window menu and then click Tile Vertically.
12	To execute the highlighted instruction, click the Debug menu, and then click Step Into.
13	Notice that the next instruction is highlighted. To execute this instruction, click the Debug menu and then click Step Into.
14	Notice that the next instruction is highlighted. To execute this instruction, click the Debug menu and then click Step Into.
15	To close the Microsoft Visual Basic Editor and view the changes in the worksheet, click the Close button of the Microsoft Visual Basic window, and then in the Microsoft Visual Basic message box, click OK.
16	Notice that the contents of the cell E6 are displayed in bold formatting. To display the list of macros again, click the Tools menu, point to Macro and then click Macros.
17	To run the macro without stopping after each instruction, in the Macro dialog box, in the Macro name section, verify that Highlight is selected, and then click Run.
18	Notice the result of running the macro in the worksheet. To save your changes, on the Standard toolbar, click the Save button.

Creating and Modifying Macros

1	To open a workbook that contains macros, on the Standard toolbar, click the Open button, double-click the Sales folder, click MarchSales, and then click Open.
2	To run the macros in the workbook, in the Security Warning box, click Enable Macros.
3	To begin creating a new macro, click the Tools menu, point to Macro, and then click Record New Macro.
4	To specify a name for the new macro, for the purposes of this exercise, press SPACEBAR to have the name typed for you in the Macro name box of the Record Macro dialog box, and then click OK.
5	To begin recording the series of steps for removing the bold formatting in the worksheet, click cell E6, and then on the Formatting toolbar, click the Bold button.
6	To perform another action, click cell E11, and then on the Formatting toolbar, click the Bold button.
7	To perform another action, click cell E18, and then on the Formatting toolbar, click the Bold button.
8	To perform another action, click cell F8, and then on the Formatting toolbar, click the Bold button.
9	To finish performing the actions you need to repeat later and to add your macro to the list of available macros, on the Stop Recording toolbar, click the Stop Recording button, as indicated.
10	Notice that the contents of cell F17 are bold. To begin modifying the macro to remove the bold formatting in F17, click the Tools menu, point to Macro and then click Macros.
11	To begin modifying the RemoveHighlight macro by using the Microsoft Visual Basic Editor, in the Macro name list of the Macro dialog box, click RemoveHighlight, and then click Edit.
12	To select cell F17, click once below the scroll box, click at the end of the line just above End Sub, press ENTER, and then press SPACEBAR to have the statement typed for you.
13	To remove the bold formatting for cell F17, press ENTER, press SPACEBAR to have the statement typed for you, and press ENTER.
14	To save your changes, on the Standard toolbar of the Microsoft Visual Basic Editor, click the Save button.
15	To close the Microsoft Visual Basic Editor, click the Close button of the Microsoft Visual Basic window.
16	To open the Macro dialog box, click the Tools menu, point to Macro and then click Macros.
17	To apply bold formatting to the cells, in the Macro name area of the Macro dialog box, verify that Highlight is selected, and then click Run.
18	Notice that the contents of cells E6, E11, E18, F8, and F17 are displayed in bold format as a result of the Highlight macro. To begin testing the macro you have created, click the Tools menu, point to Macro, and then click Macros.
19	To run the macro, in the Macro name area of the Macro dialog box, click RemoveHighlight, and then click Run.
20	Notice that the bold formatting in cells E6, E11, E18, F8, and F17 has been removed as a result of the RemoveHighlight macro. To begin deleting a macro, click the Tools menu, point to Macro, and then click Macros.
21	To delete a macro, click QuickHighlight, click Delete, and then click Yes in the Microsoft Excel message box.
22	To save your changes, on the Standard toolbar, click the Save button.