Description: If a Calc table is integrated into a Writer Document and there is a link to another, external Calc table containing data, this link can no longer be updated. Steps to Reproduce: In the attached example writer document ('Writer Document with integrated Calc Table.odt') a small Calc table is integrated. In field B1 of this table, a number (here in the example 2, 3, 4 or 5) can be entered that refers to a corresponding row number of the external Calc table ('Calc Table with external Data.ods'). In fields B2 and B3 of the integrated Calc table, the INDEX function is used to access and display the values of the external Calc table. Accordingly, field B2 displays the value of the row number of column A of the external Calc table entered in B1. Field B3 displays the value of column B accordingly. Actual Results: If one or more values in the external Calc table are now changed, the Calc table integrated in the Writer document will initially display the previous value(s). If you click on "Edit", "Link to external files..." and "Update", the changed values from the external Calc table should also be displayed in the integrated Calc table. But this is not the case from version 6.2.8 (up to the latest version 7.0.0.0). The previous values are still displayed and there is no possibility to display the changed, current values of the external Calc table. Expected Results: This worked fine until LibreOffice version 6.2.5.2! The versions 6.2.6 and 6.2.7 were not tested by us. Reproducible: Always User Profile Reset: Yes Additional Info: none
Created attachment 161444 [details] Example Writer document with integrated Calc table
Created attachment 161445 [details] Calc table with external data
I tried to test, but when I modify the file link to =INDEX('file:///home/user/libobugs/vboxshare/Calc Table with external Data.ods'#$Tabelle1.$A$1:$A$10;B1) ...on my Linux system, I get #REF! as the result. Please help me, if you can.
Please, write step by step how did you make the tables. I did Calc tables and then I copied to writer by ctrl+shift+V. Then I am editing the original calc document and after save documents it works good also in writer. However I made my own files. Also check that on actual version. Version: 7.2.0.0.alpha0+ (x64) Build ID: 4e63ec27b69fa01ff610c894c9fbf05c377a6179 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win Locale: cs-CZ (cs_CZ); UI: en-US Calc: CL
Created attachment 168415 [details] Example Writer document with integrated Calc spreadsheet
Created attachment 168416 [details] Calc spreadsheet with external data
(In reply to Martin Srdoš from comment #4) > Please, write step by step how did you make the tables. > > I did Calc tables and then I copied to writer by ctrl+shift+V. Then I am > editing the original calc document and after save documents it works good > also in writer. However I made my own files. > > Also check that on actual version. > > Version: 7.2.0.0.alpha0+ (x64) > Build ID: 4e63ec27b69fa01ff610c894c9fbf05c377a6179 > CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: > win > Locale: cs-CZ (cs_CZ); UI: en-US > Calc: CL Dear Martin. First of all, I would like to apologise for responding so late. But unfortunately I have had so much to do in the last few weeks that I haven't found the time. My example involves two different Calc spreadsheets. One is integrated in a Writer document and should display the corresponding value of an external Calc spreadsheet file after entering a row number. The second, external spreadsheet file thus serves as a database that is to be accessed from the integrated Calc spreadsheet with the help of an INDEX command. Error description: If a Calc Spreadsheet is included in a Writer document and there is a link to another, external Calc Spreadsheet with data, this link can no longer be updated. Preparations: Please save the again simplified two documents 'Writer Document with integrated Calc Spreadsheet.odt' and 'Calc Spreadsheet with external data.ods' somewhere on your WINDOWS PC. Steps to reproduce the error: Open the Writer document and then also the integrated Calc spreadsheet. In field B1 of this small spreadsheet, a number (here in the example 2, 3, 4 or 5) can be entered that refers to a corresponding row number of the external Calc spreadsheet ('Calc Spreadsheet with external data.ods'). In the field B2 of the integrated Calc spreadsheet, the values of the external Calc spreadsheet are accessed and displayed with the function INDEX. Accordingly, the field B2 shows the value of the row number of column A of the external Calc spreadsheet, which is entered in B1. I.e. if, for example, the number 2 is entered in field B1 of the integrated Calc spreadsheet, the value 200 must be displayed in field B2 as it is stored in row 2, column A of the external Calc spreadsheet. If, for example, the number 3 is entered in B1 of the integrated spreadsheet, the value 300 must appear in field B2 and so on. If one or more values of column A in the external Calc spreadsheet are modified, the integrated Calc spreadsheet in the Writer document still shows the previous values because the link to the external spreadsheet is not updated automatically. Even if the Writer document including integrated Calc spreadsheet is saved, closed and reopened, field B2 of the integrated Calc spreadsheet still shows the old values from the external spreadsheet. To update the link of the integrated Calc spreadsheet with the now changed external Calc spreadsheet, one has to click on 'Edit' with the integrated Calc Spreadsheet open, then on 'Links to External Files...', then on 'Modify...' and now select the external Calc Spreadsheet 'Calc Spreadsheet with external Data' and click on 'Open'. Finally click on 'Update' and now the selected file including path should be displayed in the line 'Source file'. Close the window by clicking on 'Close'. Now the modified values of the external Calc spreadsheet should be displayed in the integrated Calc spreadsheet. This update of the link to the external Calc spreadsheet still worked properly up to LibreOffice version 6.2.5, but from version 6.2.8 up to the latest version 7.1.0.0 beta1(x64) this no longer works! The previous values are still displayed and there is no possibility to display the modified, current values of the external Calc spreadsheet. I do not have the LibreOffice versions between version 6.2.5 and 6.2.8, so I cannot determine exactly from which version onwards the updating of the link to the external Calc spreadsheet no longer works. If you want to test updating the link again with the old LibreOffice version 6.2.5 but no longer have access to it, I can send you a link to download from my cloud (64 bit version).
Created attachment 168421 [details] Example Writer document with integrated Calc spreadsheet I have simplified the example Writer document with integrated Calc spreadsheet again to better understand the error.
The easiest way to reproduce the behaviour I have described is to simply copy the two documents ('Example Writer document with integated Calc spreadsheet.odt' and 'Calc spreadsheet with external Data.ods') to the root directory of drive D: and open the Writer document from there. Otherwise, the path to the external Calc spreadsheet in cell B2 of the integrated Calc document must be adjusted accordingly in the INDEX command.
Yes, it shows just previous values. I download only writer document and the values are there. Without calc source. Which way do you creating the integrated spredsheet? Just copy the table and paste (ctrl+shift+v) as spredsheet? For me it works same badly also in the old version 6.2.5. When I create it, it works, but when I save everything, close and open, it stop work. Also in 6.2.4. Please tel us how do you create the tables in writer. I don't know, if it is important, but maybe would. Probably we don't want test existing documents in different versions, but we want to test creating such documents. P.S. Please upload again the writer document. It was uploaded as patch and it is not possible to download it.
Created attachment 168459 [details] Example Writer document with integrated Calc spreadsheet I have uploaded the example Writer document with the integrated Calc spreadsheet again and not marked it as 'patch'. I created the Calc spreadsheet integrated in the Writer document as follows: Click on 'Insert' in the open Writer document, then on 'Object', on 'OLE Object...' and on 'LibreOfficeDev 7.1 Spreadsheet'. In the Calc Spreadsheet thus created, enter a number between 2 and 5 in cell B1 and the following function in cell B2: =INDEX('file:///D:/Calc Spreadsheet with external Data.ods'#$Tabelle1.$A$1:$A$10;B1) This should cause the corresponding value from the external Calc spreadsheet to appear in cell B2: 200, 300, 400 or 500, depending on the number in cell B1. The prerequisite is, of course, that the Calc spreadsheet with the external values is located in the path specified in the INDEX function. Afterwards, please save the Writer document with the integrated Calc spreadsheet under any name and close it. Now open the Calc spreadsheet with the external values and change one or more values in column A (e.g. in cell A3 the number 300 to 333). Then save and close the spreadsheet and open the Writer document and the integrated Calc spreadsheet again and enter the number 3 in cell B1 (confirm with Enter, of course), then the previous value 300 is still displayed in cell B2, as the link to the external calc spreadsheet is not automatically updated. To update this link, you have to click on 'Edit', 'Link to External Files...' and 'Update', as I already described yesterday. If the path displayed under 'Sourcefile' does not correspond to the actual path of the Calc spreadsheet, the correct path must be selected with the button 'Modify...' before clicking on 'Update'. Finally, click on 'Close'. Now the modified value, namely 333, should actually be displayed in cell B2, but this is not the case! The previous value 300 is still displayed! Just now I found out something very interesting: If a new calc spreadsheet is created in a Writer as described above, updating the link to the external calc spreadsheet works absolutely perfectly if the update is carried out as described above ('Edit', Link to External Files...'). However, if the Writer document with the integrated calc spreadsheet is saved and closed and then opened again, the updating of the link no longer works!
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Lothar Viel from comment #11) > Created attachment 168459 [details] > Example Writer document with integrated Calc spreadsheet > > I have uploaded the example Writer document with the integrated Calc > spreadsheet again and not marked it as 'patch'. You didn't have to do that as I just changed the mime type of the one you uploaded as patch and unchecked the patch checkbox. You can edit the details of your attachments after the fact and here you can see a cheatsheet of mime types: https://wiki.documentfoundation.org/QA/Bugzilla/Attachments#Current_Manual_Fixing
(In reply to Lothar Viel from comment #11) > I created the Calc spreadsheet integrated in the Writer document as follows: > Click on 'Insert' in the open Writer document, then on 'Object', on 'OLE > Object...' and on 'LibreOfficeDev 7.1 Spreadsheet'. > In the Calc Spreadsheet thus created, enter a number between 2 and 5 in cell > B1 and the following function in cell B2: > > =INDEX('file:///D:/Calc Spreadsheet with external > Data.ods'#$Tabelle1.$A$1:$A$10;B1) > > This should cause the corresponding value from the external Calc spreadsheet > to appear in cell B2: 200, 300, 400 or 500, depending on the number in cell > B1. The prerequisite is, of course, that the Calc spreadsheet with the > external values is located in the path specified in the INDEX function. > > Afterwards, please save the Writer document with the integrated Calc > spreadsheet under any name and close it. > > Now open the Calc spreadsheet with the external values and change one or > more values in column A (e.g. in cell A3 the number 300 to 333). Then save > and close the spreadsheet and open the Writer document and the integrated > Calc spreadsheet again and enter the number 3 in cell B1 (confirm with > Enter, of course), then the previous value 300 is still displayed in cell > B2, as the link to the external calc spreadsheet is not automatically > updated. > > To update this link, you have to click on 'Edit', 'Link to External > Files...' and 'Update', as I already described yesterday. If the path > displayed under 'Sourcefile' does not correspond to the actual path of the > Calc spreadsheet, the correct path must be selected with the button > 'Modify...' before clicking on 'Update'. Finally, click on 'Close'. > > Now the modified value, namely 333, should actually be displayed in cell B2, > but this is not the case! The previous value 300 is still displayed! Thanks, I reproduce with the steps. The link updating must be attempted while still editing the OLE object. Arch Linux 64-bit Version: 7.2.0.0.alpha0+ Build ID: b36a40a809206a57160108b3361f42420f619c2e CPU threads: 8; OS: Linux 5.9; UI render: default; VCL: kf5 Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Built on 23 December 2020
Bibisected with linux-64-6.3 to https://git.libreoffice.org/core/commit/c357d5fbb0f2191577a1eae0a03f68e4cf51e0ba Postpone loading of all external references, including INDIRECT() Adding Cc: to Eike Rathke To bibisect, I just kept opening the .odt I created from scratch according to the steps, double-clicked the embedded Calc object, selected Edit - Links to External Files and clicked Update.
Dear Lothar Viel, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
I have a very similar problem, but with the linking of cells from a main sheet to cells in an embedded OLE sheet. I use the terms "source cell" as the source cell in the main sheet, and "destination cell" as the destination cell in the embedded OLE sheet. I used two methods to set up the link: the GUI, and the Paste Special > Link. 1) With the GUI method, I enter the equals sign in the inputbar for the destination cell, then select a source from the main sheet with some text, and press enter. Some file URL syntax is generated in the inputbar. 2) With Paste Special method, I select the source cell in the main sheet, copy, then Paste Special > Link for the destination cell. Some file URL syntax is generated in the inputbar. On freshly inserted OLE's with only one destination cell set up with method 1: 1) the destination cell's value is correct. 2) Edit > Links to external files... > Update can update the destination cell's value indefinitely. After a reload: I encounter different destination values, all conveying the same outcome (the links not working); when I redirect the link to another source cell through method 1 or 2, the destination cell's value becomes 0; and when I press Edit > Links to external files... > Update, no update happens to the destination cell's value. I even witnessed after I linked, the destination cell's value becoming an old version of the source cell's. But I haven't had the time to find the reproduction steps for this. Also, as my understanding of what is supposed to happen when entering the OLE edit mode comes from OpenOffice, there is a major step missing that I never witness in LibreOffice: the prompt for updating the links. OpenOffice always shows such prompt. So even if there would not be the mentioned bug, the prompt missing is also very annoying as you would need to manually update them. My GUI is in Dutch so I have translated some terms. Both OpenOffice and LibreOffice have this problem, and it seems to me that you need very similar steps to reproduce the bug in both. I used OpenOffice v4.1.15 and LibreOffice v7.6.5.2. The main sheet has been saved, with both programs at these versions. Version: 7.6.5.2 (X86_64) / LibreOffice Community Build ID: 38d5f62f85355c192ef5f1dd47c5c0c0c6d6598b CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: nl-NL (nl_NL); UI: nl-NL Calc: threaded This bug is critical as embedded modularity is non-existent for my sheets. I was so happy to break free from the main grid by embedding another grid (OLE), but then I discovered this.. Please fix it! OP, does your bug still occur? Who can reproduce mine?