Creating an Excel Worksheet and Chart

Excel worksheets make it easy to filter data for analysis and to create charts for further study. Adding data to an Excel worksheet is similar to adding values to the cells of a table in Word. Once you populate (or fill in) your worksheet, you can add an AutoFilter with just one line of Visual Basic code. Adding an Excel chart involves a few more steps, but it's also fairly simple. The Excel chart serves two purposes: to complement the data in the worksheet and to perform in a PowerPoint presentation.

Create an Excel Worksheet

  1. In the Visual Basic Editor, click Module on the Insert menu to create a new code module.

  2. Add the following declarations to the code module:

    3. Public g_chtUsage As Excel.Chart
Dim m_appXl As New Excel.Application
Dim m_shUsageData As Excel.Worksheet
Dim m_wbEnergy As Excel.Workbook

    The first declaration declares the public variable g_chtUsage as an Excel Chart object. You declare the variable g_chtUsage as public because it's used in the code module that creates a PowerPoint presentation. The second declaration declares the module-level variable m_appXl as a new instance of the Excel Application object.

    You use the keyword New in the declaration statement to indicate that the first time the variable m_appXl is used in code, a new instance will be created. Thus, you don't need to use the CreateObject function. The m_appXl variable is a module-level variable that's used in the CreateChart procedure to set the Visible property of the Excel application window to True once the worksheet and chart have been generated. You'll add the CreateChart procedure in the section of this chapter titled "Create a Chart in Excel."

  3. After the variable declarations, add the following CreateExcelSheet procedure:

    4. Sub CreateExcelSheet()
    Set m_wbEnergy = m_appXl.Workbooks.Add
    Set m_shUsageData = m_wbEnergy.Worksheets(1)
End Sub

    The CreateExcelSheet procedure sets two variables to Excel objects. It sets the first, m_wbEnergy, to an Excel Workbook object, which is added to the Workbooks collection of the newly created instance of the Excel Application object, m_appXl. Unless the setting in the Options dialog box on the Tools menu in Excel indicates otherwise, the procedure adds three Worksheet objects to a newly created Workbook object. You set the first worksheet to the module-level variable m_shUsageData.

  4. After the two Set statements in the CreateExcelSheet procedure, add the following With…End block:

    5. With m_shUsageData.Rows(1)
    .Font.Bold = True
    .Cells(, 1).Value = "ID"
    .Cells(, 2).Value = "Date/Time"
    .Cells(, 3).Value = "Computer Network"
    .Cells(, 4).Value = "Period kWh"
    .Cells(, 5).Value = "Cost"
End With

    Within the With…End block, the font in the first row of the worksheet is set to bold. The next five lines add headers to the first five cells in the first row. The Cells property takes two arguments. The first argument is the row index, and the second is the column index. Because the procedure accesses the Cells property from the first row object, you don't need to specify the row index, but only the second argument, the column index.

You've created a new Excel workbook and formatted the first worksheet. The worksheet is now ready to accept data from the database.

Add Entries in an Excel Worksheet

  1. Click beneath the procedure CreateExcelSheet and create a new procedure called AddToSheet by adding the following code:

    2. Sub AddToSheet(sID As String, sDateTime As String, _
    sComputer As String, sgPeriodkWh As Single, _
    sgCost As Single)   
End Sub

    The procedure passes five arguments to the AddToSheet procedure. The values of the arguments are set in the GetDatabaseInfo procedure in the code module modMain.

  2. Add the following With…End block and code above the End Sub statement:

    3. With m_shUsageData.Rows(Int(sID) + 1)
    .Cells(, 1).Value = sID
    .Cells(, 2).Value = sDateTime
    .Cells(, 3).Value = sComputer
    .Cells(, 4).Value = sgPeriodkWh
    .Cells(, 5).Value = Format$(sgCost, "###0.00")
End With

    The With…End block adds values to each cell in the next available row in the Excel worksheet. Each cell in a row in a worksheet has a value, represented by the Value property. In the last line within the With…End block, you format the text string assigned to the cell text by using the built-in Visual Basic function Format$, as you did when you added the same value to the Word table you created earlier in this chapter.

