O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required