Problem using PasteSpecial() for OLEObject in PowerPoint 2010 using VC#

Question

Hi!

I have some working code which creates charts automatically using Excel 2007 with data extracted from MYSQL then pastes those charts to PowerPoint 2007 as embedded objects. All was well and smooth until I upgraded my Office to 2010. Now the chart creation in Excel 2010 still works fine but when the PasteSpecial line tries to paste the ChartArea object it throws up an exception which I cannot decipher.

I have posted the code related to the chart creation in Excel and the pasting in PowerPoint which worked in 2007. I tried using different paste methods, and although they work, they do not allow me to EDIT the Excel as an embedded object. This is a real showstopper for me.

Can someone help me "convert" my code to Office 2010 compatible with as little change as possible (I have a lot of code written this way.)

      public void TrendChart()
      {
          MySqlDataReader reader = null;
          MySqlCommand aCmd = null;

          //OdbcDataReader reader = null;

         PowerPoint.Slide slide = prs.Slides.Add(prs.Slides.Count+1, PpSlideLayout.ppLayoutTitleOnly);
         slide.Shapes.Title.TextFrame.TextRange.Text = "Total Expense at All Subcontractors";

         Excel.Application xl = new Excel.Application();
         xl.Visible = true;

         Excel.Workbook xlWb = xl.Workbooks.Add();
         Excel.Worksheet xlWs = xlWb.Worksheets["Sheet1"];

         String qry = Resources.TtlBillingTrendSQL;

         try
         {
            //aCmd = new OdbcCommand(qry, aConn);
            aCmd = new MySqlCommand(qry, aConn);
            reader = aCmd.ExecuteReader();
            int x = 1;  // used for cell positions

            // Write the titles.
            for (int y = 1; y <= reader.FieldCount; ++y)
               xlWs.Cells[x, y] = reader.GetName(y - 1);

            // Write the details.
            while (reader.Read())
            {
               ++x;
               for (int y = 1; y <= reader.FieldCount; ++y)
                  xlWs.Cells[x, y] = reader[y - 1].ToString();
            }

            Excel.Range rng = xlWs.get_Range("B2", "B" + x);
            rng.NumberFormat = "#,##0.0,,";
            rng.Select();
            Excel.Shape chartShape = xlWs.Shapes.AddChart(XlChartType.xl3DColumnClustered);
            Excel.ChartObject chartObj = xlWs.ChartObjects().Item(chartShape.Name);
            Excel.Chart chart = chartObj.Chart;

            chart.SetSourceData(xlWs.get_Range("A1", "B" + x), PowerPoint.XlRowCol.xlColumns);
            chart.SeriesCollection(1).Delete();
            chart.SeriesCollection(1).XValues = "'Sheet1'!$A$2:$A$" + x;
            chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = 79+129*256+189*256*256;  // represents colour RGB(79, 129, 189)
            chart.HasDataTable = true;
            chart.HasLegend = false;
            chart.HasTitle = false;
            chart.RightAngleAxes = true;
            chart.ChartArea.Left = 20.0F; chart.ChartArea.Top = 82.893F; chart.ChartArea.Width = 623.622F; chart.ChartArea.Height = 396.850F;
            Excel.Axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
            axis.HasTitle = true;
            axis.AxisTitle.Text = "Expenses (MUSD)";
            axis.AxisTitle.Font.Size = 10;
            axis.AxisTitle.Font.Color = XlRgbColor.rgbBlack;
            chartObj.Border.LineStyle = XlLineStyle.xlLineStyleNone;
            chart.ChartArea.Copy();
            slide.Shapes.PasteSpecial(PpPasteDataType.ppPasteOLEObject).Name = "Chart";    // *** Here is the EXCEPTION in Office 2010
            xlWb.Close(false);
            xl.Quit();

            PowerPoint.Shape shape = slide.Shapes["Chart"];
            shape.Left = 20.0F; shape.Top = 82.893F; shape.Width = 623.622F; shape.Height = 396.850F;
         }
         catch (MySqlException ex)
         {
            MessageBox.Show(ex.Message);
         }
         finally
         {
            reader.Close();
         }

         NAR(xlWs);
         NAR(xlWb);
         NAR(xl);
         GC.Collect();
         GC.WaitForPendingFinalizers();
         GC.Collect();
         GC.WaitForPendingFinalizers();
      }

