Corrupted spreadsheet when reopened

English
#1

Not really a question, I just need to vent.

For context - I’m in the process of moving our (my wife and I) budget spreadsheet from Excel to Libre Calc so I can throw Windows away. (Excel is really the only product keeping me there.) The spreadsheet has evolved with a lot of VBA code to make updates with fresh data from the bank easy and give me the charts and views that I want. I’m taking a different approach with the calc version and essentially rebuilding it from scratch so I can avoid including any code. I’d like a workbook my wife can use and maintain if necessary. I have imported some sheets from Excel, but no code, and I save it in the native .ods format. I do have a lengthy IT background so I’m running both systems in parrallel with a view to full cutover at the EOFY.

I had a corruption problem on two sheets of the file last night. (Screenshot of the repeat occurence below.) This was while I was working on it, not as a save and reopen. I’d done quite a bit of work since my last save as so I didn’t want to just go to backup if I could avoid it. I retrieved one of the sheets from a backup and copied it into the file I was working on. Changed all the named ranges, repointed chart data series, and patched up the rest. Deleted the remaining corrupt sheet and redid some missed pointers to what was now an external file. Probably a couple of hours work. Saved and shut everything down.

Reopened this morning and the same thing has happened. Screenshot of the main problem sheet.

image
image1846×949 312 KB

I do use a virtual Google drive for my files but I’ve never had issues with corruptions using it and the first occurrence last night was while editing, not on a save and reopen. I’ve checked the Libre Office temp and backup files to no avail, so now I need to decide whether I bother going through the trouble of repairing it again, knowing a recurrence is a very high chance, or whether I abandon Libre Calc and just stick with my Excel system.

#2

Or you can decide to recreate your table in Calc and always save it in ODF format.
See also:

How to open files from MS-Office 2007 or 2010 (.DOCX, .XLSX,…)?

The LibreOffice documentation is also helpful:

English documentation

#3

Seems he mostly did this already…

#4

That doesn’t make much sense with a former Excel file that’s already messed up.

#5

Do you suggest to drop “compatibility” to excel or open files read-only / with a big-warning?

#6

For some, the glass is still half full, while for others it is half empty.

#7

There is error 522, circular error, so if other cells depend on just one with a circular you might get a cascade of value and circular errors.

Another possibility, try turning off OpenCL, click Tools - Options - LibreOffice - OpenCL and untick the box to enable it. Restart LibreOffice and see if the spreadsheet recalculates correctly

1 Like
#8

Actually we know nothing on the cause. Can be a bug you met or even some corruption of profile. The second has an easy test: Open your sheet in safe mode.
.
The problem may even be at an unknown place in your file.

Only you may decide this. Toss a coin.
.
What I’d try to do is starting from a working backup, redo my steps, saving versions (as a copy) to find out the step, where the failure starts. Then check for bug-reports and maybe differences between Excel and Calc.

#9

The excel file was fine. With the exception of one sheet from my excel file and a CSV extract from the bank’s app the calc file was mostly built from scratch.

I thought I’d reduce the risks associated with different file formats by minimising the use of sheets from excel and rebuilding and saving in the native ods format. I had some other excel sheets in the file temporarily that I’ve since deleted and the one excel sheet I retained had pretty standard formulas and nothing that should have been problematic.

The calc sheet looked and worked fine until it didn’t.

Given that the first corruption occurred while the file was in use, not on save and reopen, I’d be surprised if the file format is relevant anyway.

I’ve decided to take a different approach to repairing it this time and I’ve gone to an earlier backup. I’ll repeat the work I did on lookup tables and a report view, and save more often. The two sheets that screwed up were the last two in the workbook so in case that’s relevant I’ll move them to earlier in the workbook.

I’d like to persist with libre office but if it scrambles again I’ll probably park this particular conversion and stick with my excel version. I don’t want to be restoring from backup and redoing work on a regular basis.

#10

The circular error is one of many errors. The cells in the sheet have got thoroughly out of whack. The sort of mess you can get it you accidentally make changes with multiple sheets selected. Looks like random insertions and deletions. No real fixing of these sorts of messes other than from backup.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.