Office.Interop Excel Care about Validation of Cells

Question

0.00/5 (No votes)

See more:

, +

Hi Code Project community,

first of all, sr for my bad english, its not my former language, but i'll try my best to explain to u my problem.

I'm accessing an existing Excel "*.xls" with the Microsoft.Office.Interop.Excel.Application().

Here i want to fill in a Cell some Data.

As Example i want to fill in "-1.00", and in another one "1.00". That all works fine and ist no Problem.

I can achieve this with

1) Selecting a range, edit the rande, and set the modified range into the excel
2) Directly edit the cell

Works all fine.

But now i want the following. I have an excel where are some validations in each cell. As excample a Cell has a validation, that only positiv numbers are allowed. When i try to fill in this cell the value "-1.00", i will get notified in Excel 2007 that this cell needs a positiv value.

When i try to fill in "-1.00" in this cell via my code, even with range or directly edit the cell, the progress works fine, "-1.00" is written into the cell and the sheet is successfully saved.

When i open the excel then with Excel 2007, i see the value "-1.00". When i then try to do the same directly in Excel 2007, it says again that this value isn't possible, it needs to be positive.

So what do i need now is, how can i get the same response in my code. I need to have on either the range modiffication or the direct cell editing a response that this isn't allowed. Or if there are any other ways to get this all is possible i just need to react on this behavior in my code too.

Maybe these rules can be read through any COM Object or another write method would get this response. I Don't know.

So please help solving this problem, and if u are having troubles understanding me just say it, i'll try to explain it better.

C#

FileInfo fi = new FileInfo(@"C:\test.xls");

var application = new Microsoft.Office.Interop.Excel.Application();
var workbook = application.Workbooks.Open(fi.FullName);

foreach(Worksheet worksheet in workbook.Worksheets)
{
   if(worksheet.Name.Equals("Test"))
   {
      worksheet.Cells[4,5] = -1.0;
      break;
   }
}

workbook.Save();
workbook.Close();
Marshal.ReleaseCommObject(workbook);

TIA Synergi

Posted 30-Oct-12 6:23am

SynergiAios

Updated 30-Oct-12 23:39pm

v2

Add a Solution

Comments

Maciej Los 30-Oct-12 12:32pm

Show us your code...

SynergiAios 31-Oct-12 5:39am

Sr forgot too now updated.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-11-01T08:54:00

First of all, read these articles:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.validation%28v=office.11%29.aspx[^]
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.validation_properties%28v=office.11%29.aspx[^]

If you would like to use cell validation, use Validation object.

C#

FileInfo fi = new FileInfo(@"C:\test.xls");

var oApp = new Microsoft.Office.Interop.Excel.Application();
var oWbk = oApp.Workbooks.Open(fi.FullName);
var oWsh = oWbk.Worksheets("Test");
var oVal = oApp.Range.Validation

oWsh.Cells[4,5] = -1.0;
oVal = oWsh.Cells[4,5].Validation;
if (Not oVal.Value) //Value returns TRUE or FALSE; FALSE -> inserted value is not valid
{
    //insert correct value or use ClearContents method, if blanks are allowed
    //oWsh.Cells[4,5] = ""; 
    oWsh.Cells[4,5].ClearContents();
}

workbook.Save();
workbook.Close();
Marshal.ReleaseCommObject(workbook);

Use Try... Catch... End Try[^] block to catch errors.