Copy chart in C#

[Claus Nielsen]

Hello everyone

I'm having problems with copying chart from an excel workbook to word, using
C# in a class I've created.

I create the charts dynamically, and do stuff like:

---------------------------------------------------------------------------
Excel.Chart selfLedelseChart;
selfLedelseChart = (Excel.Chart)oExcelApplic.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);

Excel.Range rng = (Excel.Range)ledelseSelf.Cells.get_Range("A1:B" +
(ledelseSelfRow-1).ToString(), Type.Missing);
selfLedelseChart.HasLegend = false;
selfLedelseChart.ChartType = Excel.XlChartType.xlRadarMarkers;
selfLedelseChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
---------------------------------------------------------------------------

The creation process works fine, and I can paste the chart into a workbook
using:

---------------------------------------------------------------------------
selfLedelseChart.Location(Excel.XlChartLocation.xlLocationAsObject,
ledelseSelf.Name);
---------------------------------------------------------------------------

And this is where my problem begins. After the insertion I can't use the
variable selfLedelseChart anymore. It kind of loses focus or something like
that...

So I created a makro:
---------------------------------------------------------------------------
ActiveSheet.ChartObjects("Diagram 2").Activate
ActiveChart.ChartArea.Select
---------------------------------------------------------------------------
And tried to translate that into C#, but I can't really do it. I get an
error saying the specified cast is not allowed. I tried several solutions,
but I can't get a variable to point to the chart after I've inserted it into
the chart.
I can use the code:

---------------------------------------------------------------------------
Excel.ChartObject chartObject =
(Excel.ChartObject)ledelseSelf.ChartObjects(1);
---------------------------------------------------------------------------
But I can't go anywhere from there. I need the charts in 2 places in the
workbook total, so I also need to copy them internally, because I need to
paste charts on top of oneanother.

The funny part is that I can resize the object after insertion:

---------------------------------------------------------------------------
ledelseSelf.Shapes.Item(1).Width = chartWidth;
ledelseSelf.Shapes.Item(1).Height = chartHeight;
ledelseSelf.Shapes.Item(1).Top = chartTop;
ledelseSelf.Shapes.Item(1).Left = chartLeft;
---------------------------------------------------------------------------

So I'm looking for a way to get a variable to point to a chart in a certain
worksheet, and a way to copy to word in C#. Does anyone know hoe to do this?
Or can anyone point me in the right direction? Or can you tell me where to
get help?

Please help!!!

Thanks!

Regards

 

[Jon Peltier]

Hi Claus -

Well, I don't know much C# (my musician daughter laughed at me once when I called it
"C-pound"), but I have a suggestion.

Can you replace this:

selfLedelseChart = (Excel.Chart)oExcelApplic.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);

with something like this:

selfLedelseChart = (Excel.Chart)oExcelApplic.ledelseSelf.ChartObjects.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing).Chart;

(assuming ledelseSelf is a worksheet!). This method creates the embedded chart
directly, without going through the chart sheet middleman.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Claus Nielsen]

Hello Jon

Thanks for your input, but it doesn't seem to compile.
ChartObjects cannot be used without (). The method takes a number for the
index chart.

The insertion works fine, and I translated it from a makro, so do you think
thats the problem.

My problem is still after insertion. I tried something like:
strategyChart = (Excel.Chart)strategySets.Shapes.Item(1);
... but that only works if the chart is not of type Radar... Doesn't that
seem weard?

Hope you can help!

Regards

 

[Jon Peltier]

The object hierarchy may not have been correct in my suggested statement. It goes:

Application > Workbook > Worksheet > ChartObject > Chart

You may need to do something like:

selfLedelseChartObject =
(Excel.ChartObject)oExcelApplic.<workbook>.<worksheet>.ChartObjects.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing)

and then

selfLedelseChart = selfLedelseChartObject.Chart

You could keep trying the Shapes collection, if you don't need to manipulate the
chart features. If I add a shape and want to reference it, I either set a variable
to the shape as it is added using ObjectVariable = Shapes.Add, or set a variable to
it afterwards using ObjectVariable = Shapes(Shapes.Count). You have to figure out
all the C# referencing for this, however.

Here's a google search for C# and Excel charts on the Microsoft web site. There
might be some better examples among these links:

http://www.google.com/search?as_q=C..._dt=i&as_sitesearch=microsoft.com&safe=images

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Claus Nielsen]

Hello Jon

Thanks for replying, and a very Chrismas to you!

Can you still help, because I found a solution that enables me to copy a
chart to word. Now my problem resides in copying the charts internally in
excel...

You preferred that I changed the insertion method of a chart, but so far it
works fine. Now I can reference the chart again by doing this:
selfLedelseChart = (Excel.Chart)oExcelApplic.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);

Excel.Range rng = (Excel.Range)ledelseSelf.Cells.get_Range("A1:B" +
(ledelseSelfRow-1).ToString(), Type.Missing);
selfLedelseChart.ChartType = Excel.XlChartType.xlRadarMarkers;
selfLedelseChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);

selfLedelseChart.Location(Excel.XlChartLocation.xlLocationAsObject,
ledelseSelf.Name);
selfLedelseChart = oExcelApplic.ActiveChart; //Most important line
selfLedelseChart.HasLegend = false;

.... so from there on, I can do whatever I want with the chart. And now I am
able to copy the chart from Excel to Word by typing:
strategyChart.ChartArea.Copy();
wordApp.GotoBookMark("setChart");
wordApp.oWordApplic.Selection.Paste();

Now the chart resides in the Word document.

Now I want to copy the chart within Excel doing a transfer from VB to C#:
selfLedelseChart.ChartArea.Copy();
oExcelApplic.ActiveWindow.Visible = false;
totalLedelse.Select(Type.Missing);
totalLedelse.Paste(Type.Missing, Type.Missing);
totalLedelseChart = oExcelApplic.ActiveChart;
//Here the first chart is located as a copy in the totalLedelse worksheet

ledelseObj.Activate();
objLedelseChart.ChartArea.Select();
objLedelseChart.ChartArea.Copy();
oExcelApplic.ActiveWindow.Visible = false;
totalLedelse.Activate();
totalLedelse.Select(Type.Missing);
totalLedelse.Activate();
totalLedelseChart.Select(Type.Missing);
totalLedelseChart.ChartArea.Select();
totalLedelseChart.Paste(Type.Missing);

But an error occurs in the line:
totalLedelseChart.Select(Type.Missing); where I get an error I can't really
describe. Something like: H13221432x crappy error...

So do you know of a method in copying charts internally in Excel the C# way?

Thanks for your help!

Regards