Inserting Excel Charts into PowerPoint 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Programmatically insert a Microsoft Office Excel 2007 chart into a Microsoft Office PowerPoint 2007 presentation.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007

Joel Krist, Akona Systems

August 2007

In many scenarios where the 2007 Microsoft Office system is used as part of a broader business application, you may extract a chart created in Excel and insert that chart into a slide in a PowerPoint presentation. This Visual How-to article shows how to use the Microsoft Office Excel 12.0 and Microsoft Office PowerPoint 12.0 Object Libraries to programmatically insert a Microsoft Office Excel 2007 chart into a Microsoft Office PowerPoint 2007 presentation.

Watch the Video

Length: 06:50 | Size: 6.35 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

This section walks through six key steps:

Creating an Excel worksheet that contains an embedded chart.
Creating a Windows Console Application project in Microsoft Visual Studio 2005.
Adding references to the Excel 12.0 object library and the PowerPoint 12.0 object library to the project.
Importing the Excel and PowerPoint Primary Interop Assembly namespaces.
Declaring helper variables.
Implementing the chart insertion code.

Create an Excel Worksheet that Contains an Embedded Chart

The first step is to create an Excel worksheet that contains the chart that will be copied and inserted into a PowerPoint presentation.

Figure 1. Excel 2007 Worksheet that Contains an Embedded Chart

Excel Worksheet that Contains an Embedded Chart

To create the worksheet

Start Excel 2007.
Type the following data into the first worksheet, Sheet1.

A

B

C

D

E

1

Q1

Q2

Q3

Q4

2

N. America

1.5

2

1.5

2.5

3

S. America

2

1.75

2

2

4

Europe

2.25

2

2.5

2

5

Asia

2.5

2.5

3

2.75
On the Sheet tab bar, right-click the Sheet1 tab, and then click Rename. Change the name of the worksheet to Quarterly Sales.
Select cells A1:E5.
Select the Insert tab on the ribbon.
Select the Column button in the Charts group, and then choose the 3-D Clustered Column chart type in the 3-D Column category.
When the chart is embedded and selected, the chart tools become available and the Design, Layout, and Format tabs are displayed. Select the Layout tab. In the Properties group, change the name of the chart to Sales Chart.
Select the Chart Title button in the Labels group and choose the Above Chart option. Change the title of the chart to Sales by Quarter.
Select the Axis Titles button in the Labels group and set the Primary Horizontal Axis Title to Title Below Axis. Change the horizontal axis title to Fiscal Quarter.
Select the Axis Titles button in the Labels group and set the Primary Vertical Axis Title to Rotated Title. Change the vertical axis title to Billions.
Save the workbook as Chart Data.xlsx.

Create a Windows Console Application in Visual Studio 2005

This article creates a Windows console application to show how to insert an Excel 2007 chart into a PowerPoint 2007 presentation. However, the following code is not specific to a Windows console application. You could use it in a variety of application types.

To create a Windows Console Application project in Visual Studio 2005

Start Visual Studio.
On the File menu, select NewProject. The New Project dialog box appears.
In the Project Types pane, select Visual C# or Visual Basic, and then select the Windows category.
In the Templates pane, select Console Application.
Specify a name for the project.
Specify a location for the project and click OK.

Visual Studio generates a Windows Console Application project with a single source file in it called Program.cs or Module1.vb, depending on the language selected in step 3 above.

Add References to the Excel 12.0 and PowerPoint Object Libraries

You must add references to the Microsoft Excel 12.0 object library and the Microsoft PowerPoint 12.0 object library to the Visual Studio project in order to program against the Excel and PowerPoint object models.

To add the references

On the Project menu select the Add Reference… menu item. The Add Reference dialog box is displayed as shown in Figure 2.
Select the COM tab in the Add Reference dialog box, then locate and select the Microsoft Excel 12.0 Object Library component.
Scroll down to the Microsoft PowerPoint 12.0 Object Library component and hold the CTRL key down while selecting it to select both components.
Click OK to add the references.

Figure 2. Adding a reference

Add Reference Dialog Box

Import the Excel and PowerPoint Primary Interop Namespaces

Import the Microsoft.Office.Interop.Excel namespace and the Microsoft.Office.Interop.PowerPoint namespace in order to use the objects defined in the namespaces without having to specify the fully qualified namespace paths. To import the namespaces, open the Program.cs or Module1.vb source file and add the following lines to the top of the source file. For a Microsoft Visual Basic project, add the code to the very top of the source file, above the Module statement. For a Microsoft Visual C# project, add the code before the namespace statement and right after the default using statements created by Visual Studio.

Imports xlNS = Microsoft.Office.Interop.Excel
Imports pptNS = Microsoft.Office.Interop.PowerPoint

using xlNS = Microsoft.Office.Interop.Excel;
using pptNS = Microsoft.Office.Interop.PowerPoint;

