MATLAB Answers

Add a hyperlink in excell through matlab

82 views (last 30 days)
Alexandros
Alexandros on 1 Dec 2011
Commented: Krutant Mehta on 26 Jun 2019
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
  1 Comment
Alexandros
Alexandros on 1 Dec 2011
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?

Sign in to comment.

Accepted Answer

Alexandros
Alexandros on 1 Dec 2011
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
  1 Comment
Krutant Mehta
Krutant Mehta on 26 Jun 2019
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

Sign in to comment.

More Answers (1)

Kaustubha Govind
Kaustubha Govind on 1 Dec 2011
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.

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!