The integrated Office solution you're about to create involves a fictitious set of data representing an energy usage log for a computer lab in your company. The solution is called "Energy Management," and it tracks a lab's energy use over a one-month period so that management can do a cost assessment and determine ways of reducing energy costs.

The Access database that stores the energy usage data consists of three main elements. The first element is the table containing the data. The second is the user interface, or dialog box, where you select the document items you want to automatically generate. The third is the code, contained in the various code modules, that creates the selected document items. The steps in this chapter show you how to create all three elements of the database.

1. Start Access. In the opening Access dialog box, select the Blank Access Database option button and click OK.
2. In the File New Database dialog box, select the Chapter 8 practice folder; in the File Name text box, type MyEnergy and click Create. The Database window is displayed.



3. In the Database window, click Tables in the Objects bar, and then click New on the Database window toolbar. Select Import Table in the New Table dialog box and click OK.
4. In the Import dialog box, select Text Files in the Files Of Type drop-down list. In the Chapter 8 practice folder, select LabEnerg.txt and click Import.

In this fictitious solution, a digital meter connected to a computer automatically generated the text file. The hardware and software associated with the meter automatically fed the data to the text file.

5. In the Import Text Wizard dialog box, click Next five times until you reach the final step in the wizard, and then type LabEnergyUsage in the Import To Table text box.

Because you're importing a comma-delimited text file containing data that was automatically generated by the tools connected to the circuits, you should accept the default settings in each step of the wizard.

6. In the Import Text Wizard dialog box, click Finish.
7. When the Import Text Wizard displays the message box indicating that Access has finished importing the data file, click OK.
8. Select the LabEnergyUsage table and click Design on the Database window toolbar.
9. In the Field Name column, double-click in the second row, which currently contains the text "Field1."



10. In the Field Name column, clear the text and type Date and Time.
11. In the Field Name column, double-click in the third row, which currently contains the text "Field2." Clear the text and type Lighting.
12. In the Field Name column, double-click in the fourth row, which currently contains the text "Field3." Clear the text and type Computer Network.
13. Close the Table Design View window by clicking the lower Close Window button in the upper-right corner of the Table Design View window.


Close Window

14. When Access asks if you want to save changes to the design of table LabEnergyUsage, click Yes.

The table is now ready for exporting data to Word, Excel, and PowerPoint, and you can start creating the dialog box that will display your document options.

1. In the Database window, click Forms in the Objects bar, and then click New on the Database window toolbar.
2. In the New Form dialog box, select Design View and click OK. The form is now in Design view, and you can start adding controls to it.
3. Click the Check Box control in the Toolbox and then click anywhere on the form to insert a check box control. Repeat these actions three more times to create a total of four check boxes.


Check Box control

4. Click the Command Button control in the Toolbox and then click anywhere on the form to insert a command button control. When the Command Button Wizard appears, click Cancel. Repeat this step to create a total of two command buttons.


Command Button control

5. Click the Label control in the Toolbox and then click anywhere on the form to insert a label control. Immediately after inserting the Label control to replace the label's default text, type Generate the following documents:. You need to add a caption immediately or the control will disappear.


Label control

6. Click the Image control in the Toolbox and then click anywhere on the form to insert an image control. In the Insert Picture dialog box that's displayed automatically once you insert the Image control, change to the Chapter 8 practice folder, select Logo.wmf, and click OK.
7. Move and resize the controls and the form so that they appear similar to those shown in the following illustration:



8. If the Properties window isn't displayed, on the View menu in Access, click Properties. In the Properties window, click the All tab to see a full list of properties, and then set the following values for the controls. To set the properties in the table below, select each control and set the value listed in the table in the Properties window.

Control	Property	Value
Check0	Name	chkWord
Label1	Name	lblWord
Label1	Caption	Microsoft Word report
Check2	Name	chkExcel
Label3	Name	lblExcel
Label3	Caption	Microsoft Excel workbook
Check4	Name	chkPowerPoint
Label5	Name	lblPowerPoint
Label5	Caption	Microsoft PowerPoint presentation
Check6	Name	chkOutlook
Label7	Name	lblOutlook
Label7	Caption	Microsoft Outlook e-mail message
Command8	Name	cmdOK
Command8	Caption	OK
Command9	Name	cmdCancel
Command9	Caption	Cancel
Command9	Cancel	Yes
Label10	Name	lblGenerate
Image11	Name	imgLogo
Image11	Size Mode	Stretch

9. To display the progress of your solution while it's running, add four Label controls just above the OK and Cancel buttons. Add the following cap-tions consecutively: Number of records read: , #, of, #. When you insert each Label control, add the caption immediately or the control disappears.
10. In the Properties window, set the following properties:

Control	Property	Value
Label12	Name	lblProgress
Label13	Name	lblCurrentRecord
Label14	Name	lblOf
Label15	Name	lblTotalRecords

In the Form Design window, click anywhere outside the form design area to display the list of properties for the form. (You may have to scroll down in order to display the area below the form design area.)

11. In the Properties window, set the following values for the form:

Property	Value
Caption	Energy Management
Record Selectors	No
Navigation Buttons	No
Dividing Lines	No
Border Style	Dialog
Shortcut Menu	No

12. Click the form window's title bar and press F5 to display the dialog box.

Try selecting and clearing the check boxes. Notice that the Form Design window creates forms just like UserForms in the Visual Basic Editor of Word, Excel, and PowerPoint does.



13. Click the Close Window button to close the form. When Access asks if you want to save changes to form Form1's design, click Yes. In the Save As dialog box, type frmMain in the Form Name text box and click OK.


Close Window

Before adding code, you may want to click the Design button and move or resize the controls to resemble the preceding illustration. press F5 to display the form, click the Close Window button to close it, and then save your changes.

1. In the MyEnergy: Database window, click Forms in the Objects bar, select the item frmMain, and then click Design on the Database window toolbar.
2. On the View menu, click Code to display the code module behind the form frmMain. You'll see the Visual Basic Editor for Access.
3. In the Object drop-down list of the Code window, select cmdOK. By default, the Click event procedure is displayed in the Code window.
4. In the event procedure cmdOK_Click, add the following lines of code:

Dim bReport As Boolean, bSheet As Boolean Dim bPres As Boolean, bEmail As Boolean If Me.chkWord.Value = True Then bReport = True If Me.chkExcel.Value = True Then bSheet = True If Me.chkPowerPoint.Value = True Then bPres = True If Me.chkOutlook.Value = True Then bEmail = True modMain.Main bReport, bSheet, bPres, bEmail DoCmd.Close acForm, "frmMain", acSaveNo

The first two lines contain declarations of the Boolean variables bReport, bSheet, bPres, and bEmail. By default, the Boolean variables are set to False when they're declared. The four If…Then condition statements that follow check the value of each check box in the form frmMain and set the appropriate Boolean variable. The Me keyword represents the form in which the code you're writing resides. Once you set all the Boolean variables, the program calls the Main procedure in the code module modMain, which you'll create next. The Boolean variables are passed as arguments to the Main procedure.

5. In the Object drop-down list of the Code window, select cmdCancel.

By default, the click event procedure is displayed in the code window.

6. In the event procedure cmdCancel_Click, add the following line of code:

DoCmd.Close acForm, "frmMain", acSaveNo

You can specify three arguments in the Close method of the DoCmd object in Access. The first argument is the object type you want to close, the second is the name of the object you want to close, and the third sets whether to save changes to the object when it's closed. In the line of code you just added, you close the Access form frmMain without saving changes to the form in the database.


Close Window

7. Click the Save button on the Standard toolbar in the Visual Basic Editor to save changes to the code.


Save

You've now completed the dialog box your users will interact with to generate their required Office documents from the data in the database. You now have to add code to create each of the Office documents listed in your custom dialog box.

