Access™ 2007 VBA Programmer's Reference

16.5. Sending Data to PowerPoint

There are often times when Office users want to convey statistical data during a presentation via the use of graphs and charts. Most of the time the users probably want to build a presentation themselves, but someone who gives many lectures might want to update an existing presentation with the latest data from her Access database. It is worth noting that PowerPoint also provides a rich object model for building presentations on-the-fly. The following example illustrates how to update a presentation with an Excel chart created programmatically with data from an Access report.

This procedure is a little complicated, so let's break it down into three easy steps. First, TransferSpreadsheet is used to export a Recordset into an intermediate Excel file, as was shown previously in this chapter.

'Define variables
Dim strExcelFile As String
Dim strQueryName As String

'Use TransferSpreadsheet to create an Excel spreadsheet
strExcelFile = "C:\MiscAssets.xlsx"
strQueryName = "MiscAssetsReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strQueryName,
strExcelFile

The second step is to create VBA code in your Access application to automate the creation of the chart using the Excel Object Model. To save space in this example, the code uses an existing chart object included the sample code files, but easily could have been created via the Excel Object Model. Here, the newly created workbook in Excel is opened and a chart is created based on ...