Access Cookbook - Ken Getz [268]
This undocumented syntax can be of some benefit. If you are going to add the functionality to interact with the shell, you can use code like the following to determine if your user is running the Windows 9x shell:
Public Function acbNewShell ( ) as Boolean
Dim lngChannel as Long
On Error Resume Next
lngChannel = DDEInitiate("Folders","AppProperties")
acbNewShell = (lngChannel <> 0)
DDETerminate lngChannel
End Function
You'll notice that the example uses this function (as well as a public flag) to decide whether to call the various shell objects "groups" and "items" (as in the Windows NT Program Manager) or "folders" and "shortcuts" (as in the Windows 9x shell).
To shield you from the details of the DDE conversation and to isolate the DDE code in one routine, each of the command-string replacement functions calls the DDEExecutePM function. This makes the code neat and easy to understand, but it does have a potential disadvantage: calling DDEInitiate and DDETerminate every time you call a wrapper function adds substantial time and overhead to your application. If you make many calls to Window via DDE, you'll want to reconsider this design. For most applications, though, this shouldn't be a problem.
12.6. Send Access Data to Excel and Create an Excel Chart
Problem
You want to export data from Access to Excel and create a chart programmatically.
Solution
You can use an ADO Recordset object to export data to Excel programmatically, then use Automation with Excel to create a chart based on the exported data.
Load and run frmExcel from 12-06.MDB. This form calls out to Excel, passing in the values from a recordset to create an Excel spreadsheet and chart based on sales data from the Northwind sample database (see Figure 12-9).
Figure 12-9. The finished Excel worksheet and chart
Here's how you can create Excel charts in your own Access applications:
Create the query that will hold your data. In the sample database, you'll find qryTopTenProducts, which calculates the top 10 products by dollar amount sold. There are two columns: the product name and the total dollar amount. The datasheet view of the query is shown in Figure 12-10.
Figure 12-10. qryTopTenProducts in datasheet view
Set a reference in your project to the Microsoft Excel object library and the ADO library, as shown in Figure 12-11.
Figure 12-11. References needed to make the code work
Create the procedure that exports the data to Excel and creates a sample chart. Here's the complete listing:
Private Const conQuery = "qryTopTenProducts"
Private Const conSheetName = "Top 10 Products"
Public Sub CreateExcelChart( )
Dim rst As ADODB.Recordset
' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim i As Integer
On Error GoTo HandleErr
' Create Excel Application object.
Set xlApp = New Excel.Application
' Create a new workbook.
Set xlBook = xlApp.Workbooks.Add
' Get rid of all but one worksheet.
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True
' Capture reference to first worksheet.
Set xlSheet = xlBook.ActiveSheet
' Change the worksheet name.
xlSheet.Name = conSheetName
' Create recordset.
Set rst = New ADODB.Recordset
rst.Open _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection
With xlSheet
' Copy field names to Excel.
' Bold the column headings.
With .Cells(1, 1)
.Value = rst.Fields(0).Name
.Font.Bold = True
End With
With .Cells(1, 2)
.Value = rst.Fields(1).Name
.Font.Bold = True
End With
' Copy all the data from the recordset
' into the spreadsheet.
.Range("A2").CopyFromRecordset rst
' Format the data.
.Columns(1).AutoFit
With .Columns(2)
.NumberFormat = "#,##0"
.AutoFit
End With
End With
' Create the chart.
Set