1. In the Visual Basic Editor, click Module on the Insert menu to create a new code module.
2. In the Visual Basic Editor, on the Tools menu, click References to display the References dialog box.
3. Select the following items in the Available References list box and click OK when you finish: Microsoft Word 9.0 Object Library, Microsoft Excel 9.0 Object Library, Microsoft PowerPoint 9.0 Object Library, and Microsoft Outlook 9.0 Object Library.

You're going to write code that accesses all of these object libraries so that you can create each type of document.

4. Add the following declarations to the code module:

Public g_sgTotalCost As Single Public g_iTotalHours As Integer Public g_sDBProjectPath As String Public Const g_sCircuit As String = "Computer Network" Const m_sgCost As Single = 1.075 Const m_iWattage As Integer = 560

The first two declarations are public variables used to total the data in the database. The public constant g_sCircuit is set to "Computer Network," the circuit that the procedure will analyze. Later you can reset it to "Lighting" so you can analyze the lighting data. You declare the public variable, g_sDBProjectPath, as a string; you'll use it to assign the file path of the MyEnergy database.

In this solution you'll use a supporting file, a Word template, to help create a report in Word. To keep the code of this solution from being complex, the Word template file should be copied to the same folder as the MyEnergy database, because the path to the database file can then easily be retrieved in code. In addition, the path to the database also specifies where to save the Word, Excel, and PowerPoint files created by this example. The two module-level constants, m_sgCost and m_iWattage, are values used to calculate the energy cost.

5. After the variable declarations, add the following Main procedure:

Sub Main(bReport As Boolean, bSheet As Boolean, _ bPres As Boolean, bEmail As Boolean) g_sDBProjectPath = Application.CurrentProject.Path & "\" End Sub

The Click event procedure for the cmdOK command button that you added to the form frmMain will call the Main procedure. The four Boolean variables passed into the Main procedure indicate which check boxes were selected in the form frmMain. You set the Boolean values in the Click event procedure of cmdOK.

This procedure assigns the g_sDBProjectPath variable to the Path property. In this example, the Path property returns a string value that is the pathname to the disk location of the Access database MyEnergy.mdb.

6. After the line of code that sets the g_sDBProjectPath variable, add the following If…Then condition blocks:

If bReport = True Then modWord.CreateWordDocument End If If bSheet = True Or bPres = True Then modExcel.CreateExcelSheet End If

If the Boolean variable bReport is passed into the Main procedure with a value of True, the program calls the procedure CreateWordDocument, located in the code module modWord. If the Boolean variable bSheet or the Boolean variable bPres is passed into the Main procedure with a value of True, the program calls the procedure CreateExcelSheet, located in the code module modExcel. In order to create a presentation with the data in a chart, you first have to create the chart in Excel. You'll create the procedures CreateWordDocument and CreateExcelDocument later in this chapter. These two procedures create a document and a worksheet ready for the database information.

7. After the two If…Then condition blocks, add the following call to the GetDatabaseInfo procedure:

GetDatabaseInfo bReport, bSheet, bPres, True

The GetDatabaseInfo procedure retrieves the information from the database and sends it to Word and Excel. The Boolean variables bReport, bSheet, and bPres are passed to the procedure so that you can check whether you need to pass the data to Word or Excel.

The last argument in the GetDatabaseInfo procedure indicates whether the Access form, frmMain, should be updated. Because the line of code above calls the GetDatabaseInfo procedure from within the Main procedure, and the Main procedure is only called when clicking OK on the form frmMain, this value is always set to True. As you'll see later in this chapter, when you test your code for creating the Word, Excel, and PowerPoint documents, this argument is set to False because running the procedure you'll use to test your code does not require the form to be displayed.

8. Add the following If…Then condition blocks after the line of code added in the previous step:

If bReport = True Then modWord.AddTotalRow End If If bSheet = True Or bPres = True Then modExcel.CreateChart End If If bPres = True Then modPowerPoint.CreatePowerPointPres End If If bEmail = True Then modOutlook.SendEmail bReport, bSheet, bPres End If

