# Excel file reference disappears after Import; how to avoid this?

EDIT

An excel file reference in a formula like =[source.xlsx]Sheet1!A1 will be replaced by [1]Sheet1!A1 after 'Import'.

How do I avoid this or, alternatively, how do I recover the original file name that this Excel cell references to?

END EDIT

A very simple example that replicates this behavior is set up by creating an excel file source.xlsx with the A1 cell value set to 5.

Export["source.xlsx", {{5}}];


I refer to this cell value from reference.xlsx.

Export["reference.xlsx", {{"=[source.xlsx]Sheet1!A1"}}];


Now when I import the reference.xlsx file, I do not get anything back as a formula because the cell value has not been evaluated by Excel.

Import["reference.xlsx"]
Import["reference.xlsx", "Formulas"]

(* Out[..]= {{{"=[source.xlsx]Sheet1!A1"}}}*)
(* Out[..]= {{{}}}*)


If I open reference.xlsx with excel and evaluate the cell and save, I arrive at the actual real life situation where I would like to find the file reference.

However the file reference in the formula =[source.xlsx]Sheet1!A1 has been replaced by [1]Sheet1!A1

Import["reference.xlsx"]
Import["reference.xlsx", "Formulas"]

(* Out[..]= {{{5.}}}*)
(* {{"[1]Sheet1!A1"}}}*)


How can I find the original file reference that was replaced by [1]?

• I managed to navigate the problem away by identifying where the unwanted replacement happens and creating a new excelsheet with the excel function `FORMULATEXT referring to the original cell, and then usiong that result to revert the replacement. Far from ideal though ... and hence keenly interested in real solutions. – Sander Jan 9 '19 at 12:59
• Have you tried saving your Excel file as "XML Table"? Excel recognizes such files as its native file format, they are automatically opened in Excel when you double-click them in the Windows Explorer. More importantly, Excel works with such files exactly as with its native XLSX files. Mathematica has much higher flexibility when importing such files. – Alexey Popkov Feb 12 '19 at 9:24