Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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)
{
   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 7:23am
Edited 31-Oct-12 0:39am
v2
Comments
Maciej Los at 30-Oct-12 12:32pm
   
Show us your code...
SynergiAios at 31-Oct-12 5:39am
   
Sr forgot too now updated.

1 solution

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

Solution 1

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.
 
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 275
1 DamithSL 265
2 CPallini 235
3 Maciej Los 185
4 George Jonsson 170
0 OriginalGriff 5,305
1 DamithSL 4,382
2 Maciej Los 3,760
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,901


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 1 Nov 2012
Copyright © CodeProject, 1999-2014
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