Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-17
6
Medium Priority
?
1,556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

609 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