Creating charts in Word and PowerPoint using newly introduced Object Model in Office 2007 Service Pack 2

Office 2007 SP2 has added a major inclusion to the Object Models of Word and PowerPoint, i.e., Object Model for Charts. This new object model (OM) provides office application programmers flexibility to manipulate charts programmatically in Word and PowerPoint.

The new OM in Word and PowerPoint is similar to the one that existed for Excel prior to Office SP2. Chart objects can be accessed using the Shape.Chart property. Every Chart object contains a ChartData property which refers to the Excel worksheet which holds the data for the chart. Once you get hold of the worksheet which holds a chart’s data, you can use Excel’s object model to manipulate that data.

In this post, we will try to add charts to Word document using this newly added object model. But, before we dive into the coding part, make sure that Service Pack 2 for Microsoft Office 2007 is installed on your machine. Also, I will be using Visual Studio 2008; however, the steps mentioned here should also work for Visual Studio 2005. In case you face any difficulty, feel free to ask me.

First of all create a new Windows Forms application in your favorite .Net programming language (i.e. C# or VB.Net). Add a new button to the form, and double click it to add the code in the Click event handler’s for it.

Next step is to add references to the required assemblies for enabling .Net code automate Word and PowerPoint. For this, right click on the References folder in your Solution Explorer (if you have created a VB.Net project, you may have to click Show All Files for making the References folder visible). Now select the COM tab in the Add reference dialog box that got opened, and search for following entries:

• Microsoft Excel 12.0 Object Library
• Microsoft PowerPoint 12.0 Object Library
• Microsoft Word 12.0 Object Library

Now, you’ll be asking why we need to select these entries from the COM tab rather than the .Net tab, as we have the Primary Interop Assemblies installed on our machines, and why in the world, do we need Excel object library for?

Well for your first question, as you know Visual Studio 2008 uses its own set of Office Primary Interop Assemblies (PIAs) when you compile and link your projects and not the ones that are installed in your system’s Global Assembly Cache (GAC), selecting assemblies from the COM tab (rather than the .Net tab) ensures that Visual studio will not compile/link your project with its own set of PIAs, but will generate ones from the corresponding COM type libraries. If we do not follow this step, Visual Studio will try to link our code with its own copy of PIAs, which do not contain declarations of the new Office SP2 object model.

And, for your second question, the Chart object model in SP2 has added a new property to the Chart object called ChartData, which (as you might have guessed) represents the data of a chart. This ChartData has a property named Workbook which corresponds to the Excel Workbook containing the data of your chart. Therefore, we need the Excel object library to be able to interact with this workbook programmatically.

Now, let’s first write the code require to insert a chart, set its data, and set some of its visible properties. We will discuss about the new classes/properties of the new object model later.

Below is an example, which shows how to insert a chart to a Word document, accesses chart’s underlying data’s workbook, modify that data and finally sets some of its other properties. (Line numbers mentioned are just for indicative purpose.)

    1:  Word.Application word = null;

    2:  word = new Word.Application();

    3:  word.Visible = true;

    4:  Word.Document doc = word.Documents.Add(ref missing, ref missing, ref missing, ref missing);

    5:  Word.Chart wdChart = doc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn , ref missing).Chart;

    6:  Word.ChartData chartData = wdChart.ChartData;

    7:  Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;

    8:  Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1];

    9:  Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");

   10:  Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];

   11:  tbl1.Resize(tRange);

   12:  ((Excel.Range)dataSheet.Cells.get_Range("A2", missing)).FormulaR1C1 = "Bikes";

   13:  ((Excel.Range)dataSheet.Cells.get_Range("A3", missing)).FormulaR1C1 = "Accessories";

   14:  ((Excel.Range)dataSheet.Cells.get_Range("A4", missing)).FormulaR1C1 = "Repairs";

   15:  ((Excel.Range)dataSheet.Cells.get_Range("A5", missing)).FormulaR1C1 = "Clothing";

   16:  ((Excel.Range)dataSheet.Cells.get_Range("B2", missing)).FormulaR1C1 = "1000";

   17:  ((Excel.Range)dataSheet.Cells.get_Range("B3", missing)).FormulaR1C1 = "2500";

   18:  ((Excel.Range)dataSheet.Cells.get_Range("B4", missing)).FormulaR1C1 = "4000";

   19:  ((Excel.Range)dataSheet.Cells.get_Range("B5", missing)).FormulaR1C1 = "3000";

   20:  wdChart.ChartTitle.Font.Italic = true;

   21:  wdChart.ChartTitle.Font.Size = 18;

   22:  wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();

   23:  wdChart.ChartTitle.Text = "2007 Sales";

   24:  wdChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;

   25:  wdChart.ChartTitle.Format.Line.ForeColor.RGB = Color.Black.ToArgb();

   26:  wdChart.ApplyDataLabels(Word.XlDataLabelsType.xlDataLabelsShowLabel, missing, missing, missing, missing, missing, missing, missing, missing, missing);

   27:  dataWorkbook.Application.Quit();

