2
$\begingroup$

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]?

$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – Sander Jan 9 at 12:59
  • $\begingroup$ 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. $\endgroup$ – Alexey Popkov Feb 12 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.