Page 1 of 1

[Solved] Unknown 'Box'

PostPosted: Mon Nov 25, 2019 4:31 pm
by KeithOO
I have a small Spreadsheet (size around 400 KB) which I have developed over last five years. However, I recently had a problem copying around 30 cells in a column (just containing one typed name per cell). After highlighting the cells and hitting Copy, the beach ball appeared and I had to force quit OO. I also noticed that the document size had increased to around 10 MB.
After fiddling, I found that a small 'box' which I did not recognise had attached itself to one of cells. I deleted the 'box' but it seemed to be just one of many, i.e. I must have deleted about 30 but they are still there, as though there are hundreds or thousands stacked on top of one another. Finally, I deleted the row and that got rid of them. Naturally, I would appreciate anyone's suggestion as to what they are and how they became included in the document.
I attach a sample but could not include the problem as, with it, the document size is around 7 MB (1 column, same as attached sample). I have copied the 'box' and included it in the attached. When a 'box' is copied and pasted, it does not give a problem.
Thanks in advance.

Re: Unknown 'Box'

PostPosted: Mon Nov 25, 2019 5:41 pm
by Bill
The Navigator shows an OLE Object in the document. Selecting the "box" and hitting "Delete" deletes the OLE Object, confirming that the "box" is the OLE Object. Double-clicking the OLE Object and dragging the corner of the object to enlarge it shows that it is another spreadsheet similar to the sample spreadsheet. Unfortunately, I don't know why the OLE Object is there or how to fix the problem.

Re: Unknown 'Box'

PostPosted: Mon Nov 25, 2019 5:42 pm
by John_Ha
I switched off macros just to be safe.

Cell A2 contains an OLE object which looks like it is Sheet TRADING from Problem B.ods!


So you are in file Problem B.ods > you are calling Problem B.ods > which calls Problem B.ods > which calls Problem B.ods ... ad infinitum. I would need to see the original file to be sure as, in this file, it seems to stop after 0ne call because the file being called does not call itself.

Delete Cell A2 and start again.

It may be easier to see what is happening in the Navigator - open by clicking where shown or F5.

This is your file unzipped. content.xml has the spreadsheet content. The content calls Object 961 which seems to be embedded and is stored as Object 961. When you insert an OLE object the count begins at 1 so something has happened - the recursive calls - to get the count up to 961.


Re: Unknown 'Box'

PostPosted: Tue Nov 26, 2019 9:52 am
by KeithOO
Thanks to Bill and John for replies. While I am fairly experienced with the creation of documents, regretfully, many of John's comments illustrate the limits of my knowledge!
The document has 6 Sheets and the sample is indeed from the problem Trading Sheet.
I would be happy to email the original sample but it is around 7 MB which I believe is well above this Forum's uploading rule.

Re: Unknown 'Box'

PostPosted: Tue Nov 26, 2019 3:27 pm
by John_Ha
If you click the email symbol to the right of one of my post you can email me the document.

Re: Unknown 'Box'

PostPosted: Wed Nov 27, 2019 1:03 am
by John_Ha
KeithOO sent me the file. I replied:

I don't know what is happening apart from the fact that the file has 961 OLE objects inserted into it - this is why it is so huge.

Clipboard01.png (10.29 KiB) Viewed 532 times

An object linking and embedding (OLE) object is an external file, such as a document, graphics file, or video file that was created using an external application and which can be inserted into another application. In this case it seems you are inserting a spreadsheet, or part of a spreadsheet, into the spreadsheet.

Imagine you have Fred.ods and Tom.ods. You can insert Tom.ods into Fred.ods without any problems.

But I think you have Problem A.ods and you have inserted Problem A.ods into it! This is a recursive thing where Problem A calls Problem A which calls Problem A which calls Problem A ... ad infinitum. In fact, it seems it stops after 961 calls which is why there are 961 copies on the object in the file.

So you need to manually delete all 961 objects by clicking to bring them into view and deleting them. When all are gone save the file - it is now fixed as it has zero OLE objects in it.