Create a Chart in Excel

  1. Click beneath the procedure AddToSheet and create a new procedure called CreateChart by adding the following code:

    2. Sub CreateChart()
    Set g_chtUsage = m_wbEnergy.Charts.Add
    With g_chtUsage
        .ChartType = xlAreaStacked
        .SetSourceData _
            Source:=m_shUsageData.Range("C2:C63,E2:E63"), _
            PlotBy:=xlColumns
        .Location Where:=xlLocationAsNewSheet, Name:= _
            "EnergyUsage-" & g_sCircuit
    End With
End Sub

    The first line of the CreateChart procedure sets the variable g_chtUsage to a newly created chart in the Excel workbook. The procedure then uses the variable in the With…End block to set the chart type, the data source for the chart, and where the chart is to be added in the workbook. The Where argument of the Location method of the Chart object indicates that Excel should add the chart to a newly created chart sheet in the workbook. The tab name of the new sheet will be "EnergyUsage-Computer Network." The data sources are the computer network usage column (Column C) and the cost column (Column E) in the workbook's first worksheet.

  2. Within the With…End block, after the Location method and above End With, add the following lines of code:

    3. .HasTitle = True
.ChartTitle.Characters _
    .Text = "Energy Usage - " & g_sCircuit
With .Axes(xlCategory, xlPrimary)
    .HasTitle = True
    .AxisTitle.Characters.Text = "Index"
End With
With .Axes(xlValue, xlPrimary)
    .HasTitle = True
    .AxisTitle.Characters.Text = "Cost ($)"
End With

    These lines add formatting to the newly created chart. The first two lines add the title "Energy Usage - Computer Network" to the chart. The two following With…End blocks add a label to the chart's x axis and y axis, respectively.

  3. Immediately following the last With…End block you just added in the CreateChart procedure, above the End Sub statement, add the following two With…End blocks:

    4. With m_shUsageData.Range("A:F")
    .Columns.AutoFit
    .AutoFilter
End With  
 
With m_appXl
    .DisplayAlerts = False
    m_wbEnergy.SaveAs _
        g_sDBProjectPath & "DataAnalysis", xlNormal
    .Visible = True
    .UserControl = True
End With

    Once you add AutoFilters to the Excel worksheet, the program saves the workbook with the name "DataAnalysis" and displays the Excel application window. The DisplayAlerts property of the Excel Application object is set to False so that no message box is displayed if the SaveAs method detects that an Excel file with the same filename already exists. The UserControl property of the Excel Application object is set to False when the Excel Application object is created through Visual Basic. (If the user started Excel, UserControl would be set to True.)

    By setting the UserControl property to True, you're giving control of the Excel Application object you created through Visual Basic to the user. Consequently, the Excel application window remains visible on the screen once the Visual Basic code finishes running. When the Visible property of the respective Application object is set to True, the Word, PowerPoint, and Outlook application windows also remain visible.

  4. In the Properties window in the Visual Basic Editor, change the Name property of the code module to modExcel.

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

    6. You've now added all the code needed to create an Excel worksheet and chart.

Test Your Code

  1. Double-click the modMain project item in the Project Explorer to make the code window active.

  2. In the modMain code module, add the following procedure.

    3. Sub TestCreatingExcelWorkbook()
    g_sDBProjectPath = Application.CurrentProject.Path & "\"
    modExcel.CreateExcelSheet
    GetDatabaseInfo bReport:=False, bSheet:=True, _
        bPres:=False, bUpdateForm:=False
    modExcel.CreateChart
End Sub

Note the values of the arguments passed to the GetDatabaseInfo procedure. You set the first argument, bReport, to False because a report created in Word is not required. You set the second argument, bSheet, to True in order to create an Excel spreadsheet and chart. You set the third argument, bPres, to False because a presentation created in PowerPoint is not required. You set the last argument, bUpdateForm, also to False because the Access form is not displayed.

When you run the procedure above, the worksheet with AutoFilters should appear as follows:

Click to view at full size.

The Excel chart that's created simultaneously should look like this:

Click to view at full size.