Ask Your Question
0

How to make Calc update links to other files automatically?

asked 2019-08-15 13:10:52 +0200

hillstorm gravatar image

updated 2019-08-15 17:11:43 +0200

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2019-08-15 14:31:33 +0200

Opaque gravatar image

updated 2019-08-15 16:02:14 +0200

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.

edit flag offensive delete link more

Comments

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).

hillstorm gravatar imagehillstorm ( 2019-08-15 17:08:13 +0200 )edit

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)

Opaque gravatar imageOpaque ( 2019-08-15 18:17:54 +0200 )edit

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.

hillstorm gravatar imagehillstorm ( 2019-08-16 04:03:33 +0200 )edit

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).

Opaque gravatar imageOpaque ( 2019-08-16 14:00:59 +0200 )edit

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 gravatar imageerAck ( 2019-08-16 19:27:02 +0200 )edit

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 gravatar imageerAck ( 2019-08-16 19:31:06 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-08-16 19:37:11 +0200 )edit

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.

hillstorm gravatar imagehillstorm ( 2019-08-19 12:21:33 +0200 )edit
0

answered 2019-08-15 14:09:02 +0200

updated 2019-08-15 14:09:45 +0200

@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 Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

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

hillstorm gravatar imagehillstorm ( 2019-08-15 17:17:23 +0200 )edit

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"?

Opaque gravatar imageOpaque ( 2019-08-16 12:52:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-15 13:10:52 +0200

Seen: 44 times

Last updated: Aug 15