5

I want to delete a Chart from an Excel file. The Excel file is an automatically generated historyfile with a chart, the problem is, that every time I renew the history, it makes a new chart, but the old one must be deleted... This is my code:

Excel.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(path);
ExcelApp.Visible = true;
Excel.Worksheet Sheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Excel.Range range = Sheet.UsedRange;
int i = 2;
while (Convert.ToString((range.Cells[i, 1] as Excel.Range).Value2) != null)
{
    i++;
}

Excel.Range oRange;
Excel._Chart oChart;
Excel.Series oSeries;
oChart = (Excel._Chart)ExcelWorkBook.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oRange = Sheet.get_Range("A2:H" + i).get_Resize(Missing.Value, 8);
oChart.ChartWizard(oRange, Excel.XlChartType.xlLineStacked, Missing.Value,
Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,"Chart01");
oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.XValues = Sheet.get_Range("A2", "A" + i);
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, Sheet.Name);

Now I need to delete the existing Chart before that code.

Something like

Excel._Chart asdf = Sheet.ChartObjects("Chart01").Chart;
if (asdf != null)
{
    asdf.Delete();
}

This doesn't find a chart with name "Übersicht" but there is a chart with title "Übersicht"

EDIT: The Problem now is that it can't delete the Chart: Exception from HRESULT: 0x800A03EC

John Saunders
  • 160,644
  • 26
  • 247
  • 397
jochot
  • 167
  • 1
  • 13

2 Answers2

4

In Excel make sure that the Chart actually exists with the name.

You can rename a chart using 

Sheets("Sheet1").ChartObjects(1).Name = "Chart01"

then when you click on the chart in the spreadsheet view you can see that it actually renamed

enter image description here

On the C# side I would suggest a minimal example like this 

bool deleted = false;
try
{
    ChartObject myChart = ws.ChartObjects("Chart01");
    myChart.Delete();
    deleted = true;
}
catch
{
    MessageBox.Show("Chart with this name could not be found");
    //throw new Exception("Chart with this name could not be found");
}
finally
{
    MessageBox.Show("the chart was " + (deleted ? "deleted" : "not deleted"));
}
  • I Changed the Name of the Chart like in your picture and now it finds the chart, but it can't delete it, the Exception is _Exception from HRESULT: 0x800A03EC_ – jochot Mar 31 '14 at 13:44
  • declare your chart object as `ChartObject` not `_Chart`? –  Mar 31 '14 at 13:44
  • @jochot you don't need to provide solution within your question. When you accept an answer it means that this is the solution that works for you. I would leave your question as is for any future visitor who may ran into a similar, even same problem. –  Mar 31 '14 at 14:00
  • ok, didn't know that ;) Now the Problem is, that if there is no Chart in there, I get an Exception, and I can't do something like `if (Sheet.ChartObjects("Chart 1"))`, because I get an Exception here too – jochot Mar 31 '14 at 14:12
  • 1
    @jochot just wrap your code with a try/catch/finally. Not sure I can help much more here. If you still can't fix it consider asking a new question as I believe this one should now be solved. –  Mar 31 '14 at 14:30
0

Try to rename the chart name to plain English like: 'Chart01'. The issue might be due to Unicode support.

tal.tzf
  • 128
  • 7