Declare Helper Variables

For a Visual Basic project, place all of the following variable declarations within Sub Main(). For a C# project, place all of the following variable declarations within the Main() function.

Declare the following variables to hold references to the Excel and PowerPoint objects used in the chart insertion code.

Dim powerpointApplication As pptNS.ApplicationClass = Nothing
Dim pptPresentation As pptNS.Presentation = Nothing
Dim pptSlide As pptNS.Slide = Nothing
Dim shapeRange As pptNS.ShapeRange = Nothing

Dim excelApplication As xlNS.ApplicationClass = Nothing
Dim excelWorkBook As xlNS.Workbook = Nothing
Dim targetSheet As xlNS.Worksheet = Nothing
Dim ChartObjects As xlNS.ChartObjects = Nothing
Dim existingChartObject As xlNS.ChartObject = Nothing

pptNS.ApplicationClass powerpointApplication = null;
pptNS.Presentation pptPresentation = null;
pptNS.Slide pptSlide = null;
pptNS.ShapeRange shapeRange = null;

xlNS.ApplicationClass excelApplication = null;
xlNS.Workbook excelWorkBook = null;
xlNS.Worksheet targetSheet = null;
xlNS.ChartObjects chartObjects = null;
xlNS.ChartObject existingChartObject = null;

Next, add the following statements that declare variables that make the parameters passed to methods used in the chart insertion code easier to read. You can use the paramWorkbookPath variable to specify the path and file name of the Excel workbook that contains the chart to copy, the paramPresentationPath variable to specify the path and file name of the PowerPoint presentation to create and paste the chart into, and the paramMissingvariable to call methods that accept optional parameters.

Replace the Path placeholder in the paramPresentationPath variable with a path to a folder that contains the PowerPoint presentation. Replace the Path placeholder in the paramWorkbookPath variable with the path to the folder that contains the "Chart Data.xlsx" workbook created previously.

Dim paramPresentationPath As String = "Path\Chart Slide.pptx"
Dim paramWorkbookPath As String = "Path\Chart Data.xlstx"

string paramPresentationPath = @"Path\Chart Slide.pptx";
string paramWorkbookPath = @"Path\Chart Data.xlsx";
object paramMissing = Type.Missing;

Note
Optional parameters are only optional when using Microsoft Visual Basic. You must specify a value for optional parameters when using C#. If you use Type.Missing as the value for an optional parameter, this alerts the method being called that you did not specify the parameter and that the method should use the parameter's default value.

Implement the Chart Insertion Code

Next, add the chart insertion code shown below which does the following:

Starts instances of Excel and PowerPoint.
Opens the "Chart Data.xlsx" Excel workbook created previously that contains the chart to copy.
Gets the Excel chart object for the embedded chart named "Sales Chart".
Creates a PowerPoint presentation and adds a slide to it.
Copies the Sales Chart from the Excel worksheet and pastes it to and positions it on the PowerPoint slide.
Saves the PowerPoint presentation.
Closes PowerPoint and Excel.

For a Visual Basic project, place the following code within Sub Main() after the variable declarations added previously. For a C# project, place the following code within the Main() function after the variable declarations added previously.

Try
    ' Create an instance of PowerPoint.
    powerpointApplication = _
        New Microsoft.Office.Interop.PowerPoint.ApplicationClass()

    ' Create an instance Excel.          
    excelApplication = _
        New Microsoft.Office.Interop.Excel.ApplicationClass()

    ' Open the Excel workbook containing the worksheet with the chart data.
    excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath)

    ' Get the worksheet that contains the chart.
    targetSheet = excelWorkBook.Worksheets("Quarterly Sales")

    ' Get the ChartObjects collection for the sheet.
    ChartObjects = targetSheet.ChartObjects()

    ' Get the chart to copy.
    existingChartObject = ChartObjects.Item("Sales Chart")

    ' Create a PowerPoint presentation.
    pptPresentation = powerpointApplication.Presentations.Add()

    ' Add a blank slide to the presentation.
    pptSlide = _
        pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank)

    ' Copy the chart from the Excel worksheet to the clipboard.
    existingChartObject.Copy()

    ' Paste the chart into the PowerPoint presentation.
    shapeRange = pptSlide.Shapes.Paste()

    ' Position the chart on the slide.
    shapeRange.Left = 60
    shapeRange.Top = 100

    ' Save the presentation.
    pptPresentation.SaveAs(paramPresentationPath)
Catch ex As Exception
    Console.WriteLine(ex.Message)
Finally
    ' Release the PowerPoint slide object.
    shapeRange = Nothing
    pptSlide = Nothing

    ' Close and release the Presentation object.
    If Not pptPresentation Is Nothing Then
        pptPresentation.Close()
        pptPresentation = Nothing
    End If

    ' Quit PowerPoint and release the ApplicationClass object.
    If Not powerpointApplication Is Nothing Then
        powerpointApplication.Quit()
        powerpointApplication = Nothing
    End If

    ' Release the Excel objects.
    targetSheet = Nothing
    ChartObjects = Nothing
    existingChartObject = Nothing

    ' Close and release the Excel Workbook object.
    If Not excelWorkBook Is Nothing Then
        excelWorkBook.Close(False)
        excelWorkBook = Nothing
    End If

    ' Quit Excel and release the ApplicationClass object.
    If Not excelApplication Is Nothing Then
        excelApplication.Quit()
        excelApplication = Nothing
    End If

    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Try

try
{
    // Create an instance of PowerPoint.
    powerpointApplication = new pptNS.ApplicationClass();

    // Create an instance Excel.          
    excelApplication = new xlNS.ApplicationClass();

    // Open the Excel workbook containing the worksheet with the chart
    // data.
    excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath,
                    paramMissing, paramMissing, paramMissing, 
                    paramMissing, paramMissing, paramMissing, 
                    paramMissing, paramMissing, paramMissing,
                    paramMissing, paramMissing, paramMissing, 
                    paramMissing, paramMissing);

    // Get the worksheet that contains the chart.
    targetSheet =
        (xlNS.Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]);

    // Get the ChartObjects collection for the sheet.
    chartObjects =
        (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));

    // Get the chart to copy.
    existingChartObject =
        (xlNS.ChartObject)(chartObjects.Item("Sales Chart"));

    // Create a PowerPoint presentation.
    pptPresentation = powerpointApplication.Presentations.Add(
                        Microsoft.Office.Core.MsoTriState.msoTrue);

    // Add a blank slide to the presentation.
    pptSlide = 
        pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank);

    // Copy the chart from the Excel worksheet to the clipboard.
    existingChartObject.Copy();

    // Paste the chart into the PowerPoint presentation.
    shapeRange = pptSlide.Shapes.Paste();

    // Position the chart on the slide.
    shapeRange.Left = 60;
    shapeRange.Top = 100;

    // Save the presentation.
    pptPresentation.SaveAs(paramPresentationPath,
                    pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation,
                    Microsoft.Office.Core.MsoTriState.msoTrue);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
finally
{
    // Release the PowerPoint slide object.
    shapeRange = null;
    pptSlide = null;
                
    // Close and release the Presentation object.
    if (pptPresentation != null)
    {
        pptPresentation.Close();
        pptPresentation = null;
    }

    // Quit PowerPoint and release the ApplicationClass object.
    if (powerpointApplication != null)
    {
        powerpointApplication.Quit();
        powerpointApplication = null;
    }

    // Release the Excel objects.
    targetSheet = null;
    chartObjects = null;
    existingChartObject = null;

    // Close and release the Excel Workbook object.
    if (excelWorkBook != null)
    {
        excelWorkBook.Close(false, paramMissing, paramMissing);
        excelWorkBook = null;
    }

    // Quit Excel and release the ApplicationClass object.
    if (excelApplication != null)
    {
        excelApplication.Quit();
        excelApplication = null;
    }

    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

The shutdown code shown in the Finally block in the previous code closes the PowerPoint and Excel objects and releases references to the underlying Excel and PowerPoint COM objects, allowing Excel and PowerPoint to unload from memory. For more information about releasing COM objects when using managed code, see Chapter 2: Basics of Office Interoperability (Part 2 of 3) from the book, Microsoft .NET Development for Microsoft Office.

Building and running the console application created by the code shown above creates a PowerPoint presentation named "Chart Slide.pptx" that contains a single slide which contains the Sales Chart from the Excel "Chart Data.xlsx" workbook. You can right-click the chart in PowerPoint and select Edit Data to open the "Chart Data.xlsx" Excel workbook. Changes made to the chart data in Excel are reflected on the linked chart on the PowerPoint slide.

Read It

In many business solutions built on top of Excel and PowerPoint, you may want to extract a chart from Excel and paste that chart into a PowerPoint presentation. This article shows how to programmatically insert an Excel 2007 chart into PowerPoint 2007 through these key steps:

Creating an Excel workbook that contains an embedded chart.
Creating a Windows Console Application project in Visual Studio 2005.
Adding references to the Excel 12.0 object library and the PowerPoint 12.0 object library to the project.
Importing the Excel and PowerPoint Primary Interop Assembly namespaces.
Declaring helper variables.
Implementing the chart insertion code that opens an existing workbook that contains a worksheet with an embedded chart, copies the chart to the clipboard, pastes it into a PowerPoint presentation, and then closes Excel and PowerPoint correctly so that they can unload completely from memory.

	A	B	C	D	E
1		Q1	Q2	Q3	Q4
2	N. America	1.5	2	1.5	2.5
3	S. America	2	1.75	2	2
4	Europe	2.25	2	2.5	2
5	Asia	2.5	2.5	3	2.75