none
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?

    Monday, October 08, 2012 11:08 PM

All replies

  • 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

    Tuesday, October 09, 2012 1:37 AM
  • 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

    Tuesday, October 09, 2012 1:59 AM
  • 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?

    Tuesday, October 09, 2012 2:55 AM
  • 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

    Tuesday, October 09, 2012 5:00 AM
  • 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


    • Edited by Andy Pope [mvp]MVP Tuesday, October 09, 2012 3:43 PM consistently lower cased variable name
    Tuesday, October 09, 2012 3:29 PM
  • 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?

    Tuesday, October 09, 2012 5:24 PM
  • 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

    Wednesday, October 10, 2012 7:12 AM
    Moderator
  • 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?

    Wednesday, October 10, 2012 4:01 PM
  • 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


    Thursday, October 11, 2012 7:31 AM
    Moderator
  • 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?

    Wednesday, October 17, 2012 3:23 PM
  • 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?

    Wednesday, October 17, 2012 3:27 PM
  • 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

    Thursday, October 18, 2012 7:45 AM
    Moderator
  • 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?

    Thursday, October 18, 2012 1:12 PM
  • 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

    Friday, October 19, 2012 7:10 AM
    Moderator
  • Hi Tom;

    Have you found anyone yet who can provide a solution?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Monday, October 29, 2012 9:44 PM
  • 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?

    Sunday, November 11, 2012 11:59 PM
  • 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?

    Friday, December 21, 2012 9:18 PM