Hi!

I'm trying to create charts in Excel 2000 Professional (SP-3)
programmatically, using C# (Visual Studio .NET 2005), but there seems
to be a problem. When I set the location of a chart, it is always
placed on the first worksheet, no matter what parameter I specify as
the worksheet name.

For example, the following code:

Excel._Application applic = new Excel.Application();
if (applic == null)
{
Console.WriteLine("Error!");
return;
}

applic.Visible = true;

Workbooks workbooks = applic.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet worksheet = (_Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name = "Worksheet1";
_Worksheet newWorksheet =
(_Worksheet)workbook.Worksheets.Add(Missing.Value,
(_Worksheet)workbook.Worksheets.get_Item(1), 1,
XlSheetType.xlWorksheet);
newWorksheet.Name = "Worksheet2";
Range oRange = worksheet.get_Range("B2", "E2");

_Chart oChart = (_Chart)workbook.Charts.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
oChart.ChartWizard(oRange, Missing.Value, Missing.Value,
XlRowCol.xlRows, Missing.Value, Missing.Value, Missing.Value, "My
chart", Missing.Value, Missing.Value, Missing.Value);

// here's the problem
oChart.Location(XlChartLocation.xlLocationAsObject , "Worksheet2"); //
instead of placing the chart on "Worksheet2", the chart appears on
"Worksheet1" !!!

Console.ReadLine(); // so that Excel doesn't close immediately
applic.Quit();


Could anyone please explain why the chart is not where it is supposed
to be?
And why does this happen only in Excel 2000? In Excel 2003 and Office
XP, the chart is placed correctly (on the second worksheet).

I have to place a chart on a worksheet different from the first one in
Excel 2000; please tell me how to do this in a different way (the above
code doesn't behave as expected).

Can't help you with the C# bit (I thought that was "C-pound" until my
musician daughter laughed at me!!). But there's another way to add an
embedded chart to a worksheet. Instead of creating a chart sheet using
Charts.Add, then putting it on the sheet with .Location, you can directly
create a chart object (the container for an embedded chart) in a worksheet.
In VBA this is the ChartObjects.Add method of the Worksheet object, and it
includes four arguments: these are the .left, .top, .width, and .height
properties of the chart object relative to the worksheet in points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

wrote in message
ups.com...
Hi!

I'm trying to create charts in Excel 2000 Professional (SP-3)
programmatically, using C# (Visual Studio .NET 2005), but there seems
to be a problem. When I set the location of a chart, it is always
placed on the first worksheet, no matter what parameter I specify as
the worksheet name.

For example, the following code:

Excel._Application applic = new Excel.Application();
if (applic == null)
{
Console.WriteLine("Error!");
return;
}

applic.Visible = true;

Workbooks workbooks = applic.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet worksheet = (_Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name = "Worksheet1";
_Worksheet newWorksheet =
(_Worksheet)workbook.Worksheets.Add(Missing.Value,
(_Worksheet)workbook.Worksheets.get_Item(1), 1,
XlSheetType.xlWorksheet);
newWorksheet.Name = "Worksheet2";
Range oRange = worksheet.get_Range("B2", "E2");

_Chart oChart = (_Chart)workbook.Charts.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
oChart.ChartWizard(oRange, Missing.Value, Missing.Value,
XlRowCol.xlRows, Missing.Value, Missing.Value, Missing.Value, "My
chart", Missing.Value, Missing.Value, Missing.Value);

// here's the problem
oChart.Location(XlChartLocation.xlLocationAsObject , "Worksheet2"); //
instead of placing the chart on "Worksheet2", the chart appears on
"Worksheet1" !!!

Console.ReadLine(); // so that Excel doesn't close immediately
applic.Quit();


Could anyone please explain why the chart is not where it is supposed
to be?
And why does this happen only in Excel 2000? In Excel 2003 and Office
XP, the chart is placed correctly (on the second worksheet).

I have to place a chart on a worksheet different from the first one in
Excel 2000; please tell me how to do this in a different way (the above
code doesn't behave as expected).

Thanks!

In the meantime I've tried several workarounds, but the only one that
works is indeed the one using ChartObjects.Add. I still haven't found
the reason for which it didn't work with Location, but it doesn't
matter anymore, since there is another way to do it :)

Cosmina