As you might have guessed, the main class here is the Chart class. We can get an object of Chart class by accessing the Shape.Chart property. The call to Document.InlineShapes.AddChart() method returns a Shape object and we have accessed its Chart object in line no 5.

The next object in question is the ChartData object. We require this object to access the underlying workbook containing all the data of our chart, and we have accessed it using ChartData.Workbook property.

After getting the Workbook object of the ChartData, the next task is just using object model of Excel to insert/update data in its cells (lines 12 to 19).

Once we are finished with filling the data in the Excel workbook, we set some of the visible properties of chart like, Font, Text and Style of its Title; enable display of labels on the Chart (which are the bars in our example). After doing all this, the last task is to quit the Excel application (which automatically got created when we accessed the ChartData.Workbook object).

Here is how the final chart looks like after running the above code:

Comments

• Anonymous
  June 19, 2009
  Hi Kindly let me know if this thing works with Powerpoint coz all the things mentioned in the code suggest that it is for excel Regards Dhaval

• Anonymous
  June 19, 2009
  Hi Dhaval, Yes, this feature works for PowerPoint and Word. Both these products now have support for Object Model (programmatic) access to Charts, which are essentially Excel Charts. Once you have gained access to the Chart object, you can access the underlying data for the Chart (which is now in Excel) using {ChartObjectVariable}.ChartData.Workbook and manipulate it according to your need. HTH, Praveen

• Anonymous
  June 19, 2009
  Hi Praveen Thanks a lot for ur previous information. Now i m a newbie in this Office products development. I am stuck @ creating a Grouped Bar Chart for Powerpoint. I am able to insert chart in my PPT but i dont know how to insert my data in that chart. I am using VS 2005 and Office 2003.. Any help in that matter wud be of great help. Thanks in advance. Regards Dhaval

• Anonymous
  June 19, 2009
  Hi Dhaval, This blog post was specifically for Office 2007 SP2 and does not apply to Office 2003. Office 2003 (PowerPoint 2003) uses MSGraph and not Excel for charting. You can look at one example of manipulating data in the MSGraph here: ACC2000: How to Create an MS Graph in PowerPoint Using Access http://support.microsoft.com/kb/200551 Don't be confused that the article talks about Access, all you need to be looking at the portion of the code which manipulates the data from MSGraph... Look at the code that says:         ' Set the reference to the datasheet collection.         Set oDataSheet = shpGraph.Application.DataSheet You can also find other useful articles if you search for "MSGraph.Chart" (with quotes) for e.g. http://support.microsoft.com/search/default.aspx?query=%22MSGraph.Chart%22&catalog=LCID%3D1033&mode=r HTH, Praveen

• Anonymous
  July 14, 2009
  Does this code works if targeted machine have NOT EXCEL 2007 installed (in other words,only MS WORD is installed on targeted machines)? Thx, LB

• Anonymous
  July 14, 2009
  The comment has been removed

• Anonymous
  July 28, 2009
  Hi Manvir, Wow, nice article - this potentially solves the only flaw in the project I am working on - being able to bridge the gap from the DrawingML chart to the underlying workbook. I am writing a VSTO add in for Office2007 in VS2008. When I change the Word object library from the PIA to the COM libs, I get an error: Description: "An assembly with the same identity 'office, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' has already been imported. Try removing one of the duplicate references." File: "c:Program FilesMicrosoft Visual Studio 9.0Visual Studio Tools for OfficePIAOffice12Office.dll" I realise that taking a reference from the PIA is a bad idea, but when I remove this ref (appears as "Office" in the project explorer) multiple things break in the code (e.g. the Microsoft.Office.Core namespace disappears), and I don't know what I should reference instead (I searched the computer but I only found the DLL in the PIA folder of VSTO). I continued experimenting with different configurations but can't find a comination that works. Are the COM library references up to the task of providing all the functionality that my VSTO plugin needs? Or is there a way to get the COM and PIA libraries to work together in harmony? If have any suggestions I would be really super grateful!! Thanks a lot! Huw

• Anonymous
  July 28, 2009
  Hi Huw, Just for your information, the references that VSTO projects use by default are a local copy of PIAs which is used for compiling the projects. At runtime, projects use assemblies from the GAC. So, In order to compile your VSTO project against GAC assemblies, you will need to replace two references in the VSTO project:  Microsoft.Office.Interop.Word (which refers to location "C:Program Files (x86)Microsoft Visual Studio 9.0Visual Studio Tools for OfficePIAOffice12Microsoft.Office.Interop.Word.dll") with "Microsoft Word 12.0 Object Library" from the COM Tab.Office (which refers to "C:Program Files (x86)Microsoft Visual Studio 9.0Visual Studio Tools for OfficePIAOffice12Office.dll" location), with "Microsoft Office 12.0 Object Library" from the COM Tab.From the description of the error that you are getting, it seems that you have two references added for the Office namespace. Just delete the one referring to the VSTO folder copy, and you should be able to compile your project. For your second question, the COM library references are capable to provide all the functionality needed to run VSTO add-ins. Also, this will require the latest PIAs available on the end user's machine. Unfortunately, there is no separate PIA setup available for SP2 updates. Therefore, you will have to distribute your copy with your add-in's setup. HTH, Manvir Singh

