Add a hyperlink in excell through matlab

Dear Matlabians!!!

I have build a script that adds a lot of data from multiple excels to one general excel. There is a huge amount of data that are being organize thus it is very important for me and the other users of the final excel to know from which files the data came from. Thus I have include the name of the initial excels on my final excel.

Is it possible now to put through matlab a hyperlink in my final excel that will link each name of the initial excels to there files in there separated folders? I don't want to do that manually as it will take years. To be able to extract the data in the first place I have to include there paths so I have there names and there path in the hard-drive.

i found a line of code that could do that but i really don't get how it could be of use. http://www.mathworks.com/matlabcentral/newsreader/view_thread/244494

Add = handle Add(handle, handle, string, Variant(Optional))

Thank you very much Alex

[Alexandros]

I did something that could be of help

I did a list though matlab to write the path of the each file in on column in another sheet

Is it more easy now to connect the two file names with the list of the path as hyperlinks ?

Do i need VBA?

Answer 1

[Alexandros]

I think i found a way a little more easy. Because there is already a function in excel for hyperlink, I did

first: a list with all the names of the file and there path through matlab to be paste in excell

second: then by using strcat i was able to put in a loop the cells of the list and do a =hyperlink(cell1, cell2) string in matlab to be paste where i want the hyperlinks to be in excel and it worked

h = ['=Hyperlink(']

h1 = cell1number e.g A13

h2 = ','

h3 = cell2number e.g B13

h4 = ')'

strcat(h,h1,h2,h3,h4)

makes '=Hyperlink(A13, B13)'

AMAZING

[Krutant Mehta]

This worked for me, however I would like to post a suggestion. For my purpose, the filepath for the file I wanted hyperlinked was quite large so posting the filename unnecessarily in a cell wasn't good enough. You can instead use quotation marks ("") to show to excel that text is a filename. For example, if you had a filepath, F, and text to show in the link, T, the resulting output of the strcat would be:

stringToInsertIntoExcel = strcat('=HYPERLINK("',F,'","',T,'")')

Insert the above line into your code to insert into excel, and use appropriate variables for F and T, and the result will be a hyperlink of name T, pointing to location F

[James Daugherty]

I did this, but in excel it first just shows the whole think as text in the cells. I have to go through the spreadsheet and click on each cell then press enter to have excel make it a hyperlink. What am I doing wrong?

[Daniel]

Was just trying to do the same thing and discovered that setting the 'useExcel' flag to true fixes it!

writetable(T,'testExcelFromMatlab.xlsx','UseExcel',true);

Answer 2

[Kaustubha Govind]

I modified an example in the documentation to add one link to a specific cell (Sheet1, Cell I2) in an Excel file:

exl = actxserver('excel.application');
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/input_resp_data.xls']);
%exl.Visible=1; %This make the Excel COM server visible
exlSheet1 = exlFile.Sheets.Item('Sheet1');
rngObj = exlSheet1.Range('I2'); %we will add the link to this cell
exlSheet1.HyperLinks.Add(rngObj, 'http://mathworks.com');
exlFile.Save();
exlFile.Close();
exl.Quit;
exl.delete;

You should be able to put a loop around the call to exlSheet1.HyperLinks.Add to achieve what you need.