0

I am trying to do something, but have a hard time. Idea is that i just loop trough excel sheet and find all charts, set it source and then move that sheet to the end of workbook.

I am using C# and Microsoft.Office.Interop.Excel

EDIT

Reason why I need foreach loop, is that because I don't know how much charts I gonna have on that sheet, as I want to allow user to just put charts in sheet, after I dynamically get data from SQL and put it in another sheet, I need to update these charts

This is what I tried so far 

// Get source for charts
Excel.Range ChartSourceRange = xlWorkSheet3.Range["A4", "G5"];

// Here becomes the problem ...I don't know how to go foreach in excel sheet
foreach (Microsoft.Office.Interop.Excel.ChartObject item in xlWorkBook.Sheets[2])
{
    item.Chart.SetSourceData(ChartSourceRange);
}

int totalSheets = xlWorkBook.Sheets.Count;
xlWorkBook.Sheets[2].Move(xlWorkBook.Sheets[totalSheets]);

This code gives me exception ...

Unable to cast COM object of type 'System.__ComObject' to 
interface type 'System.Collections.IEnumerable'. 
This operation failed because the QueryInterface call on the COM component for the 
interface with IID '{496B0ABE-CDEE-11D3-88E8-00902754C43A}' failed 
due to the following error: 'No such interface 
supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))' 
and the COM component does not support IDispatch::Invoke calls for DISPID_NEWENUM.

Can you toss me advice, how to change this code, so that my foreach can go through excel sheet?

Thank you

Veljko89
  • 1,813
  • 3
  • 28
  • 43

1 Answers1

1

Try:

foreach (xl.ChartObject item in xlWorkBook.Sheets[2].ChartObjects())
{
     item.Chart.SetSourceData(ChartSourceRange);
}

In addition, I suggest you to look here in order to know how to use excel in C# (or VB.Net). Since it is a COM object, you need to release it properly:

Community
  • 1
  • 1
ehh
  • 3,412
  • 7
  • 43
  • 91