Writing a Hyperlink in Excel with Matlab

Hello,

i tried to write a Hyperlink in Excel. I create a string with '=Hyperlink("Link")'.

When i write the string into the excel file it does not execute the funtion.

F='Hyperlink_Name';
stringToInsertIntoExcel = strcat('=HYPERLINK("',F,'")');
Testcell={stringToInsertIntoExcel};
Test=cell2struct(Testcell,'Hyperlinks',2);
Test_table=struct2table(Test);
writetable(Test_table,'hyperlink.xlsx')

So writing the Link into the excelfile is not the problem. The Problem ist, that the Function =Hyperlink() does not execute.

Thank you for you time

[Geoff Hayes]

Christian - what is stringToInsertIntoExcel set to after the assignment? Does it have a valid hyperlink in it?

[Christian Sellmann]

Yes it has. If i click in the function in excel afterwards i can use the link. But i dont want to do this for 1000 hyperlinks.

[Geoff Hayes]

Christian - I'm not sure I understand. You say that you when you click on the cell that contains the hyperlink, then you can use the link. But isn't that the intent? From hyperlink function,

The HYPERLINK function creates a shortcut that jumps to another location in the current workbook, or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed, or opens the document you specified.

I understand this to mean that you need to click on the cell in order to invoke the hyperlink.

[Mario Malic]

This is the character array that works with the hyperlink that you should write into Excel. I am not sure whether I like it being placed to cell, then cell2struct, then struct2table, maybe you have some issue there? Is anything written in the cell in Excel, if yes, can you paste the value of one cell (you can hide the link)?

 hExcel.ActiveSheet.Range("A6").Value = '=HYPERLINK("https://www.mathworks.com")'

Answer 1

[David Leichtle]

Hi Christian,

to execute the hyperlink you already have been written with writetable, set the "UseExcel" parameter to true.

writetable(Test_table,'hyperlink.xlsx','UseExcel',true)

With this line the hyperlink is active after writing it to the excel file.

Answer 2

[Ananya Tewari]

I understand you want to insert Hyperlink in an excel through MATLAB. The writetable() command is not able to execute the "=HYPERLINK()" and you need to manually make it as a function to execute. Creating excel object can help you achieve the desired output:

exl = actxserver('excel.application');
exlWorbook = exl.Workbooks;
excelFile = exlWorbook.Open('Location\hyperlinks.xlsx');
exlSheet1 = exlFile.Sheets.Item('Sheet1');
rngObj = exlSheet1.Range('A1'); %Cell where you want add the hyperlink
exlSheet1.HyperLinks.Add(rngObj, 'https://google.com');
excelFile.Save();
excelFile.Close();
excel.Quit;
excel.delete;

Another possible way of doing the same is by using xlswrite(), but it is not a recommended way.

a={'=HYPERLINK("https://google.com")'}
xlswrite('hyperlinks.xlsx',a)