Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
No matter what I try, totalWorksheet has the name Sheet1. I believe this is why the error is shown on the code line.

C#
chart = totalWorksheet.ChartObjects("ChartTotals");

C#
//disable the alearts
frmFoodPantry.objApp.DisplayAlerts = false;
Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;
//Where ShTotals = 1
Excel.Worksheet totalWorksheet =                    (Excel.Worksheet)sheets.get_Item(ShTotals);
string temp1 = totalWorksheet.Name;
totalWorksheet.Activate();
//Unprotect the Totals sheet
totalWorksheet.Unprotect();
// Access Bar chart
chart = totalWorksheet.ChartObjects("ChartTotals");

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'


What I have tried:

Using the sheet number (ShTfor

C#
Excel.Worksheet totalWorksheet =                    (Excel.Worksheet)sheets.get_Item(ShTotals);

Using the sheet name for

C#
Excel.Worksheet totalWorksheet =                    (Excel.Worksheet)sheets.get_Item("Totals");


Using the Chart number which is '1'.
Using the chart name which is 'ChartTotals'.

I have used the debugger and inserted a temp1 to see the name used for 'totalWorksheet'


I have now tried using the sheet number and the chart number, and I get a chart not found error.
Posted
Updated 12-Jul-22 15:57pm
v3
Comments
Graeme_Grant 11-Jul-22 21:22pm    
Did you try enumerating the WookSheets collection to see what worksheet names exist? The other possibility is that the chart object does not exist.

BTW, here is information on that error that you encountered: COMException Class (System.Runtime.InteropServices) | Microsoft Docs[^]

Also, have a look at this: Charts Interface (Microsoft.Office.Interop.Excel) | Microsoft Docs[^]
PaulaJoannAllen 11-Jul-22 21:58pm    
The first I have already read, and it did not help much.
The second I just tried, and no change.
Graeme_Grant 13-Jul-22 20:14pm    
Did the solution below answer your question?
matblue25 12-Jul-22 14:05pm    
Put in a debug statement
chartcount = totalWorksheet.ChartObjects.count
to see if there are, in fact, charts in the sheet
you should be able get the first one using ChartObjects(1) if there is
PaulaJoannAllen 12-Jul-22 15:33pm    
It turns up 0 charts, so I rebuilt the excel sheet, and it still turns 0.

When I set the value of Excel.Worksheet totalWorksheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets[1];

It still turns up 0

I looked at the sheet, and there is definitely a chart there.



1 solution

Here is a working example for you:

1. Excel Helper class - used to simplify code in your project (namespace excluded):
C#
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

public class ExcelHelper
{
    public ExcelHelper(string path)
    {
        _path = path;
        _workbook = _excel.Workbooks.Open(path);
    }

    private readonly string _path;

    private readonly _Application _excel = new _Excel.Application();

    private readonly Workbook _workbook;

    public IEnumerable<Worksheet> Worksheets()
    {
        foreach (Worksheet worksheet in _workbook.Worksheets.Cast<Worksheet>())
            yield return worksheet;
    }

    public IEnumerable<Chart> GetCharts(Worksheet worksheet)
    {
        foreach (var chartObject in worksheet.ChartObjects())
            yield return chartObject.Chart;
    }
}

Here is a sample Console app showing how to use it:
C#
static class Program
{
    static void Main()
    {
        string path = Path.Combine(Path.Combine(Environment.CurrentDirectory, "Workbooks"), "Book1.xlsx");
        ExcelHelper excelHelper = new ExcelHelper(path);

        foreach (Worksheet worksheet in excelHelper.Worksheets())
        {
            Console.WriteLine($"Name: {worksheet.Name}");

            if(worksheet.Name.Equals("MySheet"))
            {
                foreach (Chart chart in excelHelper.GetCharts(worksheet))
                {
                    Console.WriteLine($" - Chart Name: {chart.Name}");
                }
            }
        }
    }
}

And finally, the output:
C#
Name: Sheet1
Name: Sheet2
Name: MySheet
 - Chart Name: MySheet Chart 1
 - Chart Name: MySheet Chart 2
 - Chart Name: MySheet MyChart

You can see that the workbook has 3 sheets, one with a custom name. The worksheet with the custom name has 3 charts, the 3rd with a custom name.

The ExcelHelper class is far from complete and is only a working example for you to work with.

Hope this points you in the right direction...
 
Share this answer
 
Comments
PaulaJoannAllen 16-Jul-22 12:17pm    
The code is

private void SetChartAxis(int newRow)
{
Excel.Chart chart;
frmFoodPantry.objApp.Visible = false;
Excel.Worksheet theWorkSheet;
theWorkSheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item(ShRegister);
theWorkSheet.Activate();

//diaable the alearts
frmFoodPantry.objApp.DisplayAlerts = false;
Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;
Excel.Worksheet totalWorksheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets["Sheet1"];
string temp1 = theWorkSheet.Name;
totalWorksheet.Activate();
//Unprotect the Totals sheet
totalWorksheet.Unprotect();
// Access Bar chart

int chartcount = theWorkSheet.ChartObjects().Count;

chart = totalWorksheet.ChartObjects("ChartTotals");

string temp3 = chart.Name;

chart.SeriesCollection("HouseHolds").Delete();
//chart.SeriesCollection(2).Delete();

if (!isNewYear)
{
//chart.SeriesCollection(3).Delete();
chart.SeriesCollection(2).Delete();
}
chart.SeriesCollection().NewSeries();
//name series for the number of familes series as "HouseHolds"
chart.SeriesCollection(1).Name = @"=""HouseHolds""";
chart.SeriesCollection(1).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;

chart.SeriesCollection().NewSeries();
//namenumber of people as "People"
chart.SeriesCollection(2).Name = @"=""People""";
chart.SeriesCollection(2).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;

chart.SeriesCollection().NewSeries();
//name the new series as "Series 3"
chart.SeriesCollection(3).Name = @"=""Series3""";

if (isNewYear)
{
//name the new series as "Series 3"
chart.SeriesCollection(3).Name = @"=""Series3""";
}
else
{

}

//switching off the alerts
frmFoodPantry.objApp.DisplayAlerts = false;
//Set series 1 to be equal to the number of familes
chart.SeriesCollection("HouseHolds").Values = @"=Totals!$C$3:$C" + newRow;

//Set series 2 to be equal to the number of people in the family
chart.SeriesCollection("People").Values = @"=Totals!$D$3:$D" + newRow;

//Set the title axis to be the dates
chart.SeriesCollection(3).XValues = @"=Totals!$B$3:$B" + newRow;

//switching off the alerts
frmFoodPantry.objApp.DisplayAlerts = false;
frmFoodPantry.theWorkbook.SaveAs(RegGlobals.excelFileName);
//switching on the alerts
frmFoodPantry.objApp.DisplayAlerts = true;

totalWorksheet.Protect();
//End of SetChartAxis
}


On the line chart = totalWorksheet.ChartObjects("ChartTotals");

When I use a number, I get a com error.
When I use a string, I get: 'The item with the specified name wasn't found.'

I have made a new excel workbook from scratch.
The sheet name is Sheet1, as it should be.
The number of charts is 1.
Graeme_Grant 16-Jul-22 18:33pm    
This is a new question. However, the answer to this is in my answer to your other question. I show you how to get the ChartObject by name. Then, the chart itself.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900