Solved

Excel fails to show formula result after adding formula from C# - Interop - Excel

Posted on 2011-03-17
6
1,437 Views
Last Modified: 2013-12-17
Hi,

I'm using Office 2003 running on XP SP 2, .NET FW 3.5, Visual Studio 2010

I'm positive that I've found a bug in Excel or maybe in Interop.Excel.

Via my Excel objects I'm writing a simple formula ta cell:

                for (int row = numberOfDataRows; row >= 2; row--)
                {
                    xlRow = (Excel.Range)xlSheet.Rows[row];
                    Excel.Range thisCell = (Excel.Range)xlRow.Cells[1, 2];
                    thisCell.Formula = "=MONTH(a" + row.ToString() + ")";

When I look at the result in the Excel file there are #NAME? in all cells where I added the formula to BUT in the FORMULA bar I can see the correct formula.

Now, if I click inside the Formula bar followed by an ENTER -> voila, the value of the cell is evaluated according to the formula. I don't know how-to solve this what I think is a bug so please, anyone here, help needed ASAP!

Best Regards,
0
Comment
Question by:peer754
  • 3
  • 2
6 Comments
 

Author Comment

by:peer754
ID: 35157154
Still no reply???

I tested this further and so far I've found out this much:

Ok if I instead of =MONTH(...) writes e.g. =Q + row.ToString(). All values from column Q shows ok.

Also Ok if I =MAX(...) i.e. it is not the call to an Excel-function that seems to be the issue BUT certain functions as MONTH and also SUM won't work.

Dunno if this info helps but I'm starting to be desperate so ... please!!!
0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35158138
>> Still no reply???

You only posted this question a couple hours ago - patience, grasshopper. ;)

Anyway, I haven't got Excel 2003 to be able to attempt to duplicate your issue.  I can confirm no such problem seems to exist with Excel 2010, using this test code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			Excel.Application excel = new Excel.Application();
			Excel.Workbook workbook = excel.Workbooks.Add();
			Excel.Worksheet xlSheet = workbook.Sheets[1];

			for (int i = 1; i < 13; i++)
				((Excel.Range)xlSheet.Cells[i, 1]).Formula = "=DATE(2011," + i + ",1)";

			for (int row = 12; row > 0; row--)
			{
				Excel.Range xlRow = (Excel.Range)xlSheet.Rows[row];
				Excel.Range thisCell = (Excel.Range)xlRow.Cells[1, 2];
				thisCell.Formula = "=MONTH(A" + row.ToString() + ")";
			}

			excel.Visible = true;
		}
	}
}

Open in new window


Things I might suggest is explicitly calling Excel.Calculate(), and also check your Excel options - make sure Workbook calculation is automatic.

It's probably also worth pointing out that the MONTH() function in Excel expects a serial number, "40544", not a date-string (40544 is how Excel internally represents the date 1/1/2011).
0
 
LVL 4

Expert Comment

by:fromer
ID: 35166269
it can be a language issue...
I am from Turkey, and in the formula bar "=MONTH(40544)" is not valid where "=AY(40544)" is VALID..
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 4

Expert Comment

by:fromer
ID: 35166300
Addition To Previous Note:

"=MONTH(40544)" generates "#AD?",

AD = NAME in turkish...
0
 

Accepted Solution

by:
peer754 earned 0 total points
ID: 35182967
@fromer: Yes you were on the right track, please see below:

My problem is solved thanks to one of the very talented Moderators at MrExcel - Forum. He pointed me to this excellent thread (I copied the suggested solution and everything is working since then)

http://www.dotnetmonster.com/Uwe/Forum.aspx/vs-net-office/72/FormulaLocal-FormulaR1C1Local-not-working

As you can see, my problem is related to the environment configuration within my .NET Framework found under System.Globalization.CultureInfo

For some reason Excel doesn't recognize some of its Function names even though my Excel is an international installation.

 
Best Regards!
0
 

Author Closing Comment

by:peer754
ID: 35221216
Since I was in a hurry (aren't we always) I was forced to get a solution fast, this one worked completely for me.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question