Hands-On Lab
Core Office Solution Development
Lab version: 1.0.1
Last updated: 4/8/2011
Contents
Overview 4
Exercise 1: Setting up a Data Connection 5
Task 1 – Accessing External Data in Excel 6
Exercise 2: Creating and using Parameterized Queries in Excel 8
Task 1 – Defining the Data Source 8
Task 2 – Building the Query 9
Task 3 – Hook up the query parameters to cells on the worksheet 12
Exercise 2 Verification 13
Exercise 3: Create a No-code, Data-bound Interactive Display in Excel 14
Task 1 – Add Formulas to the Forecast worksheet 14
Task 2 – Add Interactivity to the worksheet 15
Exercise 3 Verification 17
Exercise 4: Introduction to VBA 17
Task 1 – Connect the Forecasting workbook to the Stores table in the Forecasting Database 18
Task 2 – Add a mechanism for filtering Store information 19
Task 3 – Name ranges so they can be referenced easily in VBA 21
Task 4 – Create the User Form 21
Task 5 – Provide a way to display the User Form 26
Exercise 4 Verification 27
Exercise 5: Using VBA with the Excel Object Model 28
Task 1 – Unlock Parameter Cells on Forecast Worksheet 29
Task 2 – Name key ranges 30
Task 3 – Add code to dynamically lock/unlock cells 31
Task 4 – Add a feature to reset the worksheet formulas 32
Exercise 5 Verification 33
Exercise 6: Document Assembly with Word Content Controls and VBA 34
Task 1 – Add Content Controls to the Document 34
Task 2 – Add VBA to populate the Content Controls 37
Exercise 6 Verification 39
Summary 43
Overview
By combining the power and flexibility of core Microsoft Office applications with a little bit of programming it is possible to develop solutions quickly and easily. In this HOL you will receive an introduction to Office development.
Objectives
The objective of this Hands-On Lab is to introduce you to core solution development techniques for Office 2010. In particular, you will
· Learn how to connect Microsoft Excel to external data sources
· See how to use Microsoft Query to create more sophisticated data connections
· Create a codeless interactive data analysis solution in Excel
· Receive an introduction to the Visual Basic Editor and VBA to create a user form that interacts with an Excel workbook
· Use VBA with the Excel Object Model
· Experience document assembly using Word content controls and VBA
System Requirements
You must have the following items to complete this lab:
· Microsoft Access 2010
· Microsoft Excel 2010
Setup
This Hands-On lab assumes that the Hands-On lab files are located in a folder named %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source on the drive you downloaded to. If you haven’t already done so, perform the following steps
1. Open Windows Explorer
2. Navigate to the Source folder for this lab
Exercises
This Hands-On Lab comprises the following exercises:
1. Setting up a Data Connection in Excel
2. Creating and using Parameterized Queries in Excel
3. Create a no-code, data-bound interactive display in Excel
4. Use VBA to create a user form
5. Using VBA with the Excel Object Model
6. Document assembly with Word content controls and VBA
Estimated time to complete this lab: 75 minutes.
Starting Materials
This Hands-On Lab includes the following starting materials.
· Access Database. The lab provides the database that you can use as starting point for the exercises.
◦ %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database\Budget.accdb: The Budget database contains sample budgeting and forecasting information that is accessed by this labs exercises.
· Forecasting Workbook. The lab provides a workbook to use as a starting point for the Exercises 2 and 3.
◦ %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files\Forecasting.xlsm: The Budget database contains sample budgeting and forecasting information that is accessed by this labs exercises.
Note: See the Completed Files folder for a completed example of the Forecasting workbook.
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]
WHERE [STORE NAME]='Warehouse District Store'
AND [FISCAL YEAR]=2009
17. 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.
18. 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.
19. Uncheck Budget and click OK. Notice that Excel filters the records for you.
20. Close the Workbook, don’t save it.
Exercise 2: Creating and using Parameterized Queries in Excel
It is amazing how many scenarios you can cover using simple data connections such as the one created in Exercise 1. Combined with PivotTables, this is a powerful way to approach ad-hoc data analysis. For those times when you need a more structured approach, more control over the presentation of the data, or when you are working with more complex database structures, you can also employ the capabilities of Microsoft Query. Query has been around for quite some time offers some great capabilities, the most compelling of which is the ability to create parameterized queries. In this section, you’ll learn how to use Microsoft Query to create parameterized queries.
Task 1 – Defining the Data Source
When accessing data with Microsoft Query, you need to have a data source defined for the data source you wish to access. Defining a data source is a one-time activity. That is, once you define a data source, it is available from that point on within any workbook on the computer.
1. Open the workbook named Forecasting.xlsm located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files
2. Add a new worksheet to the workbook and name it Forecast Data
3. Click on the Data tab in the ribbon and click the Get External Data button
4. Select From Microsoft Query under the From Other Sources button
5. On the Databases tab, select <New Data Source> and click OK
6. Name the data source Budget Database
7. Select the driver named Microsoft Access Driver (*.mdb, *.accdb)
8. Click Connect…
9. Click Select… in the ODBC Microsoft Access Setup
10. Select the database named Budget.accdb located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database and click OK
11. Click OK to close the ODBC Microsoft Access Setup dialog box
Figure 3
ODBC Microsoft Access Setup
12. Click OK to close the Create New Data Source dialog. You do not need to select a default table.
Task 2 – Building the Query
Now that you have a data source defined, the next step is to define the query. If you already have a data source defined, you would skip steps 5-12 in the previous task as move directly in to the first step of this task. Building a query with Microsoft Query is somewhat similar to designing a query in Microsoft Access.
1. Picking up from where you left off in the previous task, you should be looking at the Choose Data Source dialog box with the Budget Database data source selected.
2. Ensure that Use the Query Wizard to create/edit queries is not checked. The Query Wizard is helpful for simple table-based queries, but it does not allow you to create a parameterized query, which is what we want for this exercise.
3. Click OK in the Choose Data Source dialog to close this window and display Microsoft Query. Note that after you define a data source as you did in steps 5-12, you can use it in the future simply be selecting it in the Choose Data Source dialog box
4. Select Facts Crosstab from the list of tables and click Add.
5. Close the Add Tables dialog
Select ViewàCriteria to show the criteria pane
Figure 4
View Criteria
6. Add fields from the Facts Crosstab table (technically it is a query defined in the Budget database) to the query fields by double-clicking on each of them in the table. These will be added to the results area at the bottom of the window. Be sure to add them in the following order:
a. Account – click the Sort Ascending button in the toolbar after adding this field to order the results by Account.
b. 1
c. 2
d. 3
e. 4
f. 5
g. 6
h. 7
i. 8
j. 9
k. 10
l. 11
m. 12
n. Account Name
o. Fiscal Year
p. Scenario Type
q. Store
7. Drag the following fields from the Facts Crosstab table to the Criteria Field in the Criteria pane
a. Store
b. Fiscal Year
c. Scenario Type
8. Set the Criteria values as shown below. The brackets around the values designate these criteria values as parameters.
a. Store: [Store ID]
b. Fiscal Year: [Year]
c. Scenario Type: [Scenario]
Figure 5
Criteria Values
9. Test the query by selecting RecordsàQuery Now. Supply the following values for the parameters:
a. Store ID = 1
b. Year = 2009
c. Scenario = Actual
10. Select FileàReturn Data to Microsoft Excel
11. Put the data in the Data worksheet in cell B5.
12. Click OK
Task 3 – Hook up the query parameters to cells on the worksheet
At this point, you have data on the worksheet, but you have not really achieved much more than you can do using the basic functionality you learned in Exercise 1. To harness the power of this technique you need to map cells on the worksheet to the values used for the parameters.
1. In cell B1 enter the value: Store
2. In cell B2 enter the value: Year
3. In cell B3 enter the value: Scenario
4. In cell C1 enter the value: 1
5. In cell C2 enter the value: 2009
6. In cell C3 enter the value: Actual
7. Right-click on any cell in the data table and select TableàParameters
8. Select the Store ID parameter
9. Choose the option Get the value from the following cell
10. Put the cursor in the selection text box and then choose cell C1 on the Data worksheet
Figure 6
Parameters
11. Check Refresh automatically when cell value changes
12. Repeat steps 8-11 for the Year and Scenario parameters
13. Click OK
Exercise 2 Verification
In order to verify that you have correctly performed all steps of exercise 2, proceed as follows:
In this verification, you will test your work by changing the values of the cells that you mapped to query parameters.
1. Select cell C1 and change the value from 1 to 2. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
2. Select cell C2 and change the value from 2009 to 2008. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
3. Select cell C3 and change the value from Budget to Actual. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
4. To prepare for the next exercise, set the values for the cells above back to:
a. Store = 1
b. Year = 2009
c. Scenario = Actual
Exercise 3: Create a No-code, Data-bound Interactive Display in Excel
Exercise 2 demonstrated a powerful technique for displaying data in a dynamic fashion. For many scenarios, displaying data in a table such as this is sufficient. Other scenarios however, require more control over the presentation of the data. In this type of scenario, you can design the layout on a separate worksheet and create formulas to retrieve data from the query table as needed. In this exercise, you will create the forecast/budget report by mating the forecast worksheet to the table on the Data worksheet.
Task 1 – Add Formulas to the Forecast worksheet
1. Switch to the Forecast worksheet. Note that some basic setup work is complete.
a. The income statement has been formatted as desired
b. Sub-totals have been added where appropriate
c. Unnecessary columns and rows have been hidden
d. Account ID’s corresponding to the account used in a given row have been added to column A. This is to avoid having to hard-code these in formulas.
e. Column offsets are located in row one. This is to avoid having to hard-code these in formulas.
2. Select cell G12 and enter the formula: =VLOOKUP($A12,Table_Query_from_Budget_Database[#All],Forecast!G$1,FALSE)
3. Select the range G12:R12 and press CTRL+R to fill the formula from cell G12 to the rest of the cells through December
4. With the range G12:R12 still selected, grab the drag handle in the lower right corner of cell R12 and drag fill the formula down to row 16
5. Select the range G12:R12 and press CTRL+C to copy the range
6. Select cell G20 and press CTRL+V to paste the formulas
7. With the range G20:R20 still selected, grab the drag handle in the lower right corner of cell R20 and drag fill the formula down to row 33
8. Select the range G12:R12 and press CTRL+C to copy the range
9. Select cell G37 and press CTRL+V to paste the formulas
10. With the range G37:R37 still selected, grab the drag handle in the lower right corner of cell R37 and drag fill the formula down to row 60
11. Test your work:
a. Note the total Net Income in cell S63. This value should be $47,252 using the parameter values of Store=1, Year=2009, and Scenario=Actual.
b. Switch back to the Forecast Data worksheet and change the Year to 2008
c. Observe that the data refreshes with data from 2008.