LibreOffice 6.2.7.1 Err:540

[bermguy]

I just upgraded from 6.2.5.2 to 6.2.7.1. I am now getting Err:540 when I enter a formula into a new spreadsheet that includes external references. That didn’t happen with 6.2.5.2. The description of the code is “Happens if a function that requires (re)loading of external sources is encountered and the user hasn’t confirmed reloading of external sources yet”. So if I save the new spreadsheet and reload it, the error goes away and then adding more formulas with external references works. But why should I have to go through that exercise? You don’t have to do that with 6.2.5.2 (or any earlier release). Is this a permanent “feature” or is it a bug that will be fixed?

Thank you.

Additional info (2019-09-13 18:12):

• Security Options and Warnings, Options, Block any links from documents not among the trusted locations (see Macro Security) is checked.
• Macro Security, tab Security Level is High, tab Trusted Sources includes D:\Spreadsheets under Trusted File Locations

I have created a simplified scenario that is easy to implement and illustrates the problem.

1. I created a spreadsheet called SSOld.ods in D:\Spreadsheets containing these cell values:

   A1: 1234,
   A2: 5678,
   A3: 9012,
   A4: 3456

2. I created a new spreadsheet (untitled) and entered the following formula into cell A1:

   =‘file:///D:/Spreadsheets/SSOld.ods’#$Sheet1.A1

   which returns a value of Err:540

3. I moved to cell A2 in the untitled spreadsheet and entered “CTRL+D”, which enters the formula:

   =‘file:///D:/Spreadsheets/SSOld.ods’#$Sheet1.A2

   and also returns a value of Err:540

4. Now cells A1 and A2 in the untitled spreadsheet both contain a value of Err:540

5. I saved the untitled spreadsheet in D:\Spreadsheets as SSNew.ods and closed it.

6. I re-opened SSNew.ods

7. SSNew.ods now contains these cell values:

   A1: 1234,
   A2: 5678

8. In SSNew.ods, I moved to cell A3 and entered “CTRL+D”, then to cell A4 and entered “CTRL+D”

9. SSNew.ods now contains these cell values:

   A1: 1234,
   A2: 5678,
   A3: 9012,
   A4: 3456

So it seems that once I have saved the new spreadsheet and re-opened it, I can enter external references at will and they work.

Thank you for your help.

[mikekaganski]

is it a bug

Quite possibly; maybe @erAck has an opinion?

that will be fixed?

Depends on you notifying developers in the bug tracker

[erAck]

Can you please describe what exactly you are doing? Including the exact formula, where your documents are located, and what the settings under Tools → Options → Security are in

• Security Options and Warnings, Options, Block any links from documents not among the trusted locations (see Macro Security), is it checked or unchecked?
• Macro Security, tab Security Level, and possibly Trusted Sources

Even with Block any links … activated and the Very high macro security setting in 6.2.7 I can enter a formula using an external reference in a fresh new unsaved document without Err:540 appearing.

[bermguy]

Thank you. I will set up a precise description tomorrow. It’s 4:40 AM right now and I am going to bed. Stay tuned.

[erAck]

And please edit your question to provide further details, do not add them as Answer. Thanks.

[erAck]

Shrug… also with the new details (even tested with Macro Security of High instead of Very High) things work for me. Maybe some Windows specific error? Can anyone reproduce at all?

[bermguy]

I uninstalled 6.2.7.1 and installed 6.2.5.2 and the problem disappeared. I then uninstalled 6.2.5.2 and installed 6.2.7.1 and the problem reappeared.

For what it’s worth, my Operating System is Windows 10 and it is up to date (including the 1903 version update and all the updates that apply to that version).

If it’s an interface problem with Windows 10, my guess would be that 6.2.7.1 is making use of some setting in Windows that it didn’t before. That’s only a guess - and probably a bad one but it’s the only thing that I can think of. Nevertheless, the problem is definitely introduced with 6.2.7.1.

Thank you for your help erAck. This is a real baffler.

At least I have a workaround.

[Hrbrgr]

Have a look at Mysterious Err:540 error code

[bermguy]

Thank you for your response; however, I have already seen that. I’m not asking what the code means - I included the meaning in my question. I am asking if it will now be necessary to close a new spreadsheet and re-open it before external references within it will work. I don’t believe that this should be necessary. To me, it looks like a bug.

[erAck]

Fwiw, sounds like bug tdf#135108 but I never could reproduce it with a new document, only with a loaded document that so far had no linked externals.

[bermguy]

Thank you. You’re quite right – it looks like my problem. I couldn’t understand the patch solution – I’m not that skilled. But I’m glad that the problem has been recognised and there are plans to fix it. I am quite accustomed to closing the new file that has the Err:540 and re-opening it (it’s an issue I face literally every day). Doing that is just a minor annoyance and I am content to wait for a fix to turn up in regular released code. Thank you very much for your continued interest.

Also fwiw, I recognise that this is a problem that is limited to only some people. When I first saw the problem, I tried to reproduce it on a friend’s computer but was unable to do so. And yet, I have two computers of my own and it happens on both of them. Not only that, it started for me with 6.2.7.1 and the user who reported td#135108 didn’t see it until 7.0.1.2 and the tech who worked on it couldn’t reproduce it on 6.4.0 or 6.4.7. Apparently, it is a very elusive problem.