?
Solved

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

Posted on 2011-03-17
6
Medium Priority
?
1,501 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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