• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 22. Automation: Communicating wi... > Creating a Graph from Access

Creating a Graph from Access

Now that you have learned how to talk to Excel, you are ready to learn how to do something a bit more practical. Figure 22.6 shows a form called frmCreateExcelGraph. The form shows the result of a query that groups the result of price multiplied by quantity for each country. The Create Excel Graph command button sends the result of the query to Excel and produces the graph shown in Figure 22.7. (Listing 22.6 shows the code that produces this graph.)

Listing 22.6. Creating a Graph from Access

Private Sub cmdCreateGraph_Click()
    On Error GoTo cmdCreateGraph_Err
    Dim rstData As ADODB.Recordset
    Dim rstCount As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim rng As Excel.Range
    Dim objWS As Excel.Worksheet
    Dim intRowCount As Integer
    Dim intColCount As Integer

    'Display Hourglass
    DoCmd.Hourglass True

    'Instantiate an ADO recordset and set its Connection
    Set rstData = New ADODB.Recordset
    rstData.ActiveConnection = CurrentProject.Connection

    'Instantiate a second ADO recordset and set its Connection
    Set rstCount = New ADODB.Recordset
    rstCount.ActiveConnection = CurrentProject.Connection

    'Attempt to create Recordset based
    'on the result of qrySalesByCountry
    If CreateRecordset(rstData, rstCount, "qrySalesByCountry") Then

        'If the recordset is created successfully, attempt to launch Excel
        If CreateExcelObj() Then

            'If Excel is launched successfully, add a workbook

            'Create a pointer to the Active sheet
            Set objWS = gobjExcel.ActiveSheet
            intRowCount = 1
            intColCount = 1

            'Loop through the Fields collection of the recordset,
            'using field names as column headings
            For Each fld In rstData.Fields
                If fld.Type <> adLongVarBinary Then
                    objWS.Cells(1, intColCount).Value = fld.Name
                    intColCount = intColCount + 1
                End If
            Next fld

            'Send Recordset to Excel
            objWS.Range("A2").CopyFromRecordset rstData, 500

            'Format Data
            With gobjExcel
                Set rng = .Selection
                .Selection.NumberFormat = "$#,##0.00"

                'Add a Chart Object
                .ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75, 301).Select

                'Run the Chart Wizard
                .ActiveChart.ChartWizard Source:=Range(rng.Address), _
                    Gallery:=xlColumn, _
                    Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels _
                    :=1, HasLegend:=1, Title:="Sales By Country", CategoryTitle _
                    :="", ValueTitle:="", ExtraTitle:=""

                'Make Excel Visible
                .Visible = True
            End With
            'If Excel not launched successfully, display an error message
            MsgBox "Excel Not Successfully Launched"
        End If
        'If more than 500 records are in result set, display a message
        MsgBox "Too Many Records to Send to Excel"
    End If

    Set rstData = Nothing
    Set rstCount = Nothing
    Set fld = Nothing
    Set rng = Nothing
    Set objWS = Nothing

    'Turn hourglass off
    DoCmd.Hourglass False
    Exit Sub

    'If an error occurs, display a message and return to
    'common exit routine
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume cmdCreateGraph_Exit
End Sub




Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint