[RESOLVED] How to select/copy a chart in a chart sheet

[MrGTI]

Hello.

I’ve been working on a project, and had fantastic success with the search feature the last few days – I was able to find answers for everything I was looking for (and I even gave good rep to the people that posted the answers I was looking for). But now I’m stuck, and I need a little help.

I have an Excel file that I’m working with via .NET. One of the tabs is a chart – which I understand is actually a chart sheet (the entire tab is a big chart, with no cells).

I can open the Excel file:

Dim xExcelApp As New Excel.Application
xExcelApp.Workbooks.Open("C:\test.xls")

My problem is with the chart sheet. I want to select the chart, and then copy it (so I can paste it somewhere else). No matter what I try, I can’t seem to select the chart in the chart sheet:

xExcelApp.Workbooks(1).Worksheets("Report4_Chart").select()
xExcelApp.Selection.Copy()

Any ideas on what I’m doing wrong?
.

[Siddharth Rout]

I don't have vb.net so try this for me...

xExcelApp.Workbooks(1).sheets("Report4_Chart").ChartArea.Copy

or try this

xExcelApp.Workbooks(1).sheets("Report4_Chart").ChartArea.Copy()

[anhn]

To make it clear on these 3 object collections in a workbook:

1. Worksheets: contains all worksheets: each member is a Worksheet object
2. Charts: contains all chart sheets: each member is a Chart object
3. Sheets: contains all worksheets and chart sheets: each member is either a Worksheet object or a Chart object. Noted that there is no Sheet object.

A Chart object (chart sheet) is a member of Charts collection and it is also a member of Sheets collection.
A Worksheet object is a member of Worksheets collection and it is also a member of Sheets collection.

To specify a chart sheet, you cannot use Worksheets(index) but you have to use:
Charts(index)
or
Sheets(index)
where index is index-number or name.

In your case, you can use either:

xExcelApp.Workbooks(1).Charts("Report4_Chart").ChartArea.Copy

or

xExcelApp.Workbooks(1).Sheets("Report4_Chart").ChartArea.Copy

[MrGTI]

Thanks for all the help. I was able to get it to work with:

xExcelApp.Workbooks(1).Charts("Report4_Chart").ChartArea.Copy

It's such a simple solution. Excellent. Thank you.
.