Create a hyperlink in an array to be exported as Excel file

Question

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?

I think Hyperlink["xxx", url] is now recognized when exporting to XLSX. — Karsten W., Apr 18 '17 at 15:11

score 1 · Answer 1 · answered Mar 17 '17 at 12:37

1

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.

answered Mar 17 '17 at 12:37

Eisbär

1,476
7
14

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. – Alexey Popkov Mar 17 '17 at 13:13
1

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. – Eisbär Mar 20 '17 at 07:45
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. – Alexey Popkov Mar 20 '17 at 07:53
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. – Alexey Popkov Mar 20 '17 at 08:12

score 0 · Answer 2 · answered Apr 18 '17 at 21:37

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

Create a hyperlink in an array to be exported as Excel file

2 Answers2

Linked