Ask Your Question
1

External data in calc

asked 2018-10-18 15:48:35 +0200

malcmail gravatar image

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

edit retag flag offensive close merge delete

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-18 16:08:37 +0200 )edit

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.

malcmail gravatar imagemalcmail ( 2018-10-18 16:16:28 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-10-19 11:41:36 +0200

malcmail gravatar image

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.

edit flag offensive delete link more

Comments

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.

erAck gravatar imageerAck ( 2018-10-19 16:57:25 +0200 )edit
1

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

erAck gravatar imageerAck ( 2018-10-19 16:58:01 +0200 )edit

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.

malcmail gravatar imagemalcmail ( 2018-10-19 17:52:30 +0200 )edit

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.

erAck gravatar imageerAck ( 2018-10-20 14:14:55 +0200 )edit
0

answered 2018-10-18 21:46:08 +0200

erAck gravatar image

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)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-18 15:48:35 +0200

Seen: 370 times

Last updated: Oct 19 '18