Best way to link to Excel data

[Villeroy]

I have 2 versions of LO, 6.0 and 6.3. It works fine with both versions. I edit the xlsx, save, refresh the database range in the ods document. The chart reflects modified data, inserted rows and remvoved rows.

[catbill]

Now I am flummoxed. Since this works for you, I played around with this a bit more and got it to work. But I don't know why. I looked in the Base file and saw that it was not showing the updated Excel file. I clicked on View > Refresh Tables. The Base file still did not show the updated Excel file. I also looked at Edit > Database Properties but did not knowingly change anything. It is possible that I looked at something else.

Anyway, I saved and closed the Base file. When I opened it again, it showed the updated Excel file and has been working properly.

I created another Excel file, database, and Calc spreadsheet to try to recreate what happened. This time, I cannot get it to work. No matter what I do, the Base file does not show the updated Excel file.

Is this enough information to guess at what might be going wrong or what I did to make it work?

[keme]

Tried with LibreOffice and OpenOffice Base on Windows 10. Both working properly, sort of, but there is a pitfall: The downloads folder is considered an insecure area.
Save location other than "Downloads" may cause issues too, even inside the "Documents" folder, depending on specific settings in Office.
With default settings, the "Documents" folder is assumed safe, and files there are trusted (you may still get a warning that "this file is downloaded from the Internet" the first time you access it). Folder "Downloads" is assumed to be insecure.

What did I do:
When I leave your files in the downloads folder, I can open them and activate editing in the applications, and initially everything seems to work fine. I change Excel data, register the database with the suite, open the ods file in Calc (either version) and accept updating the linked data. The ods file shows the changed data from the Excel file. Change the Excel file again, and nothing comes through. Base shows old data (from the state after my first change) and Calc does not even ask to update linked content.

Moved all three files to my Documents folder. Changed the registered base to point to new location. Everything now works as expected.
Changes are still not immediate from Excel to Calc (which they might be with DDE) but is refreshed on every open, or if I select Data - Update range. Have to save the Excel file, then refresh data in OpenOffice/LibreOffice.

[Villeroy]

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

Summary
menu: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.

[catbill]

Thank you Villeroy and Keme for continuing to look into this.

I still cannot get this to work. I have tried numerous times with versions 6.2.7, 6.2.0.2, and 6.3 beta. I have also created new user profiles. I store my files in the cloud and thought that maybe that could introduce a problem (however unlikely) so I tried storing them locally. Still no success.

When I look at the Base file, it does not show the changed Excel file. I don't understand how this can be. Using Edit > Database > Properties or Edit > Database > Database > Connection Type shows the connection is fine.

The problem seems to boil down to this: what is causing the Base file from seeing the updated Excel data, even when the connection is successfully established (supposedly)? It doesn't make sense to me, but I also don't understand how linking works.

Is my assumption about the problem correct? If so, what could be causing this problem and what can be done to make this work?

[Villeroy]

I'm so sorry. The only thing I can tell you is that I tested my approach with Windows 10 and it works even better than with my private Linux machine.
Explanation: There is a hidden way how one can set up an automatic refresh interval for database ranges. This was the first thing I tried, however it did not actually refresh anything on my Linux system. Now I download the this topic's files to a Windows box with macros disabled and see that the refresh interval does work.

The top secret method is described here: viewtopic.php?f=75&t=18511#p196704
After running that macro and saving the file to disk, the refresh interval is stored with the document and the macro is no longer required until you want to remove or change the refresh interval. The database range "Import1" in my file "linked to Excel data 11-9-19.ods" has an refresh interval of 15 seconds. After I changed something in the source range of "Simple Chart testing 9-9-19.xlsx" and saving that file to disk, I see the changes in "linked to Excel data 11-9-19.ods" after some seconds (max. 15).

The connection is visible in the status bar of your Base document or in menu:Edit>Database>Properties...
My status bar looks like this: [Spreadsheet | C:\Users\Rezeption\Test\ExcelDB\Simple Chart testing 9-9-19.xlsx] If this is correct and you can see the contents of the database table named "Sheet1", then you can close and forget the Base file.
The registration name of that database as it appears in the data source window and in Tools>Options>Base>Databases should be "DB 9-9-19" because my ods sheet is linked to a data source with that name having a table named "Sheet1", which is the name of the source sheet. The used area (rectangle around all non-empty cells) is treated as a database table.
In my source sheet, you can click a cell in the import range, open the dialog menu:Data>Define... hit the options button and see at the very bottom of that dialog that the database range "Import1" is linked to "DB 9-9-19/Sheet1"

[catbill]

I deeply appreciate your help with this.
I have tried many times and many ways to get this to work with Excel data but have not yet succeeded.
This works fine with Calc, so there must be something about my setup that causes problems. I will report back if and when I figure this out.
You have provided a lot of information in these posts that will be helpful for others with similar difficulties.
Thanks again.