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#.

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.

E1
UpdateChart
E2
PresentationFromTemplate

E1 UpdateChart

E1.1 UpdateChart Name property control

Available in the floating toolbar for a chart (see Formatting and style), you can set an arbitrary, unique name to identify the chart when calling the function UpdateChart to update the data for the chart.

E1.2 Signature

tcaddin.UpdateChart( _ 
    pres As PowerPoint.Presentation, _ 
    strName As String, _ 
    rgData As Excel.Range, _ 
    bTransposed As Boolean _ 
)

E1.3 Description

This function updates the chart strName in pres with the numbers contained in rgData. The range rgData must conform to the layout described in Creating a chart from Excel.

The chart name strName is matched case-insensitive. It must have been previously assigned in PowerPoint using the UpdateChart Name property control described above.

If the chart is linked to any Excel data range when invoking this function the link is broken. Afterwards the chart will not be linked to any Excel range.

E1.4 Example

To use this sample, in Excel’s Visual Basic for Applications window, go to Tools, then References and add the Microsoft PowerPoint Object Library.

' When Option Explicit appears in a file, you must 
' explicitly declare all variables using the Dim 
' or ReDim statements. If you attempt to use an 
' undeclared variable name, an error occurs at 
' compile time. 
' Use Option Explicit to avoid incorrectly typing 
' the name of an existing variable or to avoid 
' confusion in code where the scope of the 
' variable is not clear. If you do not use the 
' Option Explicit statement, all undeclared 
' variables are of Object type. 
' http://msdn.microsoft.com/en-us/ 
' library/y9341s4f%28v=vs.80%29.aspx 
Option Explicit 
 
Sub UpdateChart_Sample() 
 
   ' Get the range containing the new data 
   Dim rng As Excel.Range 
   Set rng = _ 
    ActiveWorkbook.Sheets("Sheet1").Range("A1:D5") 
 
   ' 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 pres As PowerPoint.Presentation 
 
   ' PowerPoint window visible 
   ' Set pres = ppapp.Presentations.Open( _ 
   '  Filename:="c:\\example.pptx", Untitled:=msoTrue) 
 
   ' PowerPoint window invisible 
   Set pres = ppapp.Presentations.Open( _ 
    Filename:="c:\\example.pptx", Untitled:=msoTrue, _ 
    WithWindow:=msoFalse) 
 
   ' The name "No1" must have been 
   ' previously assigned to the chart using 
   ' the control in the floating toolbar. 
   ' The final argument indicates whether 
   ' the data range is transposed or not. 
   Call tcaddin.UpdateChart(pres, "No1", rng, False) 
 
   ' Save the updated presentation 
   pres.SaveAs ("c:\\example_updated.pptx") 
   pres.Close 
 
   ppapp.Quit 
End Sub

using Excel = Microsoft.Office.Interop.Excel; 
using PowerPoint = Microsoft.Office.Interop.PowerPoint; 
using Office = Microsoft.Office.Core; 
// Open the Solution Explorer > right-click the project file > "Add Reference..." button and add he following references: 
// .NET tab > Microsoft.Office.Interop.Excel 12.0.0.0 
// .NET tab > Microsoft.Office.Interop.PowerPoint 12.0.0.0 
// COM tab  > Microsoft Office 14.0 Object Library 
namespace ConsoleApplication_UpdateChart 
{ 
  class Program 
  { 
    static void Main() 
    { 
      Excel.Application xlapp = new Excel.Application(); 
      xlapp.Visible = true; 
 
      Excel.Workbook workbook = xlapp.Workbooks.Add(1); 
      Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1]; 
      worksheet.Cells[3, 1] = "Series 1"; 
      worksheet.Cells[3, 2] = 1; 
      worksheet.Cells[3, 3] = 2; 
      worksheet.Cells[3, 4] = 3; 
 
      PowerPoint.Application ppapp = new PowerPoint.Application(); 
      PowerPoint.Presentation presentation = 
        ppapp.Presentations.Open("C://test.pptx", Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue); 
 
      object[] aobjArg = new object[] { (object)presentation, "ChartNo1", worksheet.get_Range("A1", "D3"), false }; 
 
      Office.COMAddIn comaddin = xlapp.COMAddIns.Item("thinkcell.addin"); 
      object objAddIn = comaddin.Object; 
      objAddIn.GetType().InvokeMember("UpdateChart", System.Reflection.BindingFlags.InvokeMethod, null, objAddIn, aobjArg); 
 
      presentation.SaveAs("C://test_updated.pptx"); 
      presentation.Close(); 
      ppapp.Quit(); 
 
      workbook.Close(false); 
      xlapp.Quit(); 
    } 
  } 
}

Figure E.1: UpdateChart example in C#

E2 PresentationFromTemplate

E2.1 Signature

tcaddin.PresentationFromTemplate( _ 
    wb As Excel.Workbook, _ 
    strTemplate As String, _ 
    ppapp As PowerPoint.Application _ 
) As PowerPoint.Presentation

E2.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 on Top (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.

E2.3 Example

To use this sample, in Excel’s Visual Basic for Applications window, go to Tools, then References and add the Microsoft PowerPoint Object Library.

' When Option Explicit appears in a file, you must 
' explicitly declare all variables using the Dim 
' or ReDim statements. If you attempt to use an 
' undeclared variable name, an error occurs at 
' compile time. 
' Use Option Explicit to avoid incorrectly typing 
' the name of an existing variable or to avoid 
' confusion in code where the scope of the 
' variable is not clear. If you do not use the 
' Option Explicit statement, all undeclared 
' variables are of Object type. 
' http://msdn.microsoft.com/en-us/ 
' library/y9341s4f%28v=vs.80%29.aspx 
Option Explicit 
 
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(3, 2) 
 
   ' 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.pptx", 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 & ".pptx" 
 
      ' 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