Vlookup link to external file sometimes lost on load

[tim]

I have a few Libreoffice OpenDocument spreadsheets which all retrieve the latest share prices and forex rates from some CSV files in another folder using vlookups. These CSV files are updated by a script which I run before I open the spreadsheets:

=VLOOKUP("USDAUD=X",'file:///home/username/Documents/Share Research/Data/Forex Data Downloaded Data.csv'#$Sheet1.$A$1:$B$15, 2,0)

When I open the spreadsheets LibreOffice always prompts with “This file contains links to other files.
Should they be updated?”, which I always click yes to.

This works fine about 8 out of 10 times. But sometimes I open the spreadsheets to find that, seemingly at random, some of the vlookup cells have lost the file path to the CSV files and instead look like this

=VLOOKUP("USDAUD=X",, 2,0)

I haven’t moved or deleted any of the files involved in this at any time. Does anyone have any suggestions how to avoid this problem? Or if there’s a fix in a future version?

libreoffice-calc 4.2.7-0ubuntu2 on Ubuntu 14.04

[oweng]

The Link to external data feature has long been flaky in various ways. This seems like it may be one more case of this. Bug fdo#87973 describes the same issue for XLSX and links fdo#63992, which describes the same for ODS. The latter has been marked as a duplicate issue to a more general XLS issue by a Calc developer.

[tim]

Ok thanks, I’ve upgraded to Libreoffice 4.4, I’ll see if the problem re-occurs with that version

[tim]

A month so far on Libreoffce 4.4 and so far the problem hasn’t re-occurred, so hopefully this is something that’s been fixed somewhere between LO 4.2 and LO 4.4

[tim]

I should’ve updated this before, but the Link to External Data feature is completely broken, even in recent Libreoffice versions. It’ll fail silently and randomly.

The only thing that works is to have the data as CSV files and then to Sheet->Insert Sheet From File… and use the ‘Link’ option (so that it updates automatically each time you load Libreoffice). You can then hide the sheet if you like. This has worked well for me so far. See Insert Sheet - LibreOffice Help