MATLAB Answers

How can I get Hyperlinks' addresses from an Excel file?

21 views (last 30 days)
Fabio Masino
Fabio Masino on 15 Jun 2018
Answered: Guillaume on 15 Jun 2018
Hello, if I have an Excel file which contains Hyperlinks, how could I obtain by Matlab the link information (not the displayed text in the cell)?
Here my partial code:
file = 'Sample.xlsx';
sheet = 'Sheet1';
% load Excel data
[~,textData] = xlsread(file,sheet);
In this example code, textData doesn't contain the address information.
I hope the question is clear. Thanks in advance


Sign in to comment.

Accepted Answer

Guillaume on 15 Jun 2018
Matlab does not have a function to retrieve the target of hyperlinks and the way excel stores hyperlink is a bit convoluted as the hyperlinks are not properties of the cell themselves, they're stored in a separate collection. The following function will retrieve all the hyperlinks in a spreadsheet:
function [links, locations] = xlsgethyperlinks(file, sheet)
%retrieve all hyperlinks and their location in an excel worksheet
% [links, locations] = xlsgethyperlinks(file);
% [links, locations] = xlsgethyperlinks(file, sheet);
% file: full path of the excel workbook (scalar string / char vector)
% sheet: name or index of worksheet (scalar string / char vector / scalar positive integer). Default is 1
% links: column cell vector of hyperlink targets in the order returned by excel
% locations: column cell vector of char vectors containing the location of the hyperlinks in the sheet, in RC format
% Author: G. de Sercey
% BSD license
if nargin < 2
sheet = 1;
%todo: add input validation
%The code starts excel, load the workbook, then iterates over all the hyperlinks in the worksheet storing the hyperlink address and the range address of the hyperlinks.
%excel is closed automatically by the onCleanup when the function terminates (normally or due to errors).
excel = actxserver('Excel.Application');
cleanup = onCleanup(@() excel.Quit);
workbook = excel.Workbooks.Open(file);
worksheet = workbook.Worksheets.Item(sheet);
hyperlinks = worksheet.Hyperlinks;
links = cell(hyperlinks.Count, 1);
locations = cell(hyperlinks.Count, 1);
for hidx = 1:hyperlinks.Count
hyperlink = hyperlinks.Item(hidx);
links{hidx} = hyperlink.Address;
locations{hidx} = hyperlink.Range.Address;
Note that I've just written this on the fly just now. There may be bugs.


Sign in to comment.

More Answers (0)




Community Treasure Hunt

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

Start Hunting!