Now add the OLE object but make sure you don't add Problem A again!

I later did some further investigation.

It isn't Record changes.

Objects 1 - 960 are stacked up on each other as in the image above where they are close to cell A12. Object 961 is different and is located close to cell A2 at the top of file.

I wondered if deleting the first (Object 1) or the last (Object 960) of the stacked up objects would fix it but it did nothing apart from delete them.

I wondered if deleting Object 961 would fix it but it had no effect either.

When I unzip the .ods file I get a folder called ObjectReplacements with 961 entries Starting Object 1 and ending Object 961.


I am beginning to wonder if my idea that this is because the OLE object calls itself is the reason. This problem is very similar to Re: Paste one figure shows 1000 times where a frame got inserted hundreds and hundreds of times into a Writer file.

I am stumped.

If anyone would like the file to examine it please send me a message.

Re: Unknown 'Box'

PostPosted: Thu Nov 28, 2019 9:09 pm
by John_Ha
I am stumped.


Both Rory and I are stumped and cannot come up with any explanation for why the file has become corrupted.

I agree with you - A2 can be ignored as it looks fine. The many repeats are those in A13/14. How they arrived is a mystery to us. I am now fairly certain they are not recursive calls but I cannot explain them.

The file has been edited 10,413 times and has been open for editing for 1,511 hours. Heavily edited files in Writer get "tangled" - search the Writer forum with tangled for some posts. The fix in Writer is to copy or insert the file to an empty file. When I try that with Insert > Sheet from file ..., Calc hangs for many minutes suggesting a problem with the file being inserted. The new file seems identical to the old file When I save the new file, it is the same size as the original suggesting no "junk" has been removed.

Rory points out the file has a line "table:number-rows-repeated="1048411" which is 165 less than 1,024 x 1,024. AOO supports 1,048,576 rows which is only 165 more. Why are there so many rows?

When I looked through the macros (I don't understand macro code) I thought they all looked quite simple and very similar but it is possible one is doing something rogue.

So, in summary

1. The problem is you have 959 repeated frames attached to A12/13 which each contain an OLE object which is a spreadsheet file

2. We have no idea how these appeared other than they must have appeared by program, not by manual user, because you will not have inserted them.

3. Have you at any time had Record Changes switched on? If so, you remember every keystroke you type and things can rapidly become complex.

4. If it happens again delete rows A13/14.

Sorry we cannot be of more help.

Re: Unknown 'Box'

PostPosted: Thu Nov 28, 2019 10:40 pm
by John_Ha
It isn't recursive calls.

1. I created Tom.ods, with numbers 1 to 10; and Dick.ods, with numbers 101 to 109.

2. I opened Dick.ods and inserted Tom into it as an embedded (ie not linked) OLE object with Insert > OLE Object > From file ..., and navigating to Tom.ods. Dick now had both its old content (101 to 109) and the contents of Tom.ods (1 to 10). I saved Dick.ods.

3. I then opened Tom.ods and inserted Dick.ods into it as an embedded (ie not linked) OLE object. Tom now had its original content, and it had the contents of Dick, which was Dick and Tom.

This caused no problem as can be seen in the image below.

Image of Tom.ods which had Dick in it, where Dick has Tom in it. Both were embedded and not linked.

I then repeated it by inserting the OLE objects as linked files. When I got to Step 3 Calc gave a popup window objecting (it had spotted the trouble I was about to cause) and asked if I wanted to open Tom as READ ONLY. I did so and I then got the identical results. When I changed a value in the "original Tom data" and saved the file, it did not change that value in the "bit which was Dick which contained Tom" despite the files being linked. So, at this stage, Calc spots if you try to do something recursive and prevents you.

However when I then tried to open either of the Tom or Dick saved files AOO got in a loop of asking me if I wanted to open them READ ONLY or OPEN COPY and I had to kill soffice.bin to get out of the loop. So the files are in a recursive call loop which causes problems.