25. Automation with Excel data
When data is provided in Excel as described in Excel data links, you can use the
functions UpdateChart and PresentationFromTemplate to programmatically control
using that data.
With UpdateChart you exchange the datasheet of a specific chart with your data.
With PresentationFromTemplate you use your data to create a new presentation
based on a PowerPoint template with think-cell charts linked to data ranges in
Excel.
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 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
- 25.1
- UpdateChart
- 25.2
- PresentationFromTemplate
25.1 UpdateChart
25.1.1 Signature
tcaddin.UpdateChart( _
pres As PowerPoint.Presentation, _
strName As String, _
rgData As Excel.Range, _
bTransposed As Boolean _
)
25.1.2 Description
Called from Excel, 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.
Note that the presentation object pres can also be used to refer to a slide range in
a presentation. For more information, see:
http://msdn.microsoft.com/en-us/vba/powerpoint-vba/articles/slide-object-powerpoint
The chart name strName is matched case-insensitive. It must have been
previously assigned in PowerPoint using the UpdateChart Name property control as
described in Introduction to automation. The chart name has to be unique within the
presentation or slide range defined by pres.
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.
25.1.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 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:\\template.pptx", _
' Untitled:=msoTrue)
' PowerPoint window invisible
Set pres = ppapp.Presentations.Open( _
Filename:="c:\\template.pptx", _
Untitled:=msoTrue, _
WithWindow:=msoFalse)
' The name "Chart1" 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, "Chart1", rng, False)
' Save the updated presentation
pres.SaveAs ("c:\\template_updated.pptx")
pres.Close
ppapp.Quit
End Sub
The next example shows how to use UpdateChart from C#.
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://template.pptx", Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue);
object[] aobjArg = new object[] { (object)presentation, "Chart1", 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://template_updated.pptx");
presentation.Close();
ppapp.Quit();
workbook.Close(false);
xlapp.Quit();
}
}
}
25.2 PresentationFromTemplate
25.2.1 Signature
tcaddin.PresentationFromTemplate( _
wb As Excel.Workbook, _
strTemplate As String, _
ppapp As PowerPoint.Application _
) As PowerPoint.Presentation
25.2.2 Description
Called from Excel, 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 Use 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.
25.2.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