If the Boolean variable bReport is True, the procedure adds the row containing the total cost and energy usage to the report. If the Boolean variable bSheet or bPres is True, it creates an Excel chart. If the Boolean variable bPres is True, the procedure also creates a PowerPoint presentation that uses the Excel chart created in the procedure CreateChart. If the Boolean variable bEmail is True, it creates an Outlook e-mail message.

You've now completed the Main procedure, which is the heart of the program. Now you need to create the supporting procedures that you called.

1. Click beneath the procedure Main and then create a new procedure called GetDatabaseInfo by adding the following code:

Sub GetDatabaseInfo(bReport As Boolean, bSheet As Boolean, _ bPres As Boolean, bUpdateForm As Boolean) Dim sID As String, sDateTime As String Dim sComputer As String Dim sgPeriodkWh As Single, sgCost As Single Dim iCounter As Integer Dim rstEnergy As New ADODB.Recordset rstEnergy.Open "LabEnergyUsage", CurrentProject.Connection, _ adOpenStatic End Sub

The first two lines of the GetDatabaseInfo procedure declare variables that are set to the data in the database. The variable rstEnergy is declared as the type Recordset defined in the ActiveX Data Objects (ADO) object library. You use the Recordset object to assign data in a database table. Following the Recordset declaration, the variable rstEnergy is set to the recordset found in the table LabEnergyUsage in the MyEnergy.mdb database.

2. Below the line using the Open method in the code added in the previous step, add the following With…End block containing a Do…Loop:

With rstEnergy If bUpdateForm Then Forms("frmMain").Controls("lblTotalRecords") _ .Caption = .RecordCount End If Do sID = .Fields("Id") sDateTime = .Fields("Date and Time") sComputer = .Fields("Computer Network") If bUpdateForm Then Forms("frmMain").Controls("lblCurrentRecord") _ .Caption = .Fields("Id") DoEvents End If .MoveNext Loop Until .EOF = True .Close End With

The For…Next loop iterates through the recordset, setting the value in each field (Id, Date and Time, and Computer Network) to the declared variables. The last line in the Do…Loop moves to the next record in the recordset, making that record the current record. When the recordset is first opened, the first record in the recordset is, by default, the current record. When the last record is the current one, the EOF property is set to True and the Do…Loop exits. After the Do…Loop exits, the recordset closes.

The two lines within the With…End block that manipulate the Label controls on the form frmMain are used to update the captions of the Label controls. This way you have a visual representation of the loop's progress. The keyword DoEvents is a built-in Visual Basic function used to make sure that the screen is updated to reflect the changes in the form and in the Label controls.

3. Within the Do…Loop, just before the line moving to the next recordset, .MoveNext, add the following mathematical assignment statements:

sgPeriodkWh = m_iWattage * Int(sComputer) / 1000 sgCost = sgPeriodkWh * m_sgCost g_sgTotalCost = g_sgTotalCost + sgCost g_iTotalHours = g_iTotalHours + Int(sComputer)

4. Just below the lines you added in step 3 and above the line moving to the next recordset, .MoveNext, add the following two If…Then condition blocks:

If bReport = True Then modWord.AddToTable sID, _ sDateTime, sComputer, sgPeriodkWh, sgCost End If If bSheet = True Or bPres = True Then modExcel.AddToSheet sID, _ sDateTime, sComputer, sgPeriodkWh, sgCost End If

Once the data is set to the variables sID, sDataTime, and sComputer and the appropriate calculations are made, the two condition blocks above determine whether the data is written to the Word report, the Excel workbook, or both. The second If…Then condition block also checks the value of bPres, because to create a presentation in PowerPoint you first have to send the data to Excel so that you can create a chart. After that, you copy the chart into the presentation.

5. In the Properties window in the Visual Basic Editor, change the Name property of the code module to modMain.
6. Click the Save button on the Standard toolbar in the Visual Basic Editor to save changes to the code.

The code module modMain contains the procedure Main, which is called by the Click event procedure of the cmdOK command button on the form frmMain. The module modMain contains all the code that pertains to opening and retrieving information from the database, as well as the code that determines which Office document to create. In the following sections, you'll create code modules that allow you to create each Office document.