Automation with Excel data

On Windows, you can use think-cell API functions to programmatically create think-cell presentations with data from Excel.

Contents

Introduction to automation with Excel data
PresentationFromTemplate
UpdateBatch
UpdateChart (deprecated)

Introduction to automation with Excel data

Windows only

Excel automation is most beneficial when you need to regularly generate large reports without manually updating the data each time. For smaller or ad hoc charting tasks, we recommend using Create elements from Excel.

To create a copy of a PowerPoint template and update all think-cell elements in the copied presentation that are linked to an Excel workbook (see Create elements from Excel), use the PresentationFromTemplate function.

To update specific think-cell elements in a PowerPoint template, regardless of whether they're linked to Excel, use the UpdateBatch API. UpdateBatch has replaced UpdateChart (deprecated). However, if your existing code uses UpdateChart, this code will still work.

The think-cell API and Office Automation

The think-cell API is integrated into Microsoft's Component Object Model (COM), so you can access the API from any language with which you can program Office, such as Visual Basic for Applications (VBA) or C#. For instructions on how to use VBA and C#, see Get started with the think-cell API.

The entry point into think-cell is the think-cell add-in object. You can access the object via the Application.COMAddIns collection. Calls into think-cell are always late-bound (see Microsoft Learn). Thus, the type of the think-cell add-in object is simply Object, and you don't have to add any type library or reference. Once you acquire the object, you're ready to make calls. For example, in VBA in Excel, use the following code:

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. When you declare the reference as var, the compiler infers that the object is type dynamic, so you can simply use the following code. Here, xlapp is a reference to an Excel.Application object that loads think-cell.

var tcXlAddIn = xlapp.COMAddIns.Item("thinkcell.addin").Object;

Match Excel and template data layouts

To correctly display your data in its corresponding chart, your data layout in Excel must match the chart's datasheet layout in the template (see Transpose and edit the data layout of linked ranges). For example, if you add the 100%= row in the template chart, think-cell interprets the second row of your Excel data as the totals from which percentages are calculated.

The following table summarizes common data layout options, their position in the data layout, and their default setting. To check the data layout of a specific chart, double-click the chart to open its datasheet.

Data layout option	Contents	Position	Default setting
Series	Series labels	Column 1	Selected
Category	Category labels	Row 1	Selected
100%=	Totals for percentage calculations	Row 2 (if Category selected) Row 1 (if Category not selected)	Not selected

Match element formatting to Excel

You can match the formatting of your elements—such as number format, fill, and font color—to the formatting in the Excel workbook. Before calling PresentationFromTemplate or UpdateBatch, in your Excel workbook, apply the formatting that you want. Then, in the PowerPoint template, match the element's number format, font formatting, fill, or borders to the Excel workbook (see Match element formatting to the linked data range).

PresentationFromTemplate

Windows only

The PresentationFromTemplate function creates a copy of a PowerPoint template and updates all think-cell elements in the copied presentation that are linked to an Excel workbook. For more information on linked think-cell elements, see Create elements from Excel.

PresentationFromTemplate signature

VBA

tcXlAddIn.PresentationFromTemplate( _ 
	Workbook As Excel.Workbook, _ 
	Template As String, _ 
	PpApplication As PowerPoint.Application _ 
) As PowerPoint.Presentation

C#

PowerPoint.Presentation tcXlAddIn.PresentationFromTemplate(
	Excel.Workbook Workbook,
	string Template,
	PowerPoint.Application PpApplication
);

PresentationFromTemplate description

PresentationFromTemplate takes the following arguments:

Workbook refers to the Excel workbook that contains your data.
Template refers to the PowerPoint template that contains the elements that you want to fill with data. To fill an element with data, it must be linked to a range in Workbook (see Create elements from Excel). Template can specify a relative path from the location of Workbook or an absolute path.
PpApplication refers to the PowerPoint instance that generates the new presentation.

PresentationAsTemplate creates a copy of Template and updates all elements in the copied presentation that are linked to Workbook, regardless of whether you have set the elements to update automatically (see Manage the data in linked elements).

In the new presentation, PresentationFromTemplate intentionally breaks the updated elements' Excel links to prevent any inadvertent further updates. However, elements that the function doesn't update keep their Excel links. This means that you can update links from multiple Excel workbooks. To do so, after you create your new presentation, call PresentationFromTemplate again with your next workbook and use the presentation that you just created as the template.

PresentationFromTemplate examples

To use these examples, first create a presentation containing a stacked column chart linked to the range G1:K4 in the first sheet in an Excel workbook (see Create charts from Excel). Save the presentation as C:\Samples\PresentationFromTemplate\template.pptx and the workbook as data.xlsx in the same directory.

VBA

In this example, in Sheet1, the procedure PresentationFromTemplate_Sample() changes the value of H3 from i=1 to 10. H3 is linked to the first value in the first series of the stacked column chart. For each new value of H3, PresentationFromTemplate_Sample() does the following:

Makes a copy of template.pptx.
In the copied presentation, updates the chart with the new value.
Breaks the chart's Excel link.
In the same directory as the template, saves the new presentation as output_i.pptx.

To use this example, in the Excel workbook data.xlsx that you created earlier, create a module with the following procedure. The module requires a reference to the Microsoft PowerPoint 16.0 Object Library (see Visual Basic for Applications).

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 start.
	' 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 Sub

C#

In this example, the program opens Excel and loads data.xlsx. Then, in Sheet1, the program changes the value of H3 from i=1 to 10. H3 is linked to the first value in the first series of the stacked column chart. For each new value of H3, the program does the following:

Makes a copy of template.pptx.
In the copied presentation, updates the chart with the new value.
Breaks the chart's Excel link.
In the same directory as the template, saves the new presentation as output_i.pptx.

To use this example, in the C# Console App project template, replace the code in Program.cs with the following code. The program 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 C#).

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();
		}
	}
}

UpdateBatch

Windows only

With the UpdateBatch API, specify which think-cell elements in a PowerPoint template will update with new Excel data. You can update elements regardless of whether they're linked to Excel.

UpdateBatch has replaced UpdateChart (deprecated). Although existing code that uses UpdateChart will still work, here's why we recommend that you use UpdateBatch instead:

New functionality, such as filling images of tables with Excel data (see Insert images of tables), is only available in UpdateBatch.
With newer think-cell versions, UpdateChart can be very slow, especially with large presentations.

UpdateBatch signatures

VBA

tcXlAddIn.CreateUpdate() As Object

tcUpdate.AddRangeData(
	Target As Object, _
	Name As String, _
	Range As Excel.Range, _
	Transposed As Boolean _
)

tcUpdate.AddRangeImage(
	Target As Object, _
	Name As String, _
	Range As Excel.Range _
)

tcUpdate.Send()

C#

object tcXlAddIn.CreateUpdate();

void tcUpdate.AddRangeData(
	object Target,
	string Name,
	Excel.Range Range,
	bool Transposed
);

void tcUpdate.AddRangeImage(
	object Target,
	string Name,
	Excel.Range Range
);

void tcUpdate.Send();

UpdateBatch description

UpdateBatch contains the following functions:

CreateUpdate creates an object to store all the elements that you want to update and their corresponding Excel data ranges.
AddRangeData schedules an update for a chart, table, automation text field, Harvey ball, checkbox, or image that you want to fill with Excel data.
AddRangeImage schedules an update for an image of a table that you want to fill with Excel data (see Insert images of tables).
Send collects the Excel data and sends it to PowerPoint to create a presentation. If your code targets Excel-linked elements in your PowerPoint template, these links will break (see Create elements from Excel).

AddRangeData and AddRangeImage take the following arguments:

Target refers to the PowerPoint template that contains the elements that you want to fill with data. Target can be a Presentation, SlideRange, Slide, Master, or CustomLayout.
Name refers to an element with an AddRangeData or AddRangeImage name. Before you run UpdateBatch, you must assign the name in the PowerPoint template using AddRangeData Name or AddRangeImage Name (see Create PowerPoint templates for report automation). Names are case-insensitive. If two elements within Target have the same name, think-cell fills them with the same data.
Range refers to the Excel range that contains the data for the element.
Transposed (AddRangeData only): To swap the orientation of the rows and columns in the updated element's datasheet (see Transpose and edit the data layout of linked ranges), set Transposed to True. If the orientation of the element's data matches the orientation of the Excel data, set Transposed to False.

UpdateBatch examples

The following VBA and C# code examples update three elements in the PowerPoint template with the data in the first sheet of an Excel workbook. The examples save the resulting presentation as template_updated.pptx.

To use these examples, create a PowerPoint template with think-cell elements and an Excel workbook with data to fill the elements.

To create the PowerPoint template, follow these steps:

Create a PowerPoint presentation with a slide title and a chart as described in PowerPoint template example.
In the presentation, add an image of a table (see Insert images of tables).
Open the table's mini toolbar. In AddRangeImage Name, enter TableAsImage1. Select Enter.
Save the presentation to C:\Samples\UpdateBatch\template.pptx.

To create the Excel workbook, follow these steps:

Open an Excel workbook.
In A1, enter a slide title.
In A2:D5, enter chart data. Make sure that the data layout in Excel matches the chart's datasheet layout in the PowerPoint template (see Match Excel and template data layouts).
In A7:D10, enter table data. Any table formatting that you apply, such as fills and borders, will appear in the new presentation.
Save the workbook to C:\Samples\UpdateBatch\data.xlsx.

When specifying paths in your UpdateBatch procedures, use a double backslash (\\) as the directory separator.

VBA

To use this code example, in the Excel workbook that you created earlier, create a module with the following procedure. The module requires a reference to the Microsoft PowerPoint 16.0 Object Library. For more information, see Visual Basic for Applications.

