Create excel.worksheet object from chart sheet

Question

0.00/5 (No votes)

See more:

I am doing some charting in Excel via VB.net and I am having an issue trying to create a new worksheet object for a chart I just created. When I am done creating the chart, I do the following to locate the chart on a new sheet:

chartPage.Location(XlChartLocation.xlLocationAsNewSheet, "Chart")

After doing this, I am then able to activate the new chart worksheet via:

Dim XLSheets As Sheets = xlWorkBook.Sheets
XLSheets("Chart").activate()

I then want to create a new Worksheet object for the new chart sheet I do:

Dim XLChartSheet As Excel.Worksheet
XLChartSheet = xlWorkBook.Sheets("Chart")

However, this throws an error:

Unable to cast COM object of type Microsoft.Office.Interop.Excel.ChartClass to interface type Microsoft.Office.Interop.Excel.Worksheet

I have also tried the code below and I get the same error

XLChartSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
XLChartSheet = CType(XLSheets("Chart"), Excel.Worksheet)

I can create a new worksheet object for a non-chart sheet and it works fine. It appears to me that for some reason it doesn't think this chart sheet is a sheet.

Any suggestions?

What I have tried:

Here is more of my code:

Dim chartPage As Excel.Chart
Dim xlCharts As Excel.ChartObjects
Dim myChart As Excel.ChartObject
Dim R1 As Range, R2 As Excel.Range
Dim chartRange As Excel.Range

xlCharts = xlWorkSheet.ChartObjects
myChart = xlCharts.Add(250, 10, 500, 350)
chartPage = myChart.Chart
            

'Create a union range for the chart range
R1 = xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(LastCellRow, 2))
R2 = xlWorkSheet.Range(xlWorkSheet.Cells(1, 4), xlWorkSheet.Cells(LastCellRow, 4))

chartRange = xlApp.Union(R1, R2)
chartPage.SetSourceData(Source:=chartRange)

'Set the chart range to columns
chartPage.PlotBy = Excel.XlRowCol.xlColumns


'Change the chart type for each series
Dim ChartSeries As Excel.SeriesCollection = chartPage.SeriesCollection
ChartSeries.Item(1).ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
ChartSeries.Item(1).ChartType = XlChartType.xlColumnClustered

'Change the data label precision
If SelectedDownTimeChartUnits.ToUpper = "MINUTES" Then
   ChartSeries.Item(1).DataLabels.numberformat = "0"
Else 'Hours
   ChartSeries.Item(1).DataLabels.numberformat = "0.00"
End If

ChartSeries.Item(1).DataLabels.font.size = 7


'Create the 2nd series for the cumulative percentage
ChartSeries.Item(2).ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
ChartSeries.Item(2).ChartType = XlChartType.xlLineMarkers
ChartSeries.Item(2).AxisGroup = XlAxisGroup.xlSecondary
'Format data labels
ChartSeries.Item(2).DataLabels.numberformat = "0.00%"
ChartSeries.Item(2).DataLabels.font.size = 7
ChartSeries.Item(2).DataLabels.position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionAbove


'Move the legend
chartPage.Legend.Position = XlLegendPosition.xlLegendPositionBottom

'Add the main chart title
chartPage.HasTitle = True
chartPage.ChartTitle.Text = "Down Time Pareto for " & SelectedMachine & " From " & DatStart & " to " & DatEnd


'Add axis titles
XLAxisCategory = chartPage.Axes(, Excel.XlAxisGroup.xlPrimary)
XLAxisCategory.Item(XlAxisType.xlCategory).HasTitle = True
XLAxisCategory.Item(XlAxisType.xlCategory).AxisTitle.Text = "Down Time Reason"

XLAxisValue = chartPage.Axes(, XlAxisGroup.xlPrimary)
XLAxisValue.Item(XlAxisType.xlValue).HasTitle = True
XLAxisValue.Item(XlAxisType.xlValue).AxisTitle.Text = SelectedDownTimeChartUnits & " of Down Time"

'Add Secondary Axes title
XLSecondaryAxisValue = chartPage.Axes(, Excel.XlAxisGroup.xlSecondary)
XLSecondaryAxisValue.Item(XlAxisType.xlValue, XlAxisGroup.xlSecondary).HasTitle = True
XLSecondaryAxisValue.Item(XlAxisType.xlValue, XlAxisGroup.xlSecondary).AxisTitle.Text = "Cumulative %"
            
'AutoFit all the columns
xlWorkSheet.Columns.AutoFit()

'Move chart to separate sheet
chartPage.Location(XlChartLocation.xlLocationAsNewSheet, "Chart")

Posted 8-Feb-18 2:31am

theskiguy

Updated 8-Feb-18 7:17am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2018-02-08T07:14:00

Solution 1

If Sheets("Chart") object represents a chart object in workbook, you have to use Chart[^] object rather than Worksheet. Note that chart object differs from standard sheet. It does not contain any cell! It's just a chart.

VB.NET

XLChartObject = DirectCast(xlWorkBook.Charts("Chart"), Excel.Chart)
'or
XLChartObject = DirectCast(XLSheets("Chart"), Excel.Chart)

MSDN wrote:
The Chart object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets). Use Sheets(index), where index is the sheet index number or name, to return a single sheet.

For further details, please see:
Charts interface (Microsoft.Office.Interop.Excel)[^]
Creating Charts in Microsoft Office Excel 2003 Using Visual Basic for Applications Code[^]
DirectCast Operator (Visual Basic) | Microsoft Docs[^]

Posted 8-Feb-18 7:14am

Maciej Los

Comments

theskiguy 8-Feb-18 15:34pm

Thank you for clearing this up for me. I was unaware that my chart worksheet wasn't an actual worksheet but rather a chart object. I do, however, have another question. The reason why I was trying to create the Worksheet object for the chart was because I wanted access to the page setup so I could add a footer to my chart when someone printed it. I noticed that the "chart" object has its own page setup so I'm thinking I don't really need the worksheet object anymore. I already have a chart object declared in my example called "chartpage", however, when I try to access the page setup, I am getting the following error: "Unable to set the LeftFooter Property of the page setup class"

chartPage.PageSetup.LeftFooter = "Printed On &D &T" 'Add Date/Time

Any suggestions?

Maciej Los 8-Feb-18 16:05pm

This is another question and you should post it via using "quick answer->Ask a question" menu on the top of this page. Do not forget to share your code. Without that, i won't be able to help you.
Can you accept this answer as a solution (green button) - formally to remove your question from unanswered list?

Maciej Los 8-Feb-18 16:59pm

Thank you for accepting solution. Now, it's bit late in Poland, so i'm going to sleep. If you would like to let me know that you posted another question, use 'Reply' widget in my comment. I'll look at it.

theskiguy 9-Feb-18 8:07am

I was able to figure out how to get the footer on the chart sheet. I just made a new "chart" object after moving the chart to the new sheet using your first example above. For some reason, it would not work with my original chart object "chartpage". Thanks for your help.

Maciej Los 9-Feb-18 8:31am

Have you tried something like this:

With XLChartObject.PageSetup
    .LeftFooter = "bla bla bla"
End With

theskiguy 9-Feb-18 14:39pm

If it try it on my original "ChartPage" chart object, I still get the "Unable to set the leftfooter Property" error. If I define a new chart object after moving the chart to a new sheet, then it will work. Everything seems to be working great now.