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.InteropServ ices.COMEx ception (0x800A03EC): Exception from HRESULT: 0x800A03EC
at System.RuntimeType.Forward CallToInvo keMember(S tring memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.E xcel.Valid ation.Modi fy(Object Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
at TestMySQLInteraction.Excel Interactor .MakeSomeD ropDowns() in C:\Documents and Settings\wruttle\My Documents\Visual Studio 2008\Projects\TestMySQLInt eraction\T estMySQLIn teraction\ ExcelInter actor.cs:l ine 190
I've attatched the relevant code. I'm using Microsoft.Office.Interop.E xcel in case it was not apparent.
System.Runtime.InteropServ
at System.RuntimeType.Forward
at Microsoft.Office.Interop.E
at TestMySQLInteraction.Excel
I've attatched the relevant code. I'm using Microsoft.Office.Interop.E
//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();
}
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.
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?
ASKER
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?
ASKER
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
2) I believe that 0x800A03EC means that you don't have access rights to something.
3) Where is this code running from?