External data in calc

I want to a spreadsheet to pick up details from another one held on a web server. I have access to the file so that is not an issue.

When I go to sheet-> link external data it gives me the chance to enter the URL of the external data source. When I enter the location of the ods file the OK button remains greyed out, there are no available tables/ranges. What am I doing wrong?

TIA

Just in case: have you pressed Enter after entering the URL?

When I posted it…no. Later did and nothign appeared. There is a named range in the file. I did also try it with an html table on a webpage but I’d prefer to use the link to an ods if posisble.

After entering the URL in the External Data dialog’s source field you need to hit Enter so the document can be loaded and the available sheets or tables can be listed. You didn’t state your LibreOffice version, you may suffer from bug tdf#119141 that is/was present in 6.0.6 and 6.1.0

If you need a few values from the external document you can also do single cell queries in a formula cell such as

='http://example.com/path/file.ods'#$Sheet1.A1

or use external references in formula expressions, for example

=SUM('http://example.com/path/file.ods'#$Sheet1.A1:A2)

Thanks for the reply. I am using 6.1.2.1 (x64) Windows. I’m looking to drag in a whole load of standing data. I can do it from a webpage (it gives me the ranges when I hit enter) but cannot get anything for the ods file. It may make little difference to be honest as it looks like the level of coding knowledge needed is higher than VBA (which I can , just about, do!). But I’d still like to know how to get it to work all the same.

Darn, I always confuse that linking external data that way is not possible with non-HTML/CSV files, sorry.

It might help that you can insert/link sheets from external files using menu Sheet → Insert Sheet from File… and there in the subsequent file open dialog enter the URL of the remote file, no idea how that works on Windows though, and then check the Link option. Data later can be refreshed using menu Edit → Links to External Files… and Update.

Btw, please don’t enter comments as answer which it is not, use add a comment instead.

Gave that a try but when I put the URL in the address bar and hit enter it just goes back to my own file directory.

Maybe the Windows file dialog has some special mode to enter protocol URIs (like for example in the Gnome file picker dialog one has to press Ctrl+L for Location), I don’t know.