25. Automation with Excel data
When data is provided in Excel, you can use the functions UpdateChart and PresentationFromTemplate to programmatically control using that data.
With UpdateChart you exchange the datasheet of a specific element with your Excel data. With PresentationFromTemplate you use your data to create a new presentation based on a PowerPoint template with think-cell elements linked to data ranges in Excel as described in 21. Excel data links.
The interface to both functions 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 (VBA) or C#. See F.1 Getting started for detailed instructions.
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:
Using early binding and late binding in Automation
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. For example, in VBA in Excel:
Dim tcXlAddIn As Object
Set tcXlAddIn = Application.COMAddIns("thinkcell.addin").Object In C#, you can achieve late binding by declaring the reference to the think-cell add-in object as dynamic; this is also the type inferred by the compiler when declaring the reference as var, so that you can simply write:
var tcXlAddIn = xlapp.COMAddIns.Item("thinkcell.addin").Object; Here xlapp is a reference to an Excel.Application object in which think-cell is loaded.
Contents
- 25.1
- UpdateChart
- 25.2
- PresentationFromTemplate
25.1 UpdateChart
25.1.1 Signature
VBA
tcXlAddIn.UpdateChart( _
target As Object, _
strName As String, _
rgData As Excel.Range, _
bTransposed As Boolean _
)C#
void tcXlAddIn.UpdateChart(
object target,
string strName,
Excel.Range rgData,
bool bTransposed
);25.1.2 Description
This function updates all elements in target with name strName with the data contained in rgData. For the data to be interpreted correctly for charts, the range rgData must conform to their configured datasheet layout, or its transposed version, see also 21.1 Creating a chart from Excel and 21.2 Fitting the data layout. Whether the default or the transposed version is to be used is indicated by setting bTransposed to false or true, respectively. For elements other than charts, tables for example, the value of bTransposed is ignored.
target must be a Presentation or SlideRange, or a single Slide, Master or CustomLayout .
The name strName is matched case-insensitively. It must have been previously assigned in PowerPoint using the UpdateChart Name property control as described in 24. Introduction to automation.
To ensure that the correct elements are targeted, make sure that they are the only ones with their UpdateChart Name set to strName in the object passed as pres.
If a targeted element is linked to an Excel data range when invoking this function, the link is broken. Afterwards, the element will not be linked to any Excel range.
25.1.3 Examples
To use these samples, prepare a presentation as described in 24. Introduction to automation, and save it as C:\Samples\UpdateChart\template.pptx.
VBA
To use this sample add it to a module in an Excel workbook.
It requires a reference to the Microsoft PowerPoint 16.0 Object Library (see F.1.1 Visual Basic for Applications for details).
Running UpdateChart_Sample in a workbook will update the chart in the presentation template with the data contained in range A1:D5 of its first sheet.
Option Explicit
Sub UpdateChart_Sample()
' Get the range containing the new data
Dim rng As Excel.Range
Set rng = ActiveWorkbook.Sheets(1).Range("A1:D5")
' Get the think-cell add-in object
Dim tcXlAddIn As Object
Set tcXlAddIn = 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:\\Samples\\UpdateChart\\template.pptx", _
' Untitled:=msoTrue)
' PowerPoint window invisible
Set pres = ppapp.Presentations.Open( _
Filename:="C:\\Samples\\UpdateChart\\template.pptx", _
Untitled:=msoTrue, _
WithWindow:=msoFalse)
Call tcXlAddIn.UpdateChart(pres, "Chart1", rng, False)
' Save the updated presentation
pres.SaveAs ("C:\\Samples\\UpdateChart\\template_updated.pptx")
pres.Close
ppapp.Quit
End SubC#
To use this sample, replace the code in Program.cs of the C# Console App project template with it.
It requires references to the Microsoft PowerPoint 16.0 Object Library, the Microsoft Excel 16.0 Object Library and the Microsoft Office 16.0 Object Library (see F.1.2 C# for details).
Running the resulting application will update the chart in the presentation template to contain a single series named "Series 1" with values 1, 2, 3, and save the result as template_updated.pptx.
using Excel = Microsoft.Office.Interop.Excel;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;
namespace ConsoleApplication_UpdateChart
{
class Program
{
static void Main()
{
Excel.Application xlapp = new Excel.Application { 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:\\Samples\\UpdateChart\\template.pptx",
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue
);
var tcXlAddIn = xlapp.COMAddIns.Item("thinkcell.addin").Object;
tcXlAddIn.UpdateChart(
presentation,
"Chart1",
worksheet.get_Range("A1", "D3"),
false
);
presentation.SaveAs("C:\\Samples\\UpdateChart\\template_updated.pptx");
presentation.Close();
ppapp.Quit();
workbook.Close(false);
xlapp.Quit();
}
}
}25.2 PresentationFromTemplate
25.2.1 Signature
VBA
tcXlAddIn.PresentationFromTemplate( _
wb As Excel.Workbook, _
strTemplate As String, _
ppapp As PowerPoint.Application _
) As PowerPoint.PresentationC#
PowerPoint.Presentation tcXlAddIn.PresentationFromTemplate(
Excel.Workbook wb,
string strTemplate,
PowerPoint.Application ppapp
);25.2.2 Description
This function uses the data links between the Excel workbook wb and the template with filename strTemplate to instantiate that template by updating the linked elements with the data from the ranges they are linked to. 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 elements in strTemplate that are linked to the Excel workbook wb are updated (regardless whether they are set to auto-update or not). In the resulting presentation, their data links are broken to prevent further changes to these elements.
Elements 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 or the formatting of table cells with the Excel link, you can set the color scheme to Use Datasheet Fill on Top (see 3.8.2 Color scheme) or the Use Datasheet... options (see 17.3 Formatting a table), respectively. Likewise, to control the number format with the Excel link, set it to Use Excel Format (see 6.5.3 Number format).
Make sure to set the relevant formatting options and the number format of the respective cells in Excel before calling PresentationFromTemplate.
25.2.3 Examples
To use these samples, first create a presentation containing a stacked chart linked to the range G1:K4 of the first sheet in an Excel workbook as explained in 21.1 Creating a chart from Excel. Save the resulting presentation as C:\Samples\PresentationFromTemplate\template.pptx and the workbook as data.xlsx in the same directory.
VBA
To use this sample, add it to a module in the Excel workbook data.xlsx prepared as explained above.
It requires a reference to the Microsoft PowerPoint 16.0 Object Library (see F.1.1 Visual Basic for Applications for details).
Running PresentationFromTemplate_Sample will change the value in cell Sheet1!H3, which is linked to the first value of the first series of the chart contained in template.pptx, from i=1 to 10, create a new presentation with the chart in the template updated to contain that value, and which is not linked to the workbook anymore, and save it as output_i.pptx in the same directory as the template.
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(1).Cells(3, 8)
' Get the think-cell add-in object
Dim tcXlAddIn As Object
Set tcXlAddIn = 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
' GOOD:
rng.Value = i
' Generate a new presentation based on the
' linked template.
Dim pres As PowerPoint.Presentation
Set pres = tcXlAddIn.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:\Samples\PresentationFromTemplate\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 SubC#
To use this sample replace the code in Program.cs of the C# Console App project template with it.
It requires references to the Microsoft PowerPoint 16.0 Object Library, the Microsoft Excel 16.0 Object Library and the Microsoft Office 16.0 Object Library (see F.1.2 C# for details).
Running the resulting application will visibly open Excel, load the workbook data.xlsx, change the value in cell H3, which is linked to the first value of the first series of the chart contained in template.pptx, from i=1 to 10, create a new presentation with the chart in the template updated to contain that value, and which is not linked to the workbook anymore, and save it as output_i.pptx in the same directory as the template.
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication_PresentationFromTemplate
{
class Program
{
static void Main()
{
var xlapp = new Excel.Application { Visible = true };
var tcXlAddIn = xlapp.COMAddIns.Item("thinkcell.addin").Object;
var workbook = xlapp.Workbooks.Open("C:\\Samples\\PresentationFromTemplate\\data.xlsx");
var ppapp = new PowerPoint.Application();
for (var i = 1; i <= 10; ++i)
{
workbook.Sheets[1].Cells[3, 8] = i;
PowerPoint.Presentation presentation = tcXlAddIn.PresentationFromTemplate(
workbook,
"C:\\Samples\\PresentationFromTemplate\\template.pptx",
ppapp
);
presentation.SaveAs("C:\\Samples\\PresentationFromTemplate\\output" + i + ".pptx");
presentation.Close();
}
ppapp.Quit();
workbook.Close(false);
xlapp.Quit();
}
}
}