لطفا منتظر باشید ...
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
gobjExcel.Workbooks.Add
'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
.Columns("A:B").Select
.Columns("A:B").EntireColumn.AutoFit
.Range("A1").Select
.ActiveCell.CurrentRegion.Select
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
Else
'If Excel not launched successfully, display an error message
MsgBox "Excel Not Successfully Launched"
End If
Else
'If more than 500 records are in result set, display a message
MsgBox "Too Many Records to Send to Excel"
End If
cmdCreateGraph_Exit:
Set rstData = Nothing
Set rstCount = Nothing
Set fld = Nothing
Set rng = Nothing
Set objWS = Nothing
'Turn hourglass off
DoCmd.Hourglass False
Exit Sub
cmdCreateGraph_Err:
'If an error occurs, display a message and return to
'common exit routine
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume cmdCreateGraph_Exit
End Sub
Figure 22.6. The form used to create an Excel graph.
Figure 22.7. The result of a query graphed in Excel.
CAUTIONIf the Common Dialog control is not installed on your machine or the user's machine, this code will not run. If that is the case, you must remove the reference to the Common Dialog control before running the example. Any examples in the chapter that use the Common Dialog control must be modified.This routine begins by creating several object variables. It then creates two recordsets and sets the ActiveConnection property of each recordset to the connection associated with the current project. It calls a user-defined function called CreateRecordset, located in the basUtils module. The CreateRecordset function receives three parameters: the two recordset object variables, and the name of a query. Listing 22.7 shows the CreateRecordset function.
Listing 22.7 The CreateRecordset Function
Function CreateRecordset(rstData As ADODB.Recordset, _
rstCount As ADODB.Recordset, _
strTableName As String)
On Error GoTo CreateRecordset_Err
'Create recordset that contains count of records in query result
rstCount.Open "Select Count(*) As NumRecords from " & strTableName
'If more than 500 records in query result, return false
'Otherwise, create recordset from query
If rstCount!NumRecords > 500 Then
CreateRecordset = False
Else
rstData.Open strTableName
CreateRecordset = True
End If
CreateRecordset_Exit:
'Common exit point; destroy the rstCount recordset
Set rstCount = Nothing
Exit Function
CreateRecordset_Err:
'Display error message and resume at common exit point
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume CreateRecordset_Exit
End Function
This function begins by counting how many records are returned by the query name that is passed. If the number of records exceeds 500, the function returns False; otherwise, the function opens a recordset based on the query name that is passed and returns True. This function ensures that only a reasonable number of records are sent to Excel, and that a recordset can be opened successfully.If the CreateRecordset function returns True, the remainder of the code in the Click event of the cmdCreateGraph command button executes. The routine uses the CreateExcelObj function to launch Excel. If Excel is opened successfully, the code creates a new workbook. The routine then loops through each field in the Fields collection of the recordset (the result of the query). The values of the cells in the first row of the worksheet are set equal to the names of the fields in the recordset. Next, the routine uses the CopyFromRecordset method of the Excel Range object to copy the contents of the recordset rstData to cell A2 in the active worksheet. The data from each row is placed in a different row within the spreadsheet. The data from each column in a particular row is placed in the various columns of the worksheet. OLE object fields (adLongVarBinary) are excluded from the process.After all the data in the recordset is sent to Excel, the routine is ready to create a chart. It moves the cursor to cell A1 and then selects the entire contiguous range of data. It adds a chart object to the worksheet and then uses the Chart Wizard to create a chart. Finally, Excel is made visible so that users can see the fruits of their efforts.
