none
Excel range validation not working on a saved excel file

    Question

  • 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.

    Code:

    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);
    
    

    Exception:

    System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

    Server stack trace:

    Exception rethrown at [0]:
       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)
       at GrowthSimRelease.Sheet1.Populate()

    I also tried using Validation.Modify instead of Validation.Add but it still throws exception.

    Any suggestions would be of great help, Thanks.

    Tuesday, July 20, 2010 6:31 PM

All replies

  • Hi AvinashN,

    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!


    Best Regards,
    Amy Li 


    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.

    Friday, July 23, 2010 9:28 AM
    Moderator
  • Hi Amy,

    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!

    Monday, August 30, 2010 8:12 AM
  • Hi Amy,

    Though we've used other workarounds for this issue like statically adding formulas, I'd like to know if its something that vsto doesn't support as of now, or am I missing something.

    Thanks,

    Avinash.

    Saturday, September 11, 2010 12:40 AM