NostraDominus

Tags: Articles

The Stated Problem
How to automate creation of a PowerPoint brief from data held within a Domino Database.

The Solution
The solution to this problem is a compromise as many solutions are. The stated problem is full automation of a PowerPoint brief creation. Whereas the actual problem can be described as the automated population of a PowerPoint template with stored database data. Subtle difference but one which makes a solution possible.

Recording a Macro

The first thing to do is record a macro of modifying your existingbrief. This is only possible in office 2000 or 2003 as the option hasbeen removed from powerpoint in Office 2007. To record a right click in the toolbar and make the "Visual Basic" toolbar visible.

Select to record a macro and record it in this presentation.


Once you have made the changes click the "Stop" icon.

To view the VBA code recorded select the "Play" icon in the toolbar to view the stored macros. Select the macro we just recorded and then "Step Into". The code editor and reveal the code.


The code in our example looks like this - which works in PowerPoint but does not translate to the Internet Explorer browser.

    ActiveWindow.Selection.SlideRange.Shapes("Text Box 2").Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=23).Select
    ActiveWindow.Selection.TextRange.Text = "Project Title Here"
    ActiveWindow.Selection.SlideRange.Shapes.AddPicture(FileName:="M:\Images\tank.jpg", LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=309, Top:=232, Width:=101, Height:=76).Select
    ActiveWindow.Selection.Unselect
    ActiveWindow.Selection.SlideRange.Shapes("Rectangle 7").Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=15, Length:=16).Select
    ActiveWindow.Selection.TextRange.Text = "This project Rocks!"

Converting VBA to vbScript
Once you have the code you have to translate it from VBA to vbScript so that it will run in the browser. I did a lot of research in this area and did not find a definitive example. I cobbled the solution together from a number of sources and good ole fashioned trial and error. An link to an example database is provided below.

Creating a MSOffice application object in the browser

This code allows for the creation of an instantiation of PowerPoint to be created. The object "PowerPoint.Application" is a registry object which is called by the browser. To allow this you must permit unsigned ActiveX to run in the browser. The most secure way to do this is add your www.hostname.com to your trusted sites in the Internet Explorer browser configuration and change the settings for your trusted host.

    Set wApp = CreateObject("PowerPoint.Application")
    wApp.visible=true

Because we are doing this on the user's client (Windows with Office installed as prerequisits) machine we need to know where the PowerPoint briefing template is. How the users gets it to their desktop is up to them but in the example provided I have them download it from the web page. Then we need to prompt them to provide the location of this file (pptLoc Field). The code then opens the file within the instantiation of PowerPoint (wApp)

    pptLoc=document.getElementById("pptLoc").value
    set objPres = wApp.presentations.open(pptLoc)  


Adding text to a Shape on a Slide

Within VBA and vbScript you can concatenate commands into one long command rather than declaring objects for each object, and then the sub object etc etc. For me this saves on code and it also makes it easier to de-bug. The previous VBA example of 4 lines is combined into one line of vbScript
    ActiveWindow.Selection.SlideRange.Shapes("Text Box 2").Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=23).Select
    ActiveWindow.Selection.TextRange.Text = "Project Title Here"
Becomes
    objPres.Slides.Item(1).Shapes(2).TextFrame.TextRange.Text = document.getElementById("InitTitle").value


In this case I do not have to worry about the length of the text being replaced (see below) as I am filling the whole text box with the Project Description. I also find it easier to mentally envision because we are looking at the second shape on the first slide.

Inserting Text into the middle of an existing Text block

The next set of code describes the Slide Range.Shapes and does not mention the fact that it is on the second slide. This I can only assume is because the macro does not track the "Page Down" key stroke....Anyway because we are not describing the vbScript with the slide and shape number this is simplifed again into one line of code
    ActiveWindow.Selection.SlideRange.Shapes("Rectangle 7").Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=15, Length:=16).Select
    ActiveWindow.Selection.TextRange.Text = "This project Rocks!"
Becomes
    objPres.Slides.Item(2).Shapes("Rectangle 7").TextFrame.TextRange.Characters(15,16).Text = document.getElementById("ProjDesc").value

In this case I DO care care about the number of character where I am doing an insert - I want it to start 15 characters into the text box and I want to replace the next 16 characters. These 16 characters are the sample text I want to replace. (In doing this I am also inheriting the text formatting from the text in the template - which saves me having to format it programmatically)
NOTE
As soon as you add new information into the text field within the slide - you change the number of characters which exist within that text box. To overcome this problem when you have to fill multiple areas, start from the end and work backwards - the code looks into the text from the beginning so if you start with the last section to update and work backwards - you won't change the starting character of prior sample text in the template.

Inserting an image

Inserting the image is again done on the local client and if the user wants to add one it will have to be identified as a local file in the same way as the PowerPoint file described above. You'll also notice that the FileName, LinktoFile etc etc monikers are not necessary.

   ActiveWindow.Selection.SlideRange.Shapes.AddPicture(FileName:="M:\Images\tank.jpg",LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=309, Top:=232,Width:=101, Height:=76).Select
Becomes (with a check to see if a location has been specified by the user)
    imageLoc=document.getElementById("imageLoc").value
    if imageLoc<>"" then
        set newic=objPres.Slides.Item(1).Shapes.AddPicture(imageLoc, 0, 1, 220, 175, 101, 100)
    end if

The example
I have created an example to demonstrate this capability. It is a simple example but effective. We have only scratched the top of the capability of PowerPoint and there are many many other posibilities. But hopefully using this as a starting point and will enable you to expand the possibilities.

The example has two forms and two views. A form and a view to generate and edit content and a view to access the second form opening the data in the Presentation.

Click here to view the example


My Expectation

If this is useful to you please share you ideas, thoughts,modifications and improvements so that the next person reading the article will be more educated, more inspired and from that we all benefit.

Cheers,

Marky