Option Explicit

Sub UpdateBatch_Sample()
	
	' Get the ranges that contain the new data
	Dim rngTitle As Excel.Range
	Set rngTitle = ActiveWorkbook.Sheets(1).Range("A1")
	Dim rngChart As Excel.Range
	Set rngChart = ActiveWorkbook.Sheets(1).Range("A2:D5")
	Dim rngTableAsImage As Excel.Range
	Set rngTableAsImage = ActiveWorkbook.Sheets(1).Range("A7:D10")

	' Get the think-cell add-in object
	Dim tcXlAddIn As Object
	Set tcXlAddIn = Application.COMAddIns("thinkcell.addin").Object

	' Get a PowerPoint instance. Hold onto this object as long as
	' you want to access the new presentation. There can only be one
	' PowerPoint instance. If there is no instance, the script will
	' start one. Otherwise, the script uses the existing instance.
	Dim ppapp As Object
	Set ppapp = New PowerPoint.Application

	Dim pres As PowerPoint.Presentation

	' When the script ends, this code opens the new presentation
	' so that you can edit it further. To keep the presentation open,
	' also remove pres.Close and ppapp.Quit.
	' Set pres = ppapp.Presentations.Open( _
	' 	Filename:="C:\\Samples\\UpdateBatch\\template.pptx", _
	' 	Untitled:=msoTrue _
	' )

	' When the script ends, this code does not open the new presentation.
	Set pres = ppapp.Presentations.Open( _
		Filename:="C:\\Samples\\UpdateBatch\\template.pptx", _
		Untitled:=msoTrue, _
		WithWindow:=msoFalse _
	)

	Dim tcUpdate As Object
	Set tcUpdate = tcXlAddIn.CreateUpdate
          
	Call tcUpdate.AddRangeData(pres, "Title", rngTitle, False)
	Call tcUpdate.AddRangeData(pres, "Chart1", rngChart, False)
	Call tcUpdate.AddRangeImage(pres, "TableAsImage1", rngTableAsImage)
	Call tcUpdate.Send

	pres.SaveAs("C:\\Samples\\UpdateBatch\\template_updated.pptx")
	pres.Close

	ppapp.Quit

End Sub

C#

To use this code example, in the C# Console App project template, replace the code in Program.cs with the following code.

The program 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. For more information, see C#.

using Excel = Microsoft.Office.Interop.Excel;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace ConsoleApplication_UpdateBatch
{
	class Program
	{
		static void Main()
		{
			
			PowerPoint.Application ppapp = new PowerPoint.Application();
			PowerPoint.Presentation presentation = ppapp.Presentations.Open(
				"C:\\Samples\\UpdateBatch\\template.pptx",
				Office.MsoTriState.msoFalse,
				Office.MsoTriState.msoTrue
			);
			
			Excel.Application xlapp = new Excel.Application { Visible = true };
			Excel.Workbook workbook = xlapp.Workbooks.Open(
				"C:\\Samples\\UpdateBatch\\data.xlsx",
				Office.MsoTriState.msoFalse,
				Office.MsoTriState.msoTrue
			);
			Excel.Worksheet worksheet = workbook.Sheets[1];
			
			var tcXlAddIn = xlapp.COMAddIns.Item("thinkcell.addin").Object;
			var tcUpdate = tcXlAddIn.CreateUpdate();
			
			tcUpdate.AddRangeData(
				presentation,
				"SlideTitle",
				worksheet.get_Range("A1"),
				true
			);
			
			tcUpdate.AddRangeData(
				presentation,
				"Chart1",
				worksheet.get_Range("A2", "D5"),
				true
			);
			
			tcUpdate.AddRangeImage(
				presentation,
				"TableAsImage1",
				worksheet.get_Range("A7", "D10")
			);
			
			tcUpdate.Send();
			
			presentation.SaveAs("C:\\Samples\\UpdateBatch\\template_updated.pptx");
			presentation.Close();
			ppapp.Quit();
			
			workbook.Close(false);
			xlapp.Quit();
		}
	}
}

UpdateChart (deprecated)

Windows only

UpdateChart updates the datasheet of a specific element with your Excel data.

UpdateBatch has replaced UpdateChart (see UpdateBatch). Although existing code that uses UpdateChart will still work, here's why we recommend that you use UpdateBatch for new code instead:

New functionality, such as filling images of tables with Excel data (see Insert images of tables), is only available in UpdateBatch.
With newer think-cell versions, UpdateChart can be very slow, especially with large presentations.

UpdateChart signature

VBA

tcXlAddIn.UpdateChart( _
	Target As Object, _
	ChartName As String, _
	Range As Excel.Range, _
	Transposed As Boolean _
)

C#

void tcXlAddIn.UpdateChart(
	object Target,
	string ChartName,
	Excel.Range Range,
	bool Transposed
);