I am developing AddIn for Excel 2007 at the application level using C # (.NET 4.0). Questions like this were previously asked →
https://stackoverflow.com/questions/11374023/resizing-namedrange-throws-a-controlnotfoundexception
C # excel addin - Access controls
but the answers to them (where they were) did not work for me. Therefore, I would like to give a detailed description of what I am trying to do and what happens when I try to do it.
In short, my problem is that I cannot gain control of NamedRange objects to modify them after they have been created and initialized elsewhere in the code.
Please read a more detailed description.
When AddIn starts, it creates a NamedRange control and attaches it to some cells on a specific sheet ->
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 AddIn has started, I want to allow the User to re-point the cells that this NamedRange control can point to. To do this, I get a User to select a range of cells, and then get the location of the selected cells (like String) in a suitable event handler method.
Then I intend to programmatically redefine the cells to which the above NamedRange breakpoints belong. I tried the following approaches, but none of them work.
[Approach 1]
Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); ((NamedRange)(controlWorksheet.Controls["HeaderCells"])).RefersTo = newHeaderCellsLocation;
When 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]
Then I saw "Set naming names in Excel using C #?" and therefore tried to use the approach described in the answer:
Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); controlWorksheet.Names.Item("HeaderCells", Type.Missing, Type.Missing).RefersTo = newHeaderCellsLocation;
When I run 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:
Thus, in both approaches, the exception to HRESULT is the same: 0x800A03EC. Also, an exception is thrown at the line where I am trying to get the NamedRange control and reassign the cells it points to.
I went through my code and the NamedRange control really exists when / when I try to change the NamedRange object.
Then I checked the object type controlWorksheet.Controls["HeaderCells"] using the following code →
Worksheet controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); System.Windows.Forms.MessageBox.Show((controlWorksheet.Controls["HeaderCells"]).ToString());
The result is a MessageBox containing "Microsoft.Office.Tools.Excel.NamedRangeImpl". If I'm not mistaken, this type is not displayed using the Excel object model, and so I wonder if this problem is caused by the fact that my NamedRangeImpl object is a NamedRange control object.
Your help is greatly appreciated. If there is any additional information that I can provide, let me know. Thanks in advance!