Exercise 1: Setting up a Data Connection

Out of the box, Microsoft Excel contains numerous features that allow you to connect Excel to external data. For many reporting scenarios it is often possible to use a combination of data connections with lookup formulas and data validation to create surprisingly interactive workbooks without writing any code.

Exercise 1 is designed to allow you to begin exploring a technique for accessing external data in Excel.

Task 1 – Accessing External Data in Excel

1. Open a new workbook in Excel
2. Click on the Data tab
3. In the Get External Data group click From Access
4. Open the database named Budget.accdb located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database
5. Select the Stores table and click OK
6. Click OK. Notice that the data appears in a table in Excel.

7. On the Table Tools Design tab of the ribbon, click on Properties to display the External Data Properties dialog box. Explore some of the properties you can modify.

   Figure 1

   External Data Properties

8. Click on the Connection Properties button in the upper right quadrant of the dialog box. This will display the Connection Properties dialog box. Explore some of the connection properties that you can modify.

   Figure 2

   Connection Properties

9. Click on the Definition tab and explore the properties that you can modify.
10. Change the value of the Command text from Stores to Accounts.
11. Click OK to close the Connection Properties and then click OK again to close the External Data Properties dialog. Notice that Excel displays the Accounts table now.
12. On the Table Tools Design tab of the ribbon, click Properties
13. Click on the Connection Properties button
14. Click on the Definition tab
15. Change the Command type to SQL
16. Set the Command text to SELECT * FROM [FACTS CROSSTAB]
17. WHERE [STORE NAME]='Warehouse District Store'
18. AND [FISCAL YEAR]=2009
19. Click OK to close the Connection Properties and then click OK again to close the External Data Properties dialog. Observe that Excel displays information from the query you specified.
20. Notice that the table functionality in Excel allows you to easily sort and filter the information. For example, click on the drop-down arrow in the Scenario Type column heading.
21. Uncheck Budget and click OK. Notice that Excel filters the records for you.
22. Close the Workbook, don’t save it.