ChartObject.TopLeftCell (and most every other property) throwing an exception.

Question

I have this code (sheet is a Wordsheet):

ChartObject chart = sheet.Application.ActiveWindow.Selection as ChartObject;
if (chart != null)
	return chart.TopLeftCell;

And it throws a COMException on accessing TopLeftCell. Looking at it in the debugger, most of the properties throw an exception.

Any idea why?

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 1

Hello David:

I created a simple example which displays the Chart Name and the TopLeftCell in the debugger for all charts in an active worksheet.  You should be able to take this code and modify it for your purposes:

Option Explicit

Sub LoopThruCharts()
Dim wksMyWorksheet As Worksheet
Dim i As Long

Set wksMyWorksheet = ActiveSheet

For i = 1 To wksMyWorksheet.ChartObjects.Count
    Debug.Print wksMyWorksheet.ChartObjects(i).Name
    Debug.Print wksMyWorksheet.ChartObjects(i).TopLeftCell.Address
Next i

End Sub

The code runs without error.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 2

David:

I just noticed that you have a semicolon in your statements.  Is your code written in C#?

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 3

Hi;

Yep C#.

And my code is very similiar to yours except I am getting it as the Selection. And when I get it - the properties throw exceptions.

??? - thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 4

Dave:

When you issue the statement:

return chart.TopLeftCell;

You will get a range variable.  Is that what your calling program is expecting?  I notice that you didn't use the .Address property.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 5

I know you are working in VS using C# but even within Excel using VBA Activewindow.selection does not provide much information and when assigned to an object most properties are <Application-defined or object-defined error>

Instead once you have confirmed the selection is a chartobject use the activechart object.

VBA syntax would be

    Dim xx As ChartObject
    
    If TypeOf Application.ActiveWindow.Selection Is ChartObject Then
        Set xx = Application.ActiveChart.Parent
        MsgBox xx.TopLeftCell.Address
    End If

My stab at C#

ChartObject chart = sheet.Application.ActiveWindow.Selection as ChartObject;
if (chart != null)
{
    chart = Sheet.Application.ActiveChart.Parent;
    return chart.TopLeftCell;
}

---

Cheers,

Andy
www.andypope.info

Answer 6

Hi;

The problem I have is all the properties, Parent, TopLeft, etc. throw an exception (see below).

This is occurring when I handle a click in the ribbon. Any ideas?

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 7

Hi DavidThi808,

Thanks for posting in the MSDN Forum.

Would you please provide more detailed code snippet for further research?

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 8

Hi;

Please download and run http://www.windward.net/temp/ExcelChartPositionBug.zip - open an excel file, select the chart, then click the ribbon button. You will get the error.

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 9

Hi DavidThi808,

Sorry for late response, I think the key of this issue is your Chart is under editor status. If you use following snippet it will work fine:

public void MenuChartPosition(IRibbonControl button)
        {
            //Microsoft.Office.Interop.Excel.ChartObject chart = appExcel.ActiveWindow.Selection as Microsoft.Office.Interop.Excel.ChartObject;
            Microsoft.Office.Interop.Excel.ChartObject chart = null;
            try
            {
                if (appExcel == null) MessageBox.Show("No appExcel");
                Microsoft.Office.Interop.Excel.Workbook WB = appExcel.ActiveWorkbook;
                Microsoft.Office.Interop.Excel.Worksheet WS = WB.ActiveSheet;
                chart = WS.ChartObjects("[You chart name]");

                if (chart != null)
                {
                    Microsoft.Office.Interop.Excel.Range range = chart.TopLeftCell;
                    range.Value = "Jusst a test";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

I hope it can help you.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 10

Hi Tom;

I tried to figure out how to use this, but I can't come up with a way to get the name of the chart that is presently selected - because I can't access any properties of the chart object.

How can I get the chart name?

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 11

Hi Tom;

I tried to figure out how to use this, but I can't come up with a way to get the name of the chart that is presently selected - because I can't access any properties of the chart object.

How can I get the chart name?

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 12

Hi DavidThi808,

Please try following snippet, it might help you out:

  public void MenuChartPosition(IRibbonControl button)
  {
   //Microsoft.Office.Interop.Excel.ChartObject chart = appExcel.ActiveWindow.Selection as Microsoft.Office.Interop.Excel.ChartObject;
   Microsoft.Office.Interop.Excel.ChartObject chart = null;
   try
   {
    if (appExcel == null) MessageBox.Show("No appExcel");
    Microsoft.Office.Interop.Excel.Workbook WB = appExcel.ActiveWorkbook;
    Microsoft.Office.Interop.Excel.Worksheet WS = WB.ActiveSheet;
    for (int i = 1; i <= WS.Shapes.Count; i++)
    {
     if (WS.Shapes.Item(i).Type == MsoShapeType.msoChart)
     {
      chart = WS.ChartObjects(i);
      if (chart != null)
      {
       Microsoft.Office.Interop.Excel.Range range = chart.TopLeftCell;
       range.Value = "Just a test";
      }
     }
    }
   }
   catch (Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
  }

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 13

Hi Tom;

I tried that code but it does not help if we have multiple charts in a worksheet because again there is no way to know which is the selected chart.

How can we get a workable chart object for the selected chart?

thanks - dave

---

Who will win The International Collegiate Programming Championships?

Answer 14

Hi DavidThi808,

In my opinion, when Chart has been selected it will not allow you to access it. It will throw exception if you try access it. I will invoke some experts into your issue to see whether they can help you. There might be some time delay, appreciate for your patience.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 15

Hi Tom;

Have you found anyone yet who can provide a solution?

thanks - dave

---

Who will win The Windward International Collegiate Programming Championships?

Answer 16

Hi Tom;

I'm asking again if you have found a solution to this. The chart object is part of the Excel COM API and therefore should work. And if it does not, we should be able to get a hotfix to fix this bug.

Can you please give me a status?

thanks - dave

---

Who will win The Windward International Collegiate Programming Championships?

Answer 17

Asking again - have you been able to get a solution? It's been over 2 months I have been waiting.

thanks - dave

---

Who will win The Windward International Collegiate Programming Championships?