Getting a handle to a NamedRange control in C#

Question

I'm developing an application-level AddIn for Excel 2007 using C# (.NET 4.0). Questions similar to this one have been previously asked >>

https://stackoverflow.com/questions/11374023/resizing-namedrange-throws-a-controlnotfoundexception

C# excel addin - Accessing Controls

but the responses to them (where there were any) did not work for me. So I would like to give a detailed description of what I'm trying to do, and what happens when I try to do it.

Very briefly, my problem is that I'm not able to get hold of NamedRange control objects for modifying them, after they have been instantiated and initialized elsewhere in the code.

Please read on for a more complete description.

When the AddIn starts, it creates a NamedRange control and attaches it to some cells on a particular worksheet >>

Worksheet worksheet = Globals.Factory.GetVstoObject(
                Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range cell = worksheet.Range["A1"];
_configParams.HeaderNamedRange = worksheet.Controls.AddNamedRange(cell, "HeaderCells");
_configParams.HeaderNamedRange.RefersTo = "=" + worksheet.Name + "!$A$1:$A$5";

After the AddIn has started, I want to allow the User to re-specify the cells to which this NamedRange control can point. In order to do so, I'm getting the User to select a range of cells and then get hold of the selected cells' location (as a String) in a suitable event-handler method.

I then intend to programmatically re-specify the cells that the above NamedRange control points to. I have tried the following approaches, but neither of them work.

[Approach 1]

Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
((NamedRange)(controlWorksheet.Controls["HeaderCells"])).RefersTo = newHeaderCellsLocation;

On running the above code, it throws a ControlNotFoundException, and I get the following message:

Microsoft.VisualStudio.Tools.Applications.Runtime.ControlNotFoundException was unhandled by user code
  Message=This document might not function as expected because the following control is missing: PortfolioHeaderCells. Data that relies on this control will not be automatically displayed or updated, and other custom functionality will not be available. Contact your administrator or the author of this document for further assistance.
  Source=Microsoft.Office.Tools.Excel.Implementation
  StackTrace:
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.GetObjects()
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.EnsureProxy()
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.get_Proxy()
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.AttachSupport()
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.ResetControl()
       at Microsoft.Office.Tools.Excel.NamedRangeImpl.set_RefersTo(String value)
       at ExcelListenerAddIn.Configuration.ListenerConfigManager.ActivateListenerConfiguration(ListenerConfigParams newConfiguration, Boolean cameFromConfigFile) in C:\Users\jag201\Documents\Visual Studio 2010\Projects\PoC\ExcelListenerAddIn\Configuration\ListenerConfigManager.cs:line 97
       at ExcelListenerAddIn.TaskPanes.ListenerConfigurator._activateNewConfigButton_Click(Object sender, EventArgs e) in C:\Users\jag201\Documents\Visual Studio 2010\Projects\PoC\ExcelListenerAddIn\TaskPanes\ListenerConfigurator.cs:line 131
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  InnerException: System.Runtime.InteropServices.COMException
       Message=Exception from HRESULT: 0x800A03EC
       Source=Microsoft.VisualStudio.Tools.Office.Runtime
       ErrorCode=-2146827284
       StackTrace:
            at Microsoft.VisualStudio.Tools.Office.Runtime.Interop.IHostItemProvider.GetHostObject(String primaryType, String primaryCookie, IntPtr& hostObject)
            at Microsoft.VisualStudio.Tools.Office.Runtime.DomainCreator.ExecuteCustomization.Microsoft.Office.Tools.IHostItemProvider.GetHostObject(Type primaryType, String primaryCookie)
            at Microsoft.Office.Tools.Excel.NamedRangeImpl.GetObjects()
       InnerException:

[Approach 2]

I then saw Set Excel Named Ranges via C#? and so tried to use the approach described in the answer there:

Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
controlWorksheet.Names.Item("HeaderCells", Type.Missing, Type.Missing).RefersTo = newHeaderCellsLocation;

On running the above code, I get the following exception and message:

System.Runtime.InteropServices.COMException was unhandled by user code
  Message=Exception from HRESULT: 0x800A03EC
  Source=""
  ErrorCode=-2146827284
  StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Names.Item(Object Index, Object IndexLocal, Object RefersTo)
       at ExcelListenerAddIn.Configuration.ListenerConfigManager.ActivateListenerConfiguration(ListenerConfigParams newConfiguration, Boolean cameFromConfigFile) in C:\Users\jag201\Documents\Visual Studio 2010\Projects\PoC\ExcelListenerAddIn\Configuration\ListenerConfigManager.cs:line 97
       at ExcelListenerAddIn.TaskPanes.ListenerConfigurator._activateNewConfigButton_Click(Object sender, EventArgs e) in C:\Users\jag201\Documents\Visual Studio 2010\Projects\PoC\ExcelListenerAddIn\TaskPanes\ListenerConfigurator.cs:line 131
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  InnerException:

So, in both approaches, the exception from HRESULT is the same: 0x800A03EC. Also, the exception is being thrown in the line where I try to get hold of the NamedRange control and re-specify the cells it points to.

I have stepped through my code, and a NamedRange control does indeed exist where/when I try to modify the NamedRange object.

I then checked the type of the controlWorksheet.Controls["HeaderCells"] object using the following code >>

Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
System.Windows.Forms.MessageBox.Show((controlWorksheet.Controls["HeaderCells"]).ToString());

This results in a MessageBox containing "Microsoft.Office.Tools.Excel.NamedRangeImpl". If I'm not mistaken, this Type is not exposed by the Excel Object Model, and so I wonder if the problem is being caused by my casting a NamedRangeImpl object to a NamedRange control object.

Your help is much appreciated. If there is any further information that I can provide, please let me know. Thanks in advance!

If you are just updating the cells in the Range, does this answer help? http://stackoverflow.com/a/9792388/2258 — Richard Morgan, Sep 04 '12 at 21:06
@RichardMorgan: I have to make use of a NamedRange control, as opposed to creating a named alias for an Excel Range. The NamedRange control's events will be wired up with event-handler methods. So the issue here is different from that of attaching a pre-existing named alias to another set of cells. — stochastically_metaskeptical, Sep 04 '12 at 21:43

score 0 · Answer 1 · answered Nov 07 '12 at 15:13

There is no problem with the object type being returned. The type "Microsoft.Office.Tools.Excel.NamedRangeImp" is intended to be accessed via the "Microsoft.Office.Tools.Excel.NamedRange" interface.

The COM exception of 0x800A03EC suggests to me that excel will not allow you to perform the requested operation (this is also the error code you will get if you try to for example, send a string value to a cell that will exceed the character limit).

There is no problem setting the RefersTo property several times, as I verified by running the following code:

        Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(
            Globals.MercuryAddIn.Application.ActiveWorkbook.ActiveSheet);
        Excel.Range cell = worksheet.Range["A1"];
        Microsoft.Office.Tools.Excel.NamedRange r = worksheet.Controls.AddNamedRange(cell, "HeaderCells");
        r.RefersTo = "=" + worksheet.Name + "!$A$1:$A$5";

        r.RefersTo = "=" + worksheet.Name + "!$C$1:$C$5";

So the problem must lie in the string contained in your "newHeaderCellsLocation" variable. RefersTo must be set to a valid formula for a range. Can you double check that the string is in the correct format?

Hi Franchesca. This is very late, but thanks for your reply. I'm not working in the role/position that I was at when I asked this question. But thanks again for looking into this and answering my questions :). — stochastically_metaskeptical, Sep 17 '14 at 17:59

Getting a handle to a NamedRange control in C#

1 Answers1