Import Excel sheet with formula Cells

vincentc

Howdy,

I'm using the OpenBook and ReadSheet functions in the Excel Buffer to import excel spreadsheets in Nav 6.0 (2009). This is very effective except that one of the excel automations is complaining about formula cells. The import still continues fine after that but the error message confuses users.

Has anybody worked with this issue before?

Belias

:-k what about posting the error?
i guess that your excel have translated formula names...e.g. in the italian excel, SUM() is SOMMA()

vincentc

Sorry...

"This file was created in a newer version of Microsoft Excel. The file hase been converted to a format you can work with but the following issues were encountered. The file has been opend in read-only mode to protect the original file.

-This workbook uses more rows and/or columns than are supported in this version of Excel. Any data outside 256 (IV) columngs by 65,536 rows will not be opened. Any formula references to data in this region will return #REF! error."

seen this before?

Belias

this is an excel error: the sheet was created with a 2007 excel version (supports more than 1000000 rows), but you are trying to open it with a 2003 version (supports roughly 65000 rows)
solution: upgrade excel

vincentc

Mmm... Thanks for the suggestion. That was also my first thought but the file was created in 2007 and 2007 is installed on all client pc's. I've eliminated every other possibility except for the formulas. When typing the text in manually the system does not error. It only errors with a sheet containing formulas.

vincentc

I actually think you are correct... I'm smoking my socks.

This is the warning/error excel gives when trying to convert a file from one version to another. I'm guessing that there's something I'm not being told... I'll have to investigate this further with the user. The problem is that we can't use the excel sheet that gives the problem because it contains higly confidential data so it's a bit difficult to test the scenario properly.

Belias

in that case, good luck, but as i said, if every workstation have the same excel version, you won't have the problem

bhalpin

I got around the forumula cell error. See my post here:

http://www.mibuso.com/forum/viewtopic.php?f=23&t=30900

Belias

ok, you've "cut" the importable cells up to the 65000 limit, isn't it?
Thanks for reporting me the solution :thumbsup: