Creating PowerPoint Presentations from within Brio Intelligence - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

Creating PowerPoint Presentations from within Brio Intelligence

Description:

Creating PowerPoint Presentations from within. Brio Intelligence ... oPPTSlide.Shapes.Title.TextFrame.TextRange.Text = 'Sample title' ... – PowerPoint PPT presentation

Number of Views:92
Avg rating:3.0/5.0
Slides: 29
Provided by: davidea6
Learn more at: http://www.maddoxford.co.uk
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Creating PowerPoint Presentations from within Brio Intelligence


1
Creating PowerPoint Presentations from within
Brio Intelligence Exploiting the Automation
Interface
  • David Eastwood
  • Maddox Ford Ltd.
  • www.maddoxford.co.uk

2
Overview
  • Work arose out of a need to create and update
    complex PowerPoint presentations from Brio
    Intelligence
  • This presentation makes use of Brio
    Intelligences JavaScript capabilities and the
    Automation interface
  • Key samples of code are shown and described
  • Although centred on controlling PowerPoint
    presentations, the ideas are applicable to other
    Office products

3
Software Versions
  • The code featured has been developed under Brio
    6.2.3 (and earlier)
  • It works with both Designer and Insight
  • The software has been tested on Office 97, Office
    2000

4
Origins of the work
  • Prompted by the needs of a Category Management
    application
  • End users worked with complex PowerPoint
    presentations which used Brio queries as the
    source of data
  • There was a need to
  • update existing slides for different time
    periods and/or customers
  • provide a simple control panel to create and
    format the slides as well as compose the data
    queries
  • some graph types needed were not available in
    Brio Radar, Bubble

5
What do the applications look like?
6
The PowerPoint object model
Application
oPPT
Presentation(s)
oPPTPres
Slide(s)
oPPTSlide
Shape(s)
OLEFormat

Object
oPPTChart or oPPTTable
  • The full object model is available from MSDN
    see refs. at the end
  • Object names used by us within Brio

7
Getting started
  • Create the link to PowerPoint oPPT new
    JOOLEObject("PowerPoint.Application")
  • Create a new presentation oPPT.Visible true
  • oPPTPres oPPT.Presentations.Add()
  • Alert("New presentation created \r\n"
    oPPTPres.Name)
  • Open an existing presentation
  • sFile tbFileLocation.Text // name from text
    box
  • oPPTPres oPPT.Presentations.Open(sFile)
  • Alert("Presentation Loaded \r\n" oPPTPres.Name)

8
Slide manipulation (1)
  • Add a slide to the end of the slide show
  • nSlideNums oPPTPres.Slides.Count
  • oPPTSlide oPPTPres.Slides.Add(ppLayoutTitleOnly
    ,(nSlideNums1)) //
  • // const ppLayoutTitleOnly 11
  • Add a title (if the slide has one defined)
  • oPPTSlide.Shapes.Title.TextFrame.TextRange.Text
    "Sample title"

Note this syntax reverse order to Office
documentation
9
Slide manipulation (2)
  • Add a footer
  • oPPTFooter oPPTSlide.Shapes.AddTextbox(20,700,5
    00,10,1) //
  • oPPTFooter.TextFrame.TextRange "Slide
    created on " (new Date()).toUTCString()
  • Format some text
  • oPPTFooter.TextFrame.TextRange.Font.Size 8
  • oPPTFooter.TextFrame.TextRange.Font.Italic
    true

Note this syntax reverse order to Office
documentation
10
Objects in slides
  • Charts and tables are embedded objects on a slide
  • They form part of the Shapes collection
  • The OLEFormat object contains the methods and
    properties of embedded OLE objects

11
Graph creation (1)
  • We chose to use Microsoft Graph you could use
    embedded Excel graphs instead
  • Equivalent to the Insert/Chart menu in PowerPoint

12
Graph creation (2)
  • Graph has an attached datasheet whose layout
    determines the look of the graph

13
Create the graph object
  • oNew oPPTSlide.Shapes.AddOLEObject(false,"",0,""
    ,false,"", "MSGraph.Chart",350,600,150,50) //
  • //AddOLEObject arguments are
  • //link,iconlabel,inconindex,iconfilename,displayas
    icon,filename,classname,
  • //height,width,top,left
  • oPPTChart oNew.OLEFormat.Object
  • // now set the chart type
  • oPPTChart.ChartType xlColumnClustered //
    const xlColumnClustered 54
  • oPPTChart.Application.PlotBy xlColumns //
    const xlColumns 2

Note this syntax reverse order to Office
documentation
14
Moving data
  • Data can be exported via the clipboard
  • Exports from table sections can use the GetCell()
    method but its slower
  • We copied the data via the clipboard (and
    re-arranged it within the DataSheet if
    necessary)
  • oPPTChart.Application.DataSheet.Columns.Clear
  • ActiveDocument.Sections"Pivot".Copy()
  • oPPTChart.Application.DataSheet.Range("0000").Pa
    ste

15
Formatting
  • All PowerPoint format properties are available to
    be read and altered
  • As are the properties of embedded objects such as
    graphs
  • function AddDataLabels(chart)
  • chart.Application.Chart.ApplyDataLabels
  • for (var i1 iltchart.SeriesCollection.Count
    i)
  • chart.SeriesCollection.Item(i).HasDataLabels
    true
  • chart.SeriesCollection.Item(i).DataLabels.Font.S
    ize 10

16
Table creation
  • We chose to create embedded spreadsheets.
  • You can also use embedded Word Tables and (in
    PowerPoint 2000) native PowerPoint tables
    (members of the Shapes collection)
  • oNew oPPTSlide.Shapes.AddOLEObject(false,"",0,""
    ,false, "","Excel.Sheet",350,600,150,50) //
  • //AddOLEObject arguments are
  • // link,iconlabel,iconindex,iconfilename,displayas
    icon,
  • // filename,classname,height,width,top,left
  • oPPTTable oNew.OLEFormat.Object

Note this syntax reverse order to Office
documentation
17
Table data
  • Copy the data to the table
  • ActiveDocument.Sections"Pivot".Copy()
  • oPPTTable.Sheets.Item(1).Paste

18
Updating slides
  • We need to identify the source of the data
    already on the slide
  • did Brio create this slide?
  • which Query created this slide?
  • which Section was the source of the data?
  • what Limits were used when the query ran?
  • We can then reset the Brio query to match the
    slide and allow the user to vary these settings
    (e.g. time period)

19
Identifying slides and their source
  • Slides have a SlideID property a unique ID
    independent of slide order (and not visible to
    the PowerPoint user)
  • Slides have Tags this lets you create your own
    properties for a slide
  • Slide.Tags.Add("Value", "TAG LABEL") //
  • // the name of the Tag Label should be in caps
  • // PowerPoint will return caps anyway!

Note this syntax reverse order to Office
documentation
20
Using Tags
  • We use tags to
  • Label the slide as being created by Brio
  • Show the code version used to create the slide
  • Name the query which created the slide
  • Name limits and limit values
  • etc.

21
Finding objects in a slide
  • When updating a slide, you will need to find the
    object from the Shapes collection
  • function FindOLE(oPPTSlide)
  • var nShapeCount oPPTSlide.Shapes.Count
  • for (var n1 nltnShapeCount n)
  • var nShapeType oPPTSlide.Shapes.Item(n).Type
  • if (nShapeType msoEmbeddedOLEObject)
  • // Const msoEmbeddedOLEObject 7
  • break
  • return oPPTSlide.Shapes.Item(n).OLEFormat.Object

22
Live demo
  • I will illustrate the code we have been
    discussing via the demo application
  • This demo code available from Maddox Ford web
    site
  • www.maddoxford.co.uk
  • deastwood_at_maddoxford.co.uk

23
Coding considerations
  • (Brio 6.2.3) When calling PowerPoint methods with
    multiple arguments you need to supply the
    arguments in the reverse order to that shown in
    Microsoft documentation
  • Brio Slide.Tags.Add("Value", "TAG LABEL")
  • Microsoft Slide.Tags.Add("TAG LABEL", "Value")
  • When selecting Items in a collection, use the
    .Item(n) method not n

24
Development aids (1)
  • Use VB Script editor in PowerPoint to confirm
    syntax of commands
  • You may need to load additional Help files in
    PowerPoint
  • MSDN also has reference information and articles

25
On-Line Help
  • Find the object or method
  • Press F1

26
Development aids (2)
  • External JavaScript editor can be helpful with
    large scripts

27
Development aids (3)
  • Create a set of constants to match those used by
    Office
  • Try to follow good JavaScript coding conventions

28
Reference Material and Acknowledgements
  • MSDN
  • http//msdn.microsoft.com/library
  • http//msdn.microsoft.com/library/en-us/modcore/h
    tml/deovrObjectModelGuide.asp
  • On-line Help in Office (VB Editors)
  • Platypus JavaScript editor
  • http//www.c-point.com/pjedit.htm
  • JavaScript The Definitive Guide
  • David Flanagan, OReilly
  • Maddox Ford Brio Development Standards
  • deastwood_at_maddoxford.co.uk
About PowerShow.com