2
$\begingroup$

I want to create an Excel file with some data and hyperlinks to a file. The goal is that in the end you can click in Excel the link and the file opens. The Excel file is to be exported from Mathematica. I tried this:

ClearAll[hyper]
hyper[name_, ort_] := "=HYPERLINK(\"" <> ort <> "\";\"" <> name <> "\")"
hhh = {{"a", "a", hyper["XXX", "D:\\Temp"]}}
Export["D:\\11.xlsx", hhh]

When I open the Excel file I have to go to the cell with the hyperlink, then go to the end of the hyperlink and have to press Enter. Then it works. As there will be many links in my real Excel file I need a solution that works straight away. Can the "Enter" part be added from Mathematica? Other solutions?

$\endgroup$
4
  • 1
    $\begingroup$ You could use JLink and follow, e.g., this. $\endgroup$ Mar 15, 2017 at 9:20
  • $\begingroup$ Uufff, I was hoping for something easy directly out of MMA. $\endgroup$
    – Eisbär
    Mar 15, 2017 at 10:48
  • $\begingroup$ the semi-colon should be a comma. (doesnt fix it though) $\endgroup$
    – george2079
    Mar 17, 2017 at 18:05
  • $\begingroup$ I think Hyperlink["xxx", url] is now recognized when exporting to XLSX. $\endgroup$
    – Karsten W.
    Apr 18, 2017 at 15:11

2 Answers 2

1
$\begingroup$

There is a little work around that is working. Do it as shown above but use:

Export["D:\\11.dat", hhh]

and then import this to Excel. Yes, it is a little breaking the rules as it is not directly an Excel file. In addtion the links are working but not formated in the usual (blue, underlined) way. So some post processing is needed.

$\endgroup$
4
  • $\begingroup$ Could you add more details? What version of Excel are you using, and how do you import the file? With Excel 2003 this solution doesn't work. $\endgroup$ Mar 17, 2017 at 13:13
  • 1
    $\begingroup$ The Excel Version is 2016. For opening the .dat file I just go to open and select that .dat file. Then I use Tabstopps as Separation marker. The data Format is Standard. After that the xxx is in the cell visible and if you click on it the Explorer window with the according Folder opens. The XXX does not have the style of the link but it works as a link. $\endgroup$
    – Eisbär
    Mar 20, 2017 at 7:45
  • $\begingroup$ Thank you, it does work with Excel 2003! I use Russian localized version of Excel, so I have to replace HYPERLINK with ГИПЕРССЫЛКА in the definition of your function hyper, and then it works. $\endgroup$ Mar 20, 2017 at 7:53
  • $\begingroup$ You can be interested in a way to automatize importing of .dat files via the Data ► Import from external source... ► Import data... Excel's menu item which I describe in this answer. $\endgroup$ Mar 20, 2017 at 8:12
0
$\begingroup$

This works (tested with Mathematica 8.0.4 and 11.1.0 on Windows 7 x64):

Export["test.xls", Hyperlink["D:\\Temp\\input.txt"]]

This also works:

Export["test.xls", Hyperlink["Open input.txt", "D:\\Temp\\input.txt"]]

In the exported Excel file the hyperlink is clickable immediately after opening the file.

Note that due to a bug for exporting as XLSX you need to use only forward slashes in the file paths:

Export["test.xlsx", Hyperlink["Open input.txt", "D:/Temp/input.txt"]]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.