Hi, We have a excel add-in developed using .Net 3.5, Office 2007, VSTO 2008. We dynamically add range validation to cells for showing a in-cell drop-down. It works fine till the excel hasn't been saved (File --> Save), however once its saved, it throws up an exception.
Below is the code and the exception caused.
object[,] NotUsedArray = new object[1, 1]; NotUsedArray[0, 0] = "NotUsed"; Excel.Range cellRange = this.get_Range("$B$10:$B$24", missing); cellRange.ClearContents(); cellRange.Value2 = NotUsedArray; cellRange.Validation.Delete(); cellRange.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=$R$10:$R$" + availableRows.ToString(), " "); Marshal.FinalReleaseComObject(cellRange);
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
Server stack trace:
Exception rethrown at :
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
I also tried using Validation.Modify instead of Validation.Add but it still throws exception.
Any suggestions would be of great help, Thanks.
I've written a program on my computer targeting VS2010 and Office2010, however, it runs properly without such exception.
So could you please tell me the meaning of the "availableRows"? In my program, I just set it as an integer. Also I want to
know the Validation you want to add to the Excel. That means the function of the Validation considering the problem may be
caused by that or a column number out of range. Look forward to your reply. Thank you!
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Apologies for a delayed reply, we had put the issue on back burner. Well the issue still exists.
We are still using VSTO 2008 and Office 2007. The "availableRows" variable is just an integer variable which decides the number of options available in the list. The validation is a list (dropdown).
Please let me if that information suffices, or would you require more information. Any suggestions would be of gr8 help. Thanks!