DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Sending MS Access Data to Powerpoint - Part 1

I have written a number of articles on sending Access data to Excel using automation techniques via VBA. You can do the same thing with Powerpoint. Sending Access Data to Powerpoint is not as easy as sending the data to Excel. Excel is "cell" oriented and the cells all exist even when you start with an empty spreadsheet. Powerpoint, however, is object oriented and the objects do not exist in an empty Powerpoint slide. As well, Excel has a huge ready to go inventory of cells, whereas Powerpoint starts with one slide.

(NOTE: this article was written based on research using Access 2000 and Powerpoint 2000. It may or may not work on other versions of these programs. Also, it is assumed that you own the Powerpoint program and have installed it on your computer.)

(article continues after sponsor spot)



We will start with a very simple use of this technique, and we will see how far we get.

My use of these VBA automation techniques all started when a client required a variety of "reports" that had to be done in Powerpoint. The reports were already being done manually, but this was very time consuming. The client wanted the data stored in an Access database and used to modify a slide template for Powerpoint.

As previously stated, the Powerpoint template already existed. The first challenge that I had to overcome was naming the pre-made objects on the template so that Access could "find" them. As with my Access designs, I did not want to use default object names like "TextBox123". Let's give the objects valid and logical names. In order to do this, I had to create a little VBA code that would let me name the objects as Powerpoint 2000 does not provide this functionality. The designers of Powerpoint probably did not foresee very many people like me wanting to manipulate names in this way. Those that do want to, can use the same method I ended up using.

For a description of how to name Powerpoint objects, please see this other article here.

The following is some sample code from that project. It is designed to open the Powerpoint template and assign a title to the first slide. This slide has an object named "Title". As usual, I am not suggesting that I have discovered the best code, but it works. Let me know if you have found a "slicker" way to do this.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Also, this code will only work if you have Powerpoint references turned on. The References section is found under the Tools menu in the VBA editor. See the above mentioned article on DAO for more details on the References menu. You will find the Powerpoint references in the same way.

'--- declare any required objects and variables
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppCurrentSlide As PowerPoint.Slide

'--- open powerpoint and set a pointer from Access to it
Set ppApp = CreateObject("PowerPoint.Application")
'--- make it visible on your monitor
ppApp.Visible = True
'--- open the pre-designed template
Set ppPres = ppApp.Presentations.Open("name of template file goes here")
'--- set a pointer to the first slide in the presentation
Set ppCurrentSlide = ppPres.Slides(1)

'--- assign a custom string of text to the slide title
ppCurrentSlide.Shapes("Title").TextFrame.TextRange.Text = "Some text"

'--- remove the pointers from memory
Set ppApp = Nothing
Set ppPres = Nothing
Set ppCurrentSlide = Nothing

When the program reaches the end of the above code, you will be looking at the finished Powerpoint slide on your monitor. You can now make any minor modifications to it before printing it and/or saving it to disk.

Entire series ...

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy