How to make Calc update links to other files automatically?

[hillstorm]

EDIT:

• OS: Linux Mint 19.2 Cinnamon
• LibreOffice version: 6.0.7.3
• File format being used: ODS

===

Hello,

I have around 5 Calc files (or documents) with a couple of cells in each that link to another file. I want all links updated every time I open files.

Just to be clear, a link is made when, in one cell, I type = and then click another cell in another file. (I’m not aware of any other definition or formula of a link.)

Back when I was using MS Excel, I would open all of these 5 files at the same time, Excel would prompt me whether to update “external” links, I would click Yes to all. Excel would then update all links and even in real time (or automatically) whenever I made changes thereafter.

On the other hand, Calc also asks, “This file contains links to other files. Should they be updated?” I basically do what I used to with Excel, but Calc does not update links automatically.

Backstory: I’ve been migrating everything to LibreOffice. Those 5 files in question were .xlsx that I converted (Saved As) to .ods.

Last week I had this quite painful session of manually updating links.

Let’s say I have File A and File B with links to each other. After making a change on File A, I would head to File B and then click Edit >> Links to External Files... >> Highlight all 2 or 3 links >> Update

I also went to Tools >> Options... >> LibreOffice Calc >> General >> Ticked Always under Updating Update links when opening

Am I missing something? Is this some kind of a bug out of format conversion?

Thank you in advance.

[anon73440385]

Hello,

if you carefully read the option in Tools -> Options -> LibreOffice Calc -> General -> Category: Updating you will notice that it reads Always (from trusted locations) - and the important thing is the text in parentheses, which states from trusted locations. This means that you need to set a trusted location under Tools -> Options -> LibreOffice -> Security -> Category: Macro Security -> Button: Macro Security -> Tab: Trusted Sources -> Category: Trusted File Location. If you set your files directory as a trusted location, the update will happen, without the need to click the OK button on the yellow bar appearing on top of the sheet when opening such file (I do use my HOME directory here)

Note(s)

• IMO this is a strange place for this setting, since no general user associates this path to the setting and its name Macro Security with a funtion of “Update my links to external data automatically”, but developers view might differ.

• Why there is a Automatic / Manual radio button in dialog at Edit -> Links to External Files, which cannot be toggled, is quite unclear to me.

• This does not introdcue what you called real time update, if you change the files linked to.

• Your Always… setting is required in addition anyway.

If the answer is correct or helped you to fix your problem, please click the check mark () next to the answer.

[hillstorm]

Thank you.

My Calc doesn’t say “(from trusted locations).”

Too bad it isn’t practical for me to list my “Trusted File Locations”–I mount Cryptomator directly on a cloud server; unmounting it changes the location.

Agree on the Automatic/Manual radio button, forgot to mention that.

This “real-time link updating” is important to me. I basically track 80% of the trackable things in my life using spreadsheets. Couldn’t believe it can’t be done.

Will update my OP to reflect my OS, LO version, etc. (forgot to include them, sorry).

[anon73440385]

Misunderstanding - you must trust your local file and not the source of your data. Another strange way of thinking required. Add the directory where your local file linking to external data is stored. The way of thinking is: Trust your local file loading external data from the network (see LibreOffice application as an independant entity, which establishes trust to files). That’s why I wrote I use my HOME directory. It really don not matter, where your external data are stored. Another translation: “Allow your file to load external data” (Don’t ask me why, but that’s a developers view on trusts)

[hillstorm]

Thank you.

I’ll try what you’re saying later, gotta do some work first.

I found on another forum website that Ctrl + Shift + F9 updates all the links in a sheet. Still manual, but totally a huge help.

There’s also Data >> Calculate >> Recalculate Hard–they say–but I only have Recalculate. Recalculate Hard could be “activated” within the Customize dialog–but I have yet to try this later.

Is there another place I can raise this issue, perhaps in which a dev could answer? (I think we’ve raised a couple of good points here.)

It’s become 95% easier now, though.

[anon73440385]

If you feel that there is a bug or you want to place an enhancement request, please file her at Bugzilla. But as described in my answer and the comment, that’s how it works (and I have running this for some of my own sheets).

[erAck]

a strange place for this setting, since no general user associates this path to the setting and its name Macro Security

That place simply was there already when the detailed setting for updating external references and links was tied to trusted locations. Having two different places to manage trusted locations would even be less convenient and more confusing and unnecessary. I agree however that having it under Macro Security is suboptimal.

Why there is a Automatic / Manual radio button

Because it’s for linked external data (Sheet → Link to External Data…) which can be automatically refreshed every n seconds.

[erAck]

found on another forum website that Ctrl + Shift + F9 updates all the links in a sheet.

That’s not true, it recalculates the entire document from what is available as data, internal to the sheet and cached external data, but does not refresh the cached external data.

[erAck]

you must trust your local file and not the source of your data. Another strange way of thinking required

It’s not strange. You want to trust the file that pulls data from wherever, including odd internet places, your local disk and personal secrets. Otherwise it would be possible to offer you a download placed in whatever temporary directory that reads your local data and sends it to some server by using crafted URIs assembled during recalculation of the spreadsheet document.

[hillstorm]

Thanks and sorry this is a late reply.

I just realized that all those 5 files are in the same folder, which is on a cloud server. I have yet to figure out how to handle this (because directories/locations change whenever I unmount Cryptomator).

Just to be clear, I choose to save directly on the cloud–and then my local files sync with it, not the other way around. For now this is the model/workflow that works for me. In other words, I can’t do a setup in which I work directly on local files. Files I mainly work on are cloud-based. Hope that makes sense, and that I understand the scenario.

But so far, Ctrl + Shift + F9 works. Figures after the “Recalculate” are correct. This is largely my goal; the only feature I’m looking for is that it should Recalculate automatically upon opening files.

[silvain-dupertuis]

If you want something to happen automatically when you open a file, you can add a macro which will be executed automatically on opening your document(s) and does it for you. But you still need to set up the proper authorisation.
Here is a macro doing the CTRL-SHIFT-F9 recalculate links:
eSub Essai
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:CalculateHard”, “”, 0, Array())
rem ----------------------------------------------------------------------
End Sub

[schiavinatto]

@hillstorm , Name the cell to link in the source file and in others use Sheet / Link to External Data …

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[hillstorm]

Thank you, but as I said in my comment to @anon73440385 's answer, the URL of External Data Source (I’m assuming this is the same as “Location”) changes whenever I unmount my encryption tool.

[anon73440385]

Do you really mean that the URL changes frequently and your question is in fact about “Update the URL” instead of “Update the data from the files the URL is pointing to”?

[CHEECHBURGER]

STILL DOESN’T WORK FOR ME

[Lotus123_Fan]

I have drilled through this topic for over an hour… and still can not get the recipient linked file to automatically update when changes are made in the source. Ugh.