troubleshooting Question

Need to Modify Excel Validation using C#

Avatar of FactsetWes
FactsetWes asked on
.NET ProgrammingMicrosoft Excel
8 Comments1 Solution5005 ViewsLast Modified:
I am attempting to create an Excel spreadsheet through C# and I need to include a few columns that can be populated by the user from a drop down list.  After some digging around, I discovered that the Validation Property of a Range has a Modify function that looks like it's meant for this very purpose.  However, when I attempt to use the Modify function, I receive the following error message:

System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Validation.Modify(Object Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
   at TestMySQLInteraction.ExcelInteractor.MakeSomeDropDowns() in C:\Documents and Settings\wruttle\My Documents\Visual Studio 2008\Projects\TestMySQLInteraction\TestMySQLInteraction\ExcelInteractor.cs:line 190

I've attatched the relevant code.  I'm using Microsoft.Office.Interop.Excel in case it was not apparent.
//Constructor for my ExcelInteractor Class        
        private Microsoft.Office.Interop.Excel.Application xlApp;
        private Workbook wb;
        private ArrayList ws;
        private object missing;
        
        public ExcelInteractor(int numSheets)
        {
            xlApp = new Microsoft.Office.Interop.Excel.Application();
 
            if (xlApp == null)
            {
                Console.WriteLine("You failed to open an Excel App");
            }
 
            xlApp.Visible = false;
 
            ws = new ArrayList();
 
            xlApp.SheetsInNewWorkbook = numSheets;
 
            wb = xlApp.Workbooks.Add(Type.Missing);
 
            for (int i = 1; i <= numSheets; i++)
            {
                ws.Add(wb.Worksheets[i]);
            }
 
            if (ws == null)
            {
                Console.WriteLine("You failed to create a worksheet!");
            }
 
            missing = System.Reflection.Missing.Value;
        }
 
        public void MakeSomeDropDowns()
        {
            Worksheet m_ws = (Worksheet)ws[0];
 
            try
            {
                Range m_range = m_ws.get_Range("A1", "A1");
                String m_list = "lol,wut,wtf,omg,bbq";
                m_range.Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, missing, m_list, missing);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Caught an Exception while trying to make some Drop Downs:\r\n" + ex.ToString());
            }
        }
 
//main function
[STAThread]
        static void Main(string[] args)
        {
             ExcelInteractor m_XLInt = new ExcelInteractor(1);
             m_XLInt.MakeSomeDropDowns();
             m_XLInt.saveWorkBook(@"C:\TestDDL.xls");
             m_XLInt.closeApp();
        }
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros