Access Cookbook - Ken Getz [270]
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
The examples shown here barely scratch the surface of the capabilities of Excel Automation. Excel has a complex object model that is very easy to get lost in!
TIP
If you can't figure out the proper syntax for working with an Excel Automation object, launch Excel and choose Tools → Macro → Record new macro from the menu, then record the actions that you are having problems with. Once you stop the macro recorder, you can examine the code that was created by pressing Alt-F11 and expanding the Modules node. You may then be able to figure out how to plug the code into your Access code.
12.7. Create a PowerPoint Presentation from Access Data
Problem
You need to create similar Microsoft PowerPoint presentations over and over. You currently take an existing presentation, copy it to a new location, and modify it as necessary, resulting in a number of copies of the same text littering your hard disk. It seems that you could just store all the text and its formatting information in an Access table and then create the presentation programmatically when necessary. Then, you could choose just the slides you need, make modifications as necessary, and have only one place where you store the data. Is this possible?
Solution
Microsoft PowerPoint (part of Microsoft Office) offers an amazingly rich set of objects, methods, and properties. Even though it's not a developer's tool, its object model is spectacularly deep, especially in comparison to Access's. It appears that you can do anything programmatically from an Automation client (such as Access) that you can do manually, using PowerPoint as an Automation server—so the answer to the original question is "Yes!" You can definitely create presentations programmatically from Access using tables to store all the information about your presentation.
This solution involves two major activities: setting up the data in tables and using the interface to create your presentation. This section demonstrates both activities.
To try out the sample application, load and run frmPowerPoint from 12-07.MDB. First choose a template from the combo box's list of templates; then enter a filename to which to save your presentation (click on the "..." button to use the common File Open/Save dialog). Click the Create Presentation button to start PowerPoint and create the presentation. Figure 12-12 shows the sample form in action.
Figure 12-12. Use frmPowerPoint to create PowerPoint presentations from within Access
To use this technique to create your own presentations, follow these steps:
Import from 12-07.MDB the tblParagraphs, tblSlides, tlkpLayouts, and tlkpOptions tables.
Import the frmPowerPoint, zfrmParagraphs, and zsfrmSlides forms (the last two are for setting up your slides only and are not part of the sample's user interface).
Import the basCommonFile, basGetTemplate, basPowerPoint, and basRegistry modules.
Open one of the modules in design mode and choose the Tools → References... menu item. For the code to work, your database must include an explicit reference to the DAO and PowerPoint type libraries. Find the options labeled Microsoft DAO Type Library and PowerPoint Object Library (select the most current version of each product), and make sure they're both checked. Figure 12-13 shows the References dialog as it might appear on your machine once you've found and selected the references.
Figure 12-13. Use the Tools > References... dialog to add library references
Open the basGetTemplate module. Modify the first constant (conTemplates) so that it reflects the version of PowerPoint you have installed. The sample is configured for Office 2003; if you're using Office XP, change the "11.0" in the string to "10.0"; if using Office 2000, change it to "9.0".
TIP
You can skip Steps 1 through 5 if you want to use 12-07.MDB as it is.
Plan your presentation carefully. You may want to play