Bug 116331 - XLSX FILEOPEN Reference to an external *.xlsx file’s data table results an Error:508
Summary: XLSX FILEOPEN Reference to an external *.xlsx file’s data table results an Er...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-DataRange
  Show dependency treegraph
 
Reported: 2018-03-10 09:24 UTC by Gabor Kelemen (allotropia)
Modified: 2023-10-30 22:54 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file with database table (16.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-03-10 09:24 UTC, Gabor Kelemen (allotropia)
Details
File referencing the database range in the other file - made with LO 6.0 (15.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-03-10 09:25 UTC, Gabor Kelemen (allotropia)
Details
Screenshot of the files in LO (366.86 KB, image/png)
2018-03-10 09:26 UTC, Gabor Kelemen (allotropia)
Details
Example file made with Excel 2013 (16.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-07 08:52 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2018-03-10 09:24:12 UTC
Created attachment 140534 [details]
Example file with database table

If we create a table in Microsoft Excel and refer to the table’s column in a function with an external *.xlsx file and open it with LibreOffice Calc, the reference file link will disappear/transform [1] and the [Column1] named range will be treated as an external file.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2010/2013/2016
2. Insert a table to the A1:A5 range.
2. Fill the A2:A5 cells with some number. Save the file as “Source.xlsx”
3. Open a new spreadsheet and create an Avarage function, which refer to the [Column1] of [Table1] of the “Source.xlsx” file in the A1 cell.
Here is an example:
	=Average('C:\Users\teszt_admin\Desktop\ujproba_link\Reference-to-an-external-datatable\Source-with-datatable.xlsx'!Table1[Column1])
4. Save the file as “Target.xlsx”
5. Open the “Target.xlsx” file with LibreOffice Calc. (Click OK to the Error message)
6. Save the file as “Target_LO.xlsx” and reload the file.

Actual results:
When we open (Import) the “Target.xlsx” file with LibreOffice Calc we got an Error message: The following external file could not be loaded. Data linked from this file did not get updated. file:///C:/Users/teszt_admin/Desktop/ujproba_link/Reference-to-an-external-datatable/Column1. Open the ‘Links to external files’ window and you will see the Column1 as file reference. At the function the reference file path will transform from the original path to [1] and we have an Error:508 code in the A1 cell.
When we save as the “Target.xlsx” file and reload we got two error message because of the two “file reference”. At the ‘Links to external files’ window you will see that the Source file name will be “1” and the “Column1” will be there.

Expected results:
The external file reference should not disappear or transformed to [1] and the [Column1] reference should not be at the file Links. The problem may be that the column name and the filename are not separated locally in the function formula. LibreOffice only takes into consideration that the column name is in a stapler “[]” just like the file name in the Excel syntax.

Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 4; OS: Windows 6.1; UI render: default; 
Locale: hu-HU (hu_HU); Calc: group
Comment 1 Gabor Kelemen (allotropia) 2018-03-10 09:25:06 UTC
Created attachment 140535 [details]
File referencing the database range in the other file - made with LO 6.0
Comment 2 Gabor Kelemen (allotropia) 2018-03-10 09:26:05 UTC
Created attachment 140536 [details]
Screenshot of the files in LO
Comment 3 Buovjaga 2018-03-14 14:51:17 UTC
Assuming this is confirmed by the Hungarian team -> NEW
Comment 4 m_a_riosv 2018-11-16 23:16:10 UTC

*** This bug has been marked as a duplicate of bug 76047 ***
Comment 5 NISZ LibreOffice Team 2020-10-07 08:52:31 UTC
Created attachment 166146 [details]
Example file made with Excel 2013

Somehow we forgot to upload the original file.
If both the attachment #140534 [details] and this one is open in Excel, the AVERAGE function in this one can recalculate after changes in the source file.

Calc cannot do this after opening both files in order.
Comment 6 QA Administrators 2023-07-24 03:13:14 UTC Comment hidden (obsolete)
Comment 7 Stéphane Guillou (stragu) 2023-10-30 22:54:16 UTC
Reproduced in recent trunk build:

Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 31fb3045dabdb27d913712f3abcade315e3ea9bd
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded