Tags: :
Articles
10
This demonstration will only work in Internet Explorer as vbScript is not supported in any other browser.
The Stated ProblemHow to automate creation of a PowerPoint brief from data held within a Domino Database.
The SolutionThe 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 MacroThe 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 vbScriptOnce 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 browserThis 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 SlideWithin 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)
NOTEAs 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 imageInserting 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).SelectBecomes (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 exampleMy ExpectationIf 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
