Options

Import Excel sheet with formula Cells

vincentcvincentc Member Posts: 6
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?

Comments

  • Options
    BeliasBelias Member Posts: 2,998
    :-k what about posting the error? :)
    i guess that your excel have translated formula names...e.g. in the italian excel, SUM() is SOMMA()
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    vincentcvincentc Member Posts: 6
    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?
  • Options
    BeliasBelias Member Posts: 2,998
    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 :wink:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    vincentcvincentc Member Posts: 6
    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.
  • Options
    vincentcvincentc Member Posts: 6
    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.
  • Options
    BeliasBelias Member Posts: 2,998
    in that case, good luck, but as i said, if every workstation have the same excel version, you won't have the problem :wink:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    bhalpinbhalpin Member Posts: 309
    I got around the forumula cell error. See my post here:

    http://www.mibuso.com/forum/viewtopic.php?f=23&t=30900
  • Options
    BeliasBelias Member Posts: 2,998
    ok, you've "cut" the importable cells up to the 65000 limit, isn't it?
    Thanks for reporting me the solution :thumbsup:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
Sign In or Register to comment.