Duplicate an Excel chart and move it to another sheet

Question

I am using the C# Excel interop and I want to create a copy of a chart from one sheet but I want this copy on another sheet. I have tried the following:

Excel.ChartObject chartTemplate = (Excel.ChartObject)sheetSource.ChartObjects("chart 1");
object o = chartTemplate.Duplicate();
Excel.ChartObject chart = (Excel.ChartObject)sheetSource.ChartObjects("chart 2");
chart.Name = "Skew" + expiry.ToString("MMMyy");
range = sheetDestination.Range["T" + chartRowCoutner.ToString()];

chart.Chart.Location(Excel.XlChartLocation.xlLocationAsObject, range);

But when I try this, the last line throws an error:

An unhandled exception of type 'System.Exception' occurred in projectname.exe

Additional information: Error reading Excel file C:\ ...the file path...\template.xlsx: Value does not fall within the expected range.

I have also tried passing a sheet in instead of a range:

chart.Chart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetDestination);

but this gives the same error. I can't understand the reason for the error or how to fix it / bypass it.

I am trying to avoid bringing the clipboard into this, but even if I try copying and pasting, I can still only paste it as an image, which is really not ideal:

Excel.ChartArea chartArea = chart.ChartArea;
chartArea.Copy();
range = sheetDestination.Range["T" + chartRowCoutner.ToString()]; // Note that chart is not on the sheet sheetDestination
range.PasteSpecial(Excel.XlPasteType.xlPasteAll);

The only other solution I can think of now is to do this in VBA and then execute the macro via the interop. But surely it can be done in a clean way just using the interop without the clipboard.

Answer 1

You've already got the solution but instead of giving you a fish for a day I'll give you a proper answer that will help you with any C# Excel coding task.

The C# Interop Model for Excel is almost identical to the VBA Excel Model.

This means it's trivial to convert VBA recorded macros to C#. Let's try this with an exercise like moving a chart to a different sheet.

---

In the Developer Tab in Excel click Record Macro > right click Chart > select Move Chart > choose Object in: Sheet2 > click OK > click Stop Macro Recording.

To see the recorded Macro press Alt + F11 to bring up the VB Editor:

See in the above screenshot how VBA shows you the second parameter for Location() is Name and it's actually a string argument...

Let's convert this VBA Macro to C#:

EDIT by @Ama

The advice below is outdated, there's actually no need to worry about releasing COM objects, this is done automatically at RELEASE mode (DEBUG mode does not). See Hans Passant's answer to "Clean up Excel Interop Objects with IDisposable".

---

The trick here is: never use 2 dots with com objects.

Notice how I could have written:

var sheetSource = workbookWrapper.ComObject.Sheets["Sheet1"];

but that has two dots, so instead I write this:

var workbookComObject = workbookWrapper.ComObject;
var sheetSource = workbookComObject.Sheets["Sheet1"];

Ref: How do I properly clean up Excel interop objects?

You will see the AutoReleaseComObject code in the above QA that projects like VSTOContrib use.

Here is the complete code:

using Microsoft.Office.Interop.Excel;
...
var missing = Type.Missing;
using (AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application> excelApplicationWrapper = new AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application>(new Microsoft.Office.Interop.Excel.Application()))
{
    var excelApplicationWrapperComObject = excelApplicationWrapper.ComObject;
    excelApplicationWrapperComObject.Visible = true;

    var excelApplicationWrapperComObjectWkBooks = excelApplicationWrapperComObject.Workbooks;
    try
    {
        using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapperComObjectWkBooks.Open(@"C:\Temp\ExcelMoveChart.xlsx", false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
        {
            var workbookComObject = workbookWrapper.ComObject;
            Worksheet sheetSource = workbookComObject.Sheets["Sheet1"];
            ChartObject chartObj = (ChartObject)sheetSource.ChartObjects("Chart 3");
            Chart chart = chartObj.Chart;
            chart.Location(XlChartLocation.xlLocationAsObject, "Sheet2");

            ReleaseObject(chart);
            ReleaseObject(chartObj);
            ReleaseObject(sheetSource);

            workbookComObject.Close(false);
        }
    }
    finally
    {
        excelApplicationWrapperComObjectWkBooks.Close();
        ReleaseObject(excelApplicationWrapperComObjectWkBooks);

        excelApplicationWrapper.ComObject.Application.Quit();
        excelApplicationWrapper.ComObject.Quit();
        ReleaseObject(excelApplicationWrapper.ComObject.Application);
        ReleaseObject(excelApplicationWrapper.ComObject);

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

private static void ReleaseObject(object obj)
{
    try
    {
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        Console.WriteLine("Unable to release the Object " + ex.ToString());
    }
}

I know Releasing all the Objects, using GC.Collect and not using two dots when assigning seems over the top but at least when I quit the instance of Excel the process is freed, I don't have to programmatically kill the Excel process!

Ref: Microsoft KB: Office application does not quit after automation from .NET client

Answer 2

From the MSDN documentation here:

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.location.aspx

it states that for the Name parameter of type object:

Name Type: System.Object The name of the sheet where the chart is embedded if Where is xlLocationAsObject or the name of the new sheet if Where is xlLocationAsNewSheet.

This is somewhat misleading from the example at the bottom of the same linked page. It would appear from the example given, that you should actually pass a string of the sheet name. The pertinent line from the example is copied below (the example is for copying to a new sheet):

chart1.Location(Excel.XlChartLocation.xlLocationAsNewSheet, 
    "Sales");

So, for moving to an existing sheet, I would do:

chart1.Location(Excel.XlChartLocation.xlLocationAsObject, 
        "ExistingSheetName");

Do NOT pass a range, workbook or worksheet object. Try a string of the sheet name.

Now, from the same MSDN document page linked above, if you want to reposition the chart within the page once you have moved it to another sheet, there are additional instructions, repeated here for convenience:

If you want to move a chart to another position on a sheet, use the P:Microsoft.Office.Interop.Excel.ChartArea.Top property and P:Microsoft.Office.Interop.Excel.ChartArea.Left property of the ChartArea. You can get the ChartArea object of the Chart by using the ChartArea property.

If you're moving a chart to an existing sheet, be careful not to overlap your chart over existing data. If so, you will have to code around that separately.

Answer 3

This isn't the answer to the question you asked, but might be fruitful

if you're making a copy and editing it for different variations THIS IS NOT A SOLUTION

if you're truly just copying a chart then I recommend using Excel's "Camera" function instead. It basically creates a window into another sheet - you can do this programmatically and it's well documented, but a little known feature of excel I thought I'd be remiss if I didn't point out.

-E

If you are looking to make edits & the question is still open let me know that in a comment - I've done this before I just need to look back in my workbook and see exactly how I did it.

'Camera option is nice because it doesn't 'recalculate' the data - so I imagine it operates faster; a concern in large workbooks.