How to include Excel hyperlinks using readtable

When using readtable to import an Excel file that includes a column of hyperlinks, the hyperlink text is not imported. For example:

=HYPERLINK("https://LinkPath/Item?number=1234","Item 1234")

Gets imported as "".

Is there a way for such hyperlink definitions to be included when importing an Excel file using readtable (or another method)?

[dpb]

The builtin functions supplied for workbooks read only the data contained in the worksheet range, the ."Value" property of the referenced range object.

The hyperlink is not stored as the .Value property of the range; there's another object Hyperlink (which is a member of the worksheet/workbook "Hyperlinks" collection) that holds the address. The only way to return the string value of the hyperlink itself would be to use ActiveX and retrieve it that way. I've not seen any FEX or other submissions that have done this; it would roughly parallel a crude function I put together to retrieve the comments except use the hyperlinks collection instead of the commentsCollection here...

function [cmnts]=xlsComments(Excel, rangeObject)
  % return comments from Excel sheet in range given

  % Input called rangeObject definition--for reference 
  % UsedRange is user input range or if not given as found area used
  % rangeObject = Excel.Application.ActiveSheet.UsedRange;

    % Allocate space same size a the input range object to place comments into
    cmnts=cell(rangeObject.Rows.Count,rangeObject.Columns.Count);
    rowOffset=rangeObject.Row-1;                      % Get first row in range
    colOffset=rangeObject.Column-1;                   % Get column row in range
    % Get Handle to Comments collection -- this is, unfortunately, worksheet global
    commentsCollection=Excel.Application.ActiveSheet.Comments;
    nComments=commentsCollection.Count;
    for i=1:nComments                                 % Iterate over the collection
      comment=invoke(commentsCollection,'Item',i);    % Get handle to each in turn
      parent=comment.Parent;                          % Handle to parent cell
      addr=parent.Address;                            % Address string of cell
      rnge=parent.Cells;                              % Parent cell range object
      if ~xlsInRange(Excel,rangeObject,rnge),continue,end  % Skip if not in range
      [r,c]=xlsRowCol(addr,-rowOffset,-colOffset);    % Convert to row,col indices
      cmnts{r,c}=comment.Text;                        % Save each in cell array
    end  
  return

At the time I did the above, I was total novice at writing COM to address the Excel object model; I couldn't make anything work to retrieve the comments only from a specific cell; only by iterating over the entire collection and testing that the given comment was in a location I was interested in...hence the klunky appearance.

I subsequently learned more but never had the need to improve this particular function; it was for one specific task only...

Anyways, it will give you the flavor of what will need to do; the VBA doc of relevance is at <vba/api/excel.hyperlinks>

[RM]

Thanks dpb. This is very helpful information and gives me a direction to pursue.

[Jeff Mattice]

Were you ever able to get this working?