Answer 1

without an excel sheet to play against, I'd probably start by adding an additional catch statement for either a generic exception, or a comexception to get something specific regarding the error (it's probably not a mysqlexception).

al

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Al Edlund Visio MVP

Answer 2

BlakJak, I didn't try your code, I wrote the following code which can achieve your goal:

        static void Main(string[] args)
        {
            CreateNewChartInExcel();
            UseCopyPaste();
            // UseAddChart();
            // UseAddOLEObject();
        }
        static void SetCellValue(xlNS.Worksheet targetSheet, string Cell, object Value)
        {
            targetSheet.get_Range(Cell, Cell).set_Value(xlNS.XlRangeValueDataType.xlRangeValueDefault, Value);
        }

        static void CreateNewChartInExcel()
        {
            // Declare a variable for the Excel ApplicationClass instance.
            Microsoft.Office.Interop.Excel.Application excelApplication = null;

            // Declare variables for the Workbooks.Open method parameters. 
            string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "ChartData.xlsx";
            object paramMissing = Type.Missing;
             
            // Declare variables for the Chart.ChartWizard method.
            object paramChartFormat = 1;
            object paramCategoryLabels = 0;
            object paramSeriesLabels = 0;
            bool paramHasLegend = true;
            object paramTitle = "Sales by Quarter";
            object paramCategoryTitle = "Fiscal Quarter";
            object paramValueTitle = "Billions";

            try
            {
                // Create an instance of the Excel ApplicationClass object.          
                excelApplication = new Microsoft.Office.Interop.Excel.Application();
                excelApplication.Visible = true;
                // Create a new workbook with 1 sheet in it.
                xlNS.Workbook newWorkbook = excelApplication.Workbooks.Add(xlNS.XlWBATemplate.xlWBATWorksheet);

                // Change the name of the sheet.
                xlNS.Worksheet targetSheet = (xlNS.Worksheet)(newWorkbook.Worksheets[1]);
                targetSheet.Name = "Quarterly Sales";

                // Insert some data for the chart into the sheet.
                //              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     2       2.75

                SetCellValue(targetSheet, "A2", "N. America");
                SetCellValue(targetSheet, "A3", "S. America");
                SetCellValue(targetSheet, "A4", "Europe");
                SetCellValue(targetSheet, "A5", "Asia");

                SetCellValue(targetSheet, "B1", "Q1");
                SetCellValue(targetSheet, "B2", 1.5);
                SetCellValue(targetSheet, "B3", 2);
                SetCellValue(targetSheet, "B4", 2.25);
                SetCellValue(targetSheet, "B5", 2.5);

                SetCellValue(targetSheet, "C1", "Q2");
                SetCellValue(targetSheet, "C2", 2);
                SetCellValue(targetSheet, "C3", 1.75);
                SetCellValue(targetSheet, "C4", 2);
                SetCellValue(targetSheet, "C5", 2.5);

                SetCellValue(targetSheet, "D1", "Q3");
                SetCellValue(targetSheet, "D2", 1.5);
                SetCellValue(targetSheet, "D3", 2);
                SetCellValue(targetSheet, "D4", 2.5);
                SetCellValue(targetSheet, "D5", 2);

                SetCellValue(targetSheet, "E1", "Q4");
                SetCellValue(targetSheet, "E2", 2.5);
                SetCellValue(targetSheet, "E3", 2);
                SetCellValue(targetSheet, "E4", 2);
                SetCellValue(targetSheet, "E5", 2.75);

                // Get the range holding the chart data.
                xlNS.Range dataRange = targetSheet.get_Range("A1", "E5");

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

                // Add a Chart to the collection.
                xlNS.ChartObject newChartObject = chartObjects.Add(0, 100, 600, 300);
                newChartObject.Name = "Sales Chart";

                // Create a new chart of the data.
                newChartObject.Chart.ChartWizard(dataRange, xlNS.XlChartType.xl3DColumn, paramChartFormat, xlNS.XlRowCol.xlRows,
                    paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing);

                // Save the workbook.
                newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
                    paramMissing, xlNS.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (excelApplication != null)
                {
                    // Close Excel.
                    excelApplication.Quit();
                }
            }
        }

        static void UseCopyPaste()
        {
            // Declare variables to hold references to PowerPoint objects.
            pptNS.Application powerpointApplication = null;
            pptNS.Presentation pptPresentation = null;
            pptNS.Slide pptSlide = null;
            pptNS.ShapeRange shapeRange = null;

            // Declare variables to hold references to Excel objects.
            xlNS.Application excelApplication = null;
            xlNS.Workbook excelWorkBook = null;
            xlNS.Worksheet targetSheet = null;
            xlNS.ChartObjects chartObjects = null;
            xlNS.ChartObject existingChartObject = null;

            string paramPresentationPath = AppDomain.CurrentDomain.BaseDirectory + "Chart Test.pptx";
            string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "ChartData.xlsx";
            object paramMissing = Type.Missing;

            try
            {
                // Create an instance of PowerPoint.
                powerpointApplication = new pptNS.Application();
                powerpointApplication.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;

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

                // 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 code can create the ChartData.xlsx with the chart in it and then use shapeRange = pptSlide.Shapes.Paste(); to paste the chart to the slide. It works well for Office 2010. 

Just feel free to follow up after you have tried.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Hi Bruce,

Thanks for the idea but in fact I had already tried that and it does not work. My problem is that I am creating temporary XLSX files then pasting the ChartObject to the PPTX file. I then delete the XLSX file.

I want the XLSX file to be an embedded object in the PPTX file. The Paste() function only pastes a linked object so if you delete the underlying XLSX file then you lose the data. You have the chart visible but in the case which I have where I need then to manually touch up some of the data directly in the PPTX file it is impossible.

Using the PasteSpecial(PpPasteDataType.ppPasteOLEObject) function throws an exception without a clear explanation.

It is interesting that in oder to do this function by hand in Powerpoint 2010, you can no longer use the PASTE SPECIAL menu. You have to select PASTE and then select the "Keep Source Formatting & Embed Workbook" option. In Powerpoint 2007 you used PASTE SPECIAL and then pasted as a Microsoft Excel object. If you try that method in 2010 then you end up only pasting a linked object and we are back to square one.

Is this a kind of bug I have found in the VBA or VC# support for the Powerpoint 2010?


Regards.

Answer 4

BlakJak, I also tried on my side to paste it as OLEObject, but also encountered the exception, and I also tried the method mentioned on this thread:
http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/f581cd7b-b2f8-4991-9e26-812dc1e70137

pptSlide.Shapes.PasteSpecial(pptNS.PpPasteDataType.ppPasteOLEObject, Microsoft.Office.Core.MsoTriState.msoFalse);

or

                var view = powerpointApplication.ActiveWindow.View;   
                view.PasteSpecial(pptNS.PpPasteDataType.ppPasteDefault, Microsoft.Office.Core.MsoTriState.msoFalse);

However,they do not work and also throw the exception. I couldn't think a workaround about pasting chart as embed workbook into the PowerPoint. I will help you involve others to help you. There might be some delay about the response.Appreciate your patience.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Thanks, Bruce. I'll monitor the thread regularly for any updates.

Regards

Answer 6

Hi BlakJak,
Here is some additional information that may help you with your issue.

See Bessie Zhao’s solution in this Forum thread.
Programatically paste special Excel chart into Powerpoint as ...
http://social.msdn.microsoft.com/Forums/eu/vbgeneral/thread/f581cd7b-b2f8-4991-9e26-812dc1e70137

The following two links are to the View.PasteSpecial Method of PowerPoint 2010.

View.PasteSpecial Method (PowerPoint) - MSDN – Explore Windows ...
http://msdn.microsoft.com/en-us/library/ff743924.aspx

View.PasteSpecial Method (Microsoft.Office.Interop.PowerPoint)
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.powerpoint.view.pastespecial.aspx

In your call that fails – “slide.Shapes.PasteSpecial(PpPasteDataType.ppPasteOLEObject).Name
= "Chart"; // *** Here is the EXCEPTION in Office 2010”

Consider adding arguments for all of the optional parameters by using ‘,ref ObjectMissing,’

Requisite arguments then include DataType which should be
ppPasteOleObject, and
Link which you set as msoFalse to prevent it from linking to the source file for the data on the clipboard

The PowerPoint 2010 object model for
Microsoft.Office.Interop.PowerPoint.View.PasteSpecial([Microsoft.Office.Interop.PowerPoint.PpPasteDataType],
[Microsoft.Office.Core.MsoTriState], [string], [int], [string],
[Microsoft.Office.Core.MsoTriState])
doesn’t include a parameter for .Name

The object model for 
Microsoft.Office.Interop.PowerPoint.Shapes.PasteSpecial([Microsoft.Office.Interop.PowerPoint.PpPasteDataType],
[Microsoft.Office.Core.MsoTriState], [string], [int], [string],
[Microsoft.Office.Core.MsoTriState])
also doesn’t include a parameter for .Name

Other than these suggestions perhaps other Forum visitors will contribute salient comments.
Regards,
Chris Jensen
Senior Technical Support Lead

---

Chris Jensen

Answer 7

Chris,

I had tried all of those options already but no success. Still faced the same problem hence I came to this forum. However I have found what I think is the source of the problem and it is not obvious at all.

(NOTE:  I found this because I have other chart types such as pie charts and line charts which I discovered worked fine with no change to my code.)

If you look earlier in my code you will see that I am creating a chart type = XlChartType.xl3DColumnClustered

with the line of code:

            Excel.Shape chartShape = xlWs.Shapes.AddChart(XlChartType.xl3DColumnClustered);

It seems that if I use any other chart type then there is no crash when pasting the object to PPTX and embedding it as an OLEObject with PasteSpecial(). I tried xl3DColumn and it works (although now my chart sizing code doesn't seem to work properly.)

I suspect this is a bug in either Excel 2010 or Powerpoint 2010 but at least I have found some form of workaround.

Regards.

Answer 8

Hi,

Further investigation has revealed that xl3DColumnStacked and xl3DColumnClustered both crash.

As said above xl3DColumn works fine however there is one flaw with this and that is the xl3DColumn type does not use the full chart area or plot area. Try making an Excel graph with the different types and see. I have no idea why this is but it seems strange and frankly the xl3DColumn is not very presentable.

I am now exploring ways to programmatically modify the Excel chart type after it is embedded in PowerPoint. I am trying code like this but cannot get it to work.

          PowerPoint.Slide slide = prs.Slides[1];                                 // Note prs is an existing presentation variable to an open PPT.
          PowerPoint.Shape shape = slide.Shapes["Chart"];             // The chart in my PPT files is named "Chart"
          Excel.Workbook wb = shape.OLEFormat.Object;
          wb.Application.Visible = true;                                                              // Here I see an empty workbook on the screen.
          wb.Charts[1].ChartType = Excel.XlChartType.xl3DColumnStacked;
          wb.Close(true);                                                                                    // No change on the PPT chart after this.

I am hoping this is an easier problem to solve than the previous one. Any advice on what I am doing wrong?

Regards.

Answer 9

Hi!

Some more information after even further testing.

I was trying to solve the layout problem on the xl3DColumn and have realised that if you set:

    chart.Autoscaling = false;

prior to copying the ChartArea then the chart will look more like the xl3DColumnClustered or xl3DColumnStacked. However changing the Autoscale option to false on two consecutive xl3DColumn charts results in a crash on the pasting of the second chart. If you paste some other chart type in the middle (e.g. a Pie) then the second xl3DColumn will paste again properly. 

Very strange!!!

I think the best workaround will be if someone can answer my previous post on modifying the ChartType whilst already embedded in the PPTX file.

Regards.

Answer 10

Hi BlakJak,

After you exercise your code to change your chart type you say you see no change to the chart type. That is strange. When I opened the parts of a test presentation with 4 slides, each with a copy of a stacked chart, but only one of the 4 was a 3d stacked type , then edited the XML for chart type in one of the other slides the resulting chart changed from a stack chart to a 3D stacked type. Here is the initial XML for the slide before the the same slide after.

PPT Slide 4
<c:plotArea>
      <c:layout/>
      <c:barChart>
        <c:barDir val="col"/>
        <c:grouping val="stacked"/>
        <c:varyColors val="0"/>
        <c:ser>
          <c:idx val="0"/>
          <c:order val="0"/>

PPT Slide 4
<c:plotArea>
      <c:layout/>
      <c:bar3dChart>
        <c:barDir val="col"/>
        <c:grouping val="stacked"/>
        <c:varyColors val="0"/>
        <c:ser>
          <c:idx val="0"/>
          <c:order val="0"/>

There were other changes throughout the <c:plotArea>...</plotArea> including changing the closing tag of the </c:bar3DChart>

Consider modifying the XML in a chart in a PowerPoint presentation slide to make the chart look as you intended. I use Visual Studio as my XML editor.

Regards,
Chris Jensen
Senior Technical Support Lead

---

Chris Jensen

Answer 11

Chris,

Sorry but I am not clear on your answer. Are you saying my code worked when added to your code?

I want the result of my code to generate a 3DColumStacked chart and this fails in the Copy/Paste-Embedded. I was then trying to workaround pasting a standard 2DColumn chart then once embedded trying to manipulate that in the PPTX file. I can't get the code to change the chart type in the PPTX file to work. The code I used was:

          PowerPoint.Slide slide = prs.Slides[1];                                 // Note prs is an existing presentation variable to an open PPT.
          PowerPoint.Shape shape = slide.Shapes["Chart"];             // The chart in my PPT files is named "Chart"
          Excel.Workbook wb = shape.OLEFormat.Object;
          wb.Application.Visible = true;                                                              // Here I see an empty workbook on the screen.
          wb.Charts[1].ChartType = Excel.XlChartType.xl3DColumnStacked;
          wb.Close(true);                                                                                    // No change on the PPT chart after this.

I do not want to do it manually which you seem to be suggesting in the statement "Consider modifying the XML..."

Sorry if I am not understanding your answer correctly but could you please clarify a bit for me. Thanks.

Regards.

Answer 12

Hi BlakJak,

You say "I think the best workaround will be if someone can answer my previous post on modifying the ChartType whilst already embedded in the PPTX file." My response does mean you'll need to modify the XML, but not manually. You need to programmatically modify the XML between the <c:plotArea> and the </c:plotArea> tags. To know what to change I suggest you manually paste the differently styled charts in two different slides, save the presentation, rename the PPTX file to add the .zip extension to it, then unzip the presentation, compare the XML in the two slides and examine the xml between those <c:plotArea>...</c:plotArea> tags to learn the changes you need to make to change one chart to another. Then write the code to make those.

I hope that clarifies my answer a bit.
Regards,
Chris Jensen
Senior Tecnhical Support Lead

---

Chris Jensen

Answer 13

Chris,

Could you please give me a code example to modify the XML. Thanks.

Regards

Answer 14

Hi BlakJak,

Many code samples are in the Open XML SDK help file, and in the many content pages that have been posted by Microsoft Developers and others. Please start with the subject by installing the Open XML SDK, and then reviewing the content at the links below.

Open XML SDK Documentation | Developer, Microsoft Office, 2010,
2007
http://msdn.microsoft.com/en-us/office/ee441239

Download details: Open XML SDK 2.0 for Microsoft Office
http://www.microsoft.com/downloads/details.aspx?FamilyID=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&amp;amp;displaylang=e

By default The Open XML SDK will be installed on your system at C:\Program Files\Open XML SDK\V2.0\
In the Open XML SDK v2 ‘doc’ folder click on the only contents, i.e.
OpenXMLSDK.chm file.  This opens with a tabbed panel at the left. On the ‘Contents” tab expand the ‘How Do I…” node, then expand the ‘Presentations leaf where you will see topics including “How to
Insert a New Slide into a Presentation” and “How to: Change the Fill Color of a Shape in a Presentation.”

There are other topics you may examine to see code samples and other information that will answer your request.

If you’re not familiar with the Open XML SDK the links below offer
content to help you get oriented to the SDK and its use.

Open XML Developer Center | Microsoft Office, SDK, code, how-to
http://msdn.microsoft.com/en-us/office/bb265236

Welcome to the Open XML SDK 2.0 for Microsoft Office
http://msdn.microsoft.com/en-us/library/bb448854.aspx

What's New in the Open XML SDK 2.0 for Microsoft Office
http://msdn.microsoft.com/en-us/library/cc471858.aspx

Open XML SDK... The Basics - Brian Jones & Zeyad Rajabi: Office ...
http://blogs.msdn.com/b/brian_jones/archive/2009/01/12/open-xml-sdk-the-basics.aspx

Getting Started with the Open XML SDK 2.0 for Microsoft Office
http://msdn.microsoft.com/en-us/library/bb456488.aspx

Using the Open XML SDK - Eric White's Blog - Site Home - MSDN Blogs
http://blogs.msdn.com/b/ericwhite/archive/2008/04/22/using-the-open-xml-sdk.aspx

Open XML SDK Code Snippets - Brian Jones & Zeyad Rajabi: Office ...
http://blogs.msdn.com/b/brian_jones/archive/2009/09/17/open-xml-sdk-code-snippets.aspx

Regards,
Chris Jensen
Senior Technical Support Lead

---

Chris Jensen

Answer 15

Here is the VB.NET version.

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Microsoft.Office.Interop.PowerPoint
Imports xlNS = Microsoft.Office.Interop.Excel
Imports pptNS = Microsoft.Office.Interop.PowerPoint

Module Module1
    Sub Main(ByVal args As String())
        CreateNewChartInExcel()
        UseCopyPaste()
    End Sub
    Sub SetCellValue(ByVal targetSheet As xlNS.Worksheet, ByVal Cell As String, ByVal Value As Object)
        Try
            targetSheet.Range(Cell, Cell).Value = Value
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Sub CreateNewChartInExcel()
        Dim excelApplication As Microsoft.Office.Interop.Excel.Application = Nothing
        Dim paramWorkbookPath As String = AppDomain.CurrentDomain.BaseDirectory + "ChartData.xlsx"
        Dim paramMissing As Object = Type.Missing
        Dim paramChartFormat As Object = 1
        Dim paramCategoryLabels As Object = 0
        Dim paramSeriesLabels As Object = 0
        Dim paramHasLegend As Boolean = True
        Dim paramTitle As Object = "Sales by Quarter"
        Dim paramCategoryTitle As Object = "Fiscal Quarter"
        Dim paramValueTitle As Object = "Billions"
        Try
            excelApplication = New Microsoft.Office.Interop.Excel.Application()
            excelApplication.Visible = True
            Dim newWorkbook As xlNS.Workbook = excelApplication.Workbooks.Add(xlNS.XlWBATemplate.xlWBATWorksheet)
            Dim targetSheet As xlNS.Worksheet = CType((newWorkbook.Worksheets(1)), xlNS.Worksheet)
            targetSheet.Name = "Quarterly Sales"
            SetCellValue(targetSheet, "A2", "N. America")
            SetCellValue(targetSheet, "A3", "S. America")
            SetCellValue(targetSheet, "A4", "Europe")
            SetCellValue(targetSheet, "A5", "Asia")
            SetCellValue(targetSheet, "B1", "Q1")
            SetCellValue(targetSheet, "B2", 1.5)
            SetCellValue(targetSheet, "B3", 2)
            SetCellValue(targetSheet, "B4", 2.25)
            SetCellValue(targetSheet, "B5", 2.5)
            SetCellValue(targetSheet, "C1", "Q2")
            SetCellValue(targetSheet, "C2", 2)
            SetCellValue(targetSheet, "C3", 1.75)
            SetCellValue(targetSheet, "C4", 2)
            SetCellValue(targetSheet, "C5", 2.5)
            SetCellValue(targetSheet, "D1", "Q3")
            SetCellValue(targetSheet, "D2", 1.5)
            SetCellValue(targetSheet, "D3", 2)
            SetCellValue(targetSheet, "D4", 2.5)
            SetCellValue(targetSheet, "D5", 2)
            SetCellValue(targetSheet, "E1", "Q4")
            SetCellValue(targetSheet, "E2", 2.5)
            SetCellValue(targetSheet, "E3", 2)
            SetCellValue(targetSheet, "E4", 2)
            SetCellValue(targetSheet, "E5", 2.75)
            Dim dataRange As xlNS.Range = targetSheet.Range("A1", "E5")
            Dim chartObjects As xlNS.ChartObjects = CType((targetSheet.ChartObjects(paramMissing)), xlNS.ChartObjects)
            Dim newChartObject As xlNS.ChartObject = chartObjects.Add(0, 100, 600, 300)
            newChartObject.Name = "Sales Chart"
            newChartObject.Chart.ChartWizard(dataRange, xlNS.XlChartType.xl3DColumn, paramChartFormat, xlNS.XlRowCol.xlRows, paramCategoryLabels, paramSeriesLabels, _
             paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing)
            newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, _
             xlNS.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            If Not IsNothing(excelApplication) Then
                excelApplication.Quit()
            End If
        End Try

    End Sub
    Sub UseCopyPaste()
        Dim powerpointApplication As pptNS.Application = Nothing
        Dim pptPresentation As pptNS.Presentation = Nothing
        Dim pptSlide As pptNS.Slide = Nothing
        Dim shapeRange As pptNS.ShapeRange = Nothing
        Dim excelApplication As xlNS.Application = 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
        Dim paramPresentationPath As String = AppDomain.CurrentDomain.BaseDirectory + "Chart Test.pptx"
        Dim paramWorkbookPath As String = AppDomain.CurrentDomain.BaseDirectory + "ChartData.xlsx"
        Dim paramMissing As Object = Type.Missing
        Try
            powerpointApplication = New pptNS.Application()
            excelApplication = New xlNS.Application()
            excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, _
             paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, _
             paramMissing, paramMissing, paramMissing)
            targetSheet = CType((excelWorkBook.Worksheets("Quarterly Sales")), xlNS.Worksheet)
            chartObjects = CType((targetSheet.ChartObjects(paramMissing)), xlNS.ChartObjects)
            existingChartObject = CType((chartObjects.Item("Sales Chart")), xlNS.ChartObject)
            pptPresentation = powerpointApplication.Presentations.Add()
            pptSlide = pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank)
            existingChartObject.Copy()
            shapeRange = pptSlide.Shapes.Paste()
            shapeRange.Left = 60
            shapeRange.Top = 100
            pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsDefault, Microsoft.Office.Core.MsoTriState.msoTrue)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            shapeRange = Nothing
            pptSlide = Nothing
            If Not IsNothing(pptPresentation) Then
                pptPresentation.Close()
                pptPresentation = Nothing
            End If
            If Not IsNothing(powerpointApplication) Then
                powerpointApplication.Quit()
                powerpointApplication = Nothing
            End If
            targetSheet = Nothing
            chartObjects = Nothing
            existingChartObject = Nothing
            If Not IsNothing(excelWorkBook) Then
                excelWorkBook.Close(False, paramMissing, paramMissing)
                excelWorkBook = Nothing
            End If
            If Not IsNothing(excelApplication) Then
                excelApplication.Quit()
                excelApplication = Nothing
            End If
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try

    End Sub
End Module