Link to home
Start Free TrialLog in
Avatar of FactsetWes
FactsetWes

asked on

Need to Modify Excel Validation using C#

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

Open in new window

Avatar of Bob Learned
Bob Learned
Flag of United States of America image

1) What version of Office are you using?

2) I believe that 0x800A03EC means that you don't have access rights to something.

3) Where is this code running from?
Avatar of FactsetWes
FactsetWes

ASKER

I'm running Office 2003 and the code is currently just be run in debug mode from my Visual Studio 2008 projects directory.

I'm an admin on my machine and I have been doing other Excel interactions in this project that have been working just fine, so I'm not exactly sure how I could be missing access rights.
That might also be access to workbook denied, like from another process using the workbook.  Is that the first access to the worksheet that is attempted in your processing?
In this example, it is, but I originally had it implemented after inserting a header row and an array of data and it threw the same exception.  In order to debug, I watered it down to see if the exception was related to the other stuff I was doing with Excel or if it was just from the Range.Validation.Modify line, so I made the function you see in the sample code up there, and it is producing the same exception.
If you comment out the Range.Validation.Modify, and try a simple access method, does that work?
Well, I'm not really accessing data from Excel, I'm populating a new spreadsheet with data that I'm pulling from a MySQL database.  And yes, when I comment out the Range.Validation.Modify line, the rest of the code works as expected and propperly populates the spreadsheet with data.
ASKER CERTIFIED SOLUTION
Avatar of FactsetWes
FactsetWes

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Man, it was right there, and neither of us could see that right away.  I didn't see anything wrong with your code.   Thanks for the clarification.