Click here to Skip to main content
12,399,362 members (58,240 online)
Rate this:
Please Sign up or sign in to vote.
See more: Office MS-Excel validation , +
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.

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)
      worksheet.Cells[4,5] = -1.0;

TIA Synergi
Posted 30-Oct-12 6:23am
Updated 30-Oct-12 23:39pm
Maciej Los 30-Oct-12 12:32pm
Show us your code...
SynergiAios 31-Oct-12 5:39am
Sr forgot too now updated.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

First of all, read these articles:[^][^]

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

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] = ""; 

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 1 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100