C Programming think-cell
Some functions of think-cell can be accessed programmatically. The interface is integrated into the Office Automation model, so it can be accessed from any language with which you can program Office, such as Visual Basic for Applications or C#. All samples in this chapter are written in Visual Basic for Applications, but can be easily ported to other languages.
The entry point into think-cell is the think-cell add-in object. It can be accessed via the Application.COMAddIns collection. Calls into think-cell are always late-bound. See Microsoft’s knowledge base for an explanation:
http://support.microsoft.com/kb/245115
Thus, the type of the think-cell add-in object is simply Object, and there is no type library or reference to add. Just acquire the object, and you are ready to make calls:
Dim tcaddin As Object
Set tcaddin = _
Application.COMAddIns("thinkcell.addin").Object
The following sections describe the available functionality.
C.1 PresentationFromTemplate
C.1.1 Signature
tcaddin.PresentationFromTemplate( _
wb As Excel.Workbook, _
strTemplate As String, _
ppapp As PowerPoint.Application _
) As PowerPoint.Presentation
C.1.2 Description
This function applies any data links in wb to the template with filename strTemplate. The result is a new presentation within the PowerPoint instance ppapp.
strTemplate can either be a full path or a relative path, which is then taken to be relative to the location of the Excel workbook file wb.
All charts in strTemplate that are linked to the Excel workbook wb are updated (regardless whether they are set to auto-update or not). Then their data links are broken to prevent further changes to these charts.
Charts in strTemplate which are linked to Excel workbooks other than wb are left unchanged and still linked, so it is possible to update links from multiple Excel workbooks by saving the result of this function as a new template and then calling this function again with the next workbook.
If you want to control the colors of chart segments with the Excel link, you can set the color scheme to Use Excel Fill (see ‘Color scheme’). Likewise, to control the number format with the Excel link, set it to Excel Format (see ‘Number format’). Make sure to set the background color and the number format of the respective cells in Excel before calling PresentationFromTemplate.
C.1.3 Example
’ To use this sample, go to Tools - References
’ and add the Microsoft PowerPoint Object Library.
Sub PresentationFromTemplate_Sample()
’ Get the range to modify. It is more efficient
’ to do this once rather than within the loop.
Dim rng As Excel.Range
Set rng = _
ActiveWorkbook.Sheets("Sheet1").Cells(1, 1)
’ Get the think-cell add-in object
Dim tcaddin As Object
Set tcaddin = _
Application.COMAddIns("thinkcell.addin").Object
’ Get a PowerPoint instance. Hold on to this
’ object as long as you want to access the
’ generated presentations. There can only be a
’ single PowerPoint instance. If there is no
’ PowerPoint running, one will be started.
’ Otherwise the existing one is used.
Dim ppapp As Object
Set ppapp = New PowerPoint.Application
Dim i As Integer
For i = 1 To 10
’ Modify the range value.
’ Note: Avoid selecting the cell prior to
’ changing it. It is very slow and has
’ undesirable side-effects.
’ BAD:
’ rng.Select
’ ActiveWindow.Selection.Value = 0
rng.Value = i
’ Generate a new presentation based on the
’ linked template.
Dim pres As PowerPoint.Presentation
Set pres = tcaddin.PresentationFromTemplate( _
Excel.ActiveWorkbook, "template.ppt", ppapp)
’ If you want to modify the new presentation
’ before saving it this is the place to do it.
’ Save the new presentation
pres.SaveAs "c:\\output" & i & ".ppt"
’ Explicitly close the presentation when we
’ are done with it to free its memory.
’ Letting the object go out of scope is not
’ sufficient.
pres.Close
Next
End Sub
Previous chapter [B Exchanging files with PowerPoint] Next chapter [D Keyboard shortcuts]
