Bug saving as .xls format - loses a name reference

users
#1

LibreOffice 3.4.1
OOO340m1 (Build:103)

I have a spreadsheet that I have been using for years.

This contains column where the formula in each cell is similar to
*"=gall_to_litre*F4/B*4"

*gall_to_litre* is defined as a global name and contains a number.

When I enter this formula the spreadsheet calculates correctly. When I save
the spreadsheet as Excel 97 ... and then re-open it it loses the name
reference (shows an error) and the formula then generates an error.

When I save as ods format the formula remains intact.

I have just converted from Open Office 3.2 to LibreOffice 3.4. It worked
correctly in OO 3.2 and all previous versions back to 2.x. I have one
machine where I still have Excel 2003 and would like to continue using .xls
format.

#2

If you don't mind sending me a sample directly, I'll be happy to test in
LO 3.3.3/3.4.1 & OOo 3.2/3.4.

#3

steelej wrote:

LibreOffice 3.4.1
OOO340m1 (Build:103)

I have a spreadsheet that I have been using for years.

This contains column where the formula in each cell is similar to
*"=gall_to_litre*F4/B*4"

*gall_to_litre* is defined as a global name and contains a number.

When I enter this formula the spreadsheet calculates correctly. When I
save the spreadsheet as Excel 97 ... and then re-open it it loses the name
reference (shows an error) and the formula then generates an error.

When I save as ods format the formula remains intact.

I have just converted from Open Office 3.2 to LibreOffice 3.4. It worked
correctly in OO 3.2 and all previous versions back to 2.x. I have one
machine where I still have Excel 2003 and would like to continue using
.xls format.

It is a known (and annoying) bug :
https://bugs.freedesktop.org/show_bug.cgi?id=38113

#4

Hi :slight_smile:
The 3.4.1 is really for early adopters only. It 'should' say that on the
downloads pages but seems to be missing at the moment.

Please could you install the 3.3.3 instead, or alongside
http://wiki.documentfoundation.org/Installing_in_parallel

The 3.3.3 is the latest stable version and has a full years support. The 3.4.1
will soon be out-dated anyway.
Good luck and regards from
Tom :slight_smile:

#5

3.3.4 is scheduled to come out on August 31st.

3.4.2 is scheduled to come out on July 27th.

I read on one of the LO pages that 3.4.2 may be "enterprise ready", but I will think it would be better to wait till 3.4.3 before I would consider it ready for businesses. We will see what the bug reports have to say then.

Right now, I am distributing 3.3.3 to people I know for their businesses and home use. I do not want to confuse them on which version is for which use.

The statement - "has a full years support" - is a question for me about what you mean. Are you saying that we will support 3.3.3 for a full calendar year before telling people they must upgrade to a newer version, or are you saying that the 3.3.x line has been worked on for a full year? I am not sure what your are saying. If you have a link that references that statement, I would like to have it and read what it states - plus print it in PDF format for later use.

#6

It is not an annoying bug, it *seriously corrupted my spreadsheet *as the
data while appearing OK was lost durign the save and the problem only became
apparent when the spreadsheet was reopened. I was fooled by the web page
into thinking that the release was stable and fit for use.

In my case it ONLY corrupted about 200 cells which were fairly simple to
correct. In other spreadsheets I have several thousand such references using
perhaps a dozen named references which would be virtually impossible to
resurrect.

If this is not fixed very soon then all of the good work is in danger of
being destroyed as spreadsheets are damaged

#7

Hi :slight_smile:
Can you fix the spreadsheet using search&replace to find "#NAME?" and replace
with "gall_to_litre"? Hopefully that should fix it for the moment?

The bug-report is already tagged as "Major" and "medium" importance. I'm not
sure it can be pushed any higher than that? I really don't know about how the
devs run their side of things. It is being worked on apparently but only got
reported last month and might not be trivial to fix given that it involves a
proprietary format.

Good luck and regards from
Tom :slight_smile:

#8

I could fix THIS spreadheet using a replace function as there is only one
such name used.

I have other spreadheets however where there are typically about 12 names
defined in this way. The names are accesed in formulae across many tabs and
over several thousand cells. If the reference is =<name> then it is
replaced by simply = just to add to the "fun"

How could anybody repair such a spreadsheet? Most of my spreadsheets are
work related = Enterprise! Yhe sample I have here is for home use and is
much simpler.

I would expect that the effort to recover a large spreadsheet would not be
worth while (probablyseveral days) and I would have needed to revert to a
backup as the spreadheet would be to all intents and purposes totally
destroyed just by saving as Excel format.

*If that is not a serious bug I don't know what is.*

I have tested that version 3.3 does nto suffer from this problem so I will
revert to that version with my confidence in LO somewhat shaken. I am due to
give a talk to our local computer club in less than a month about Ope/libre
Office and am now in doubt as to what to say.

#9

Hi :slight_smile:
The bug is being worked on. It was only identified recently and has already
been marked as being as serious as it can get. Obviously we want miracles but
work takes time. Until the bug is resolved i recommend avoid using the
proprietary format that doesn't work and stick to the format that does.
Otherwise use Excel for those other workbooks. It's also wise to back-up data
regularly whichever programs you use. I'm glad to hear you were able to recover
the sheet that had gone badly wrong.

Regards from
Tom :slight_smile:

#10

Hi,

Gérard Fargeot wrote (Mon, 18 July, 2011 7:45:31)

steelej wrote:

LibreOffice 3.4.1
OOO340m1 (Build:103)
[...]
When I enter this formula the spreadsheet calculates correctly. When I
save the spreadsheet as Excel 97 ... and then re-open it it loses the name
reference (shows an error) and the formula then generates an error.
[...]

It is a known (and annoying) bug :
https://bugs.freedesktop.org/show_bug.cgi?id=38113

Which should be fixed in 3.4.2

Could you please check this in the release candidate for 3.4.2?
http://www.libreoffice.org/download/pre-releases/
Thanks!

#11

Thanks for the response - I had a quick test yesterday with 3.4.2 RC2 and
believe that the problem has been solved.

There is however now a problem with compatibility with office 2007. The
document reports error when transferring between the two programs. Office
complains about the formate when written by LO and vice versa but at least
each can open the other files.

As an aside trying to reply to your message has taken a long time. I can
read emails sent to my registered address but cannot post using that address
and s cannot just reply. A proper forum rather than a mailing list would be
so much easier as I am often away from home and direct access to my email.

John

#12

Hi John,

steelej wrote (27-07-11 19:37)

Thanks for the response - I had a quick test yesterday with 3.4.2 RC2 and
believe that the problem has been solved.

Great - thanks for reporting!

There is however now a problem with compatibility with office 2007. The
document reports error when transferring between the two programs. Office
complains about the formate when written by LO and vice versa but at least
each can open the other files.

As there is specific data about a problem, feel free to post or to look at BugZilla for possible corresponding issues. Clear real-life cases for im,rovement are valuable :slight_smile:

As an aside trying to reply to your message has taken a long time. I can
read emails sent to my registered address but cannot post using that address
and s cannot just reply. A proper forum rather than a mailing list would be
so much easier as I am often away from home and direct access to my email.

Well, posting via Nabble e.g. is an option too :wink:

Cheers,