Since there is no macro code involved, the Download folder should work just as well as any other folder.
The DDE link shows any edits with a delay of some seconds but it can not show any insertions or deletions of data.
- Code: Select all Expand viewCollapse view
=DDE("soffice";"/home/andreas/Downloads/Simple Chart testing 9-9-19.xlsx";"Rentals")
Even if the named range "Rentals" refers to the correct source area, neither the area of the array formula nor the chart will adjust.
Writing a macro to resize an array formula and a chart source is horrible.
--- Back to the database range issue ---
This one-line macro updates the linked database range named "Import1":
- Code: Select all Expand viewCollapse view
Sub refresh_Import1()
ThisComponent.DatabaseRanges.getByName("Import1").refresh()
End Sub
The macro can be triggered by the document's activation event (see attachment).
With or without out any macro, you can use a push button or toolbar button or (context-) menu in order to either call the built-in refresh command directly (with cell cursor in db-range) or call the macro code.
With the attached 11-9-19.ods you only need to edit, insert or delete rows in the xlsx source, save to disk, activate the ods and see the changes in target range and chart
Summarymenu:Tools>Options>Calc>General... "Expand references... " = ON (global option)
Link a database document to the source document (Excel or Calc or something else).
Drag the table icon that represents your data set from the data source window's left pane into the target sheet.
LibreOffice only: menu:Data>Define... [Options] "Insert and delete rows"=ON, "Keep formatting" = ON (this is on by default with OpenOffice)
Create the chart when there are 3 import rows at least (one header row +2 data rows). When expanding from 2 rows, the chart will not follow and you have to adjust the source range manually.
Always save the source sheet to disk before you refesh the import range one way or the other. There are many ways to access the refresh command.
OpenOffice needs a full restart in order to refresh a changed pseudo database that is linked to a spreadsheet. LibreOffice can do it live as long as the source is saved to disk.
A true database connection, even a dBase table, would be more appropriate, easier to edit and update.