• Anonymous
  July 28, 2009
  Hi again, my bad - I just had to add the COM lib "Microsoft Office 12.0 Object Library" and it seems to put the missing pieces in place. Sorry to spam with these posts but I'm having another difficulty and I was wondering if I could ask you about it. I put the following code into a button click event in my addin:            if (Application.Selection.Type == WdSelectionType.wdSelectionInlineShape)            {                InlineShape shp = (InlineShape)Application.Selection.InlineShapes[1];                if (shp.HasChart == MsoTriState.msoTrue)                {                    Word.Chart theChart = shp.Chart;                    ChartData cd = theChart.ChartData;                    Workbook theWorkbook = (Workbook)cd.Workbook;                }            } I get a runtime crash on the last line - the exception has the message: Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL)) There is no inner exception. Again if you have any ideas I would be really glad!! Thanks Huw

• Anonymous
  July 28, 2009
  Hi Huw, I am not sure why you are getting this error. I think a more appropriate place for this would be at the VSTO forum http://social.msdn.microsoft.com/Forums/en-US/vsto/threads Regards, Manvir Singh

• Anonymous
  July 28, 2009
  The comment has been removed

• Anonymous
  August 03, 2009
  Hi Manvir, I've the same problem! Did you solve it? Thanks a lot Jose

• Anonymous
  August 04, 2009
  Hi Jose and Huw, I was able to reproduce the scenario where you get the HRESULT: 0x80004005 (E_FAIL))error while accessing the ChartData.Workbook property from a VSTO add-in. I also saw the same behavior from an IDTExtensibility based add-in (COM add-in). I was able to avoid this by calling ChartData.Activate() prior to accessing the ChartData.Workbook property. HTH, Manvir Singh

• Anonymous
  August 27, 2009
  hi , i am able to insert graphs in to word document by using above approach, but the data in the work sheet in not reflected in the actual chart. when i open the excel chart in edit mode then i data reflects in the graph. is there any way to refresh the graph on the fly. Thanks, -K

• Anonymous
  September 02, 2009
  You probably need a: wdChart.Refresh(); to make the Word/PowerPoint-Document reflect your changes. Regards, Pillae

• Anonymous
  September 25, 2009
  The comment has been removed

• Anonymous
  September 25, 2009
  How do I access Word.DataLabel and/or Word.DataLabels objects in the chart??

• Anonymous
  September 25, 2009
  Hi Gene, Thank you for your query! I am looking into this, and will post an update soon. Regards, Manvir Singh

• Anonymous
  September 25, 2009
  Hi Gene, Here is a snippet showing how to set DataLabel format:    Sub test_datalabel()        Dim oChart As Chart        Dim oSeries As Series        Dim oDataLabels As DataLabels        Set oChart = ActiveDocument.InlineShapes(1).Chart        Set oSeries = oChart.SeriesCollection(1)        Set oDataLabels = oSeries.DataLabels        oDataLabels.NumberFormat = "#,##0.00"    End Sub Hope this helps! Manvir Singh

• Anonymous
  May 05, 2010
  The comment has been removed

• Anonymous
  May 28, 2010
  Hi Imran, I am sorry for the delay in getting back to you. Could you paste some of the code snippets that you have used, and are not working for you? Thanks, Manvir Singh

• Anonymous
  March 04, 2011
  Hi, I read ur article and its very useful to me i want to know how can i use power presentation  instead of word..Please tell me its very urgent... Thanks Hh

• Anonymous
  March 06, 2011
  Hi Hh, The same logic should work fine when used with PowerPoint (e.g. using PowerPoint.Application instead of Word.Application, PowerPoint.Slide instead of Word.Document etc.). Is there any specific difficulty that you are facing in applying this to PowerPoint 2007? Regards, Manvir Singh

• Anonymous
  September 29, 2012
  The comment has been removed

• Anonymous
  October 02, 2012
  Hi Revaz, You can go to the end of document by calling Selection.EndKey, something like :- object oWdStory = Word.WdUnits.wdStory; object oWdMove = Word.WdMovementType.wdMove; doc.ActiveWindow.Selection.EndKey(ref oWdStory, ref oWdMove); and then insert the chart by calling Selection.InlineShapes.AddChart. HTH, Praveen.

• Anonymous
  May 27, 2014
  Hi Manvir, As you know, the excel sheet will be open when we use AddChart method to insert a chart. I want to know that is there any way to automatically close excel windows. Thanks, Qiang

• Anonymous
  May 27, 2014
  Hi Qiang, The Excel application should close since the code is calling "dataWorkbook.Application.Quit();". If for some reason it is not closing for you, then you may be running into .NET COM ref counting issue. Please refer to this KB and make sure to release all references :- Office application does not quit after automation from Visual Studio .NET client support.microsoft.com/.../317109 HTH, Praveen.