How to write xls comment nodes (red corners) from Matlab ?

I use the standard xlswrite function to write data into xls from Matlab :

xlswrite(name_xls,xls_data,1,'A1');

I would like to write comments (the pop-up that comes when hovering over the cells) simultaneously :

    xlswritewithcomments(name_xls,xls_data,xls_comments,1,'A1');

Anybody knows if there is a function I did not find or some way to do this ?

Working with ActiveX is really not the most interesting part of Matlab, so if someone has a working code or pieces of code, I am interested.

Anyway, here is one of my attempts for comments :

xls macro :

    Range("A1").Select
    Range("A1").AddComment
    Range("A1").Comment.Visible = False
    Range("A1").Comment.Text Text:="Arnaud LASTNAME:" & Chr(10) & "qsd"

Matlab :

 >> Select(Range(Excel,'A1'));
 >> Excel.selection.AddComment;
 >> set(Excel.selection.Comment,'Visible',false);
 >> set(Excel.selection.Comment,'Text','My comment');
 Error using Interface.00024427_0000_0000_C000_000000000046/set
 Error: method or property not found

And Text IS defined :

 >> Excel.selection.Comment.Text

 ans =

Thanks Arnaud LASTNAME:

Answer 1

[Image Analyst]

Arnaud, below is my code for inserting comments. I make up a cell array of strings, instantiate Excel, and call this function.

%---------------------------------------------------------------------------------------------
% Add comments to cells on sheet.
% Sometimes this throws exception #0x800A03EC on the second and subsequent images.  It looks like this:
% "Error: Object returned error code: 0x800A03EC"
% It is because of trying to insert a comment for a worksheet cell when a comment already exists for that worksheet cell.
% So in that case, rather than deleting the comment and then inserting it, I'll just let it throw the exception
% but I won't pop up any warning message for the user.
function InsertComments(Excel, caComments, sheetNumber, startingRow, startingColumn)
try
  worksheets = Excel.sheets;
  %     thisSheet = get(worksheets, 'Item', sheetNumber);
  thisSheet = Excel.ActiveSheet;
  thisSheetsName = Excel.ActiveSheet.Name;  % For info only.
  numberOfComments = size(caComments, 1);  % # rows
  for columnNumber = 1 : numberOfComments
    columnLetterCode = cell2mat(ExcelCol(startingColumn + columnNumber - 1));
    % Get the comment for this row.
    myComment = sprintf('%s', caComments{columnNumber});
    % Get a reference to the cell at this row in column A.
    cellReference = sprintf('%s%d', columnLetterCode, startingRow);
    theCell = thisSheet.Range(cellReference);
    % You need to clear any existing comment or else the AddComment method will throw an exception.
    theCell.ClearComments();
    % Add the comment to the cell.
    theCell.AddComment(myComment);
  end
  
catch ME
  errorMessage = sprintf('Error in function InsertComments.\n\nError Message:\n%s', ME.message);
  fprintf(errorMessage);
  uiwait(warndlg((errorMessage));
end
return; % from InsertComments

[Image Analyst]

Actually, looking it over, it looks like it just adds to the "Active" sheet. It also requires ExcelCol from the File Exchange.

[Arnaud]

Yes that's it thanks :

 theCell.AddComment(myComment);

Which is not like the macro in xls... Is there any documentation existing on these matlab/activeX commands ?

As for existing comments, I use this, which deletes and creates back the comment, but I had also to put a try/catch as the delete does not always work :

 if ~isempty(Excel.selection.get('Comment'))
    Excel.selection.Comment.Delete;
 end
 try Excel.selection.AddComment; catch,end

Answer 2

[Iain]

There is a solution. Its called "write your own function".

Step 1: If you open up the code for xlswrite, you'll be able to see how matlab interacts with excel.

Step 2: Open excel and record a macro of you putting a comment into a cell.

Step 3: Read that macro's code.

Step 4: Adapt the code behind "xlswrite" to do what you need it to. - You'll likely need to use a fair amount of trial and error to get it right. "Excel.Show" might be of use :P

[Iain]

 set(Excel.selection.Comment,'Visible',false);
 set(Excel.selection.Comment,'Text','My comment');

Just a guess, but

 Excel.selection.Comment.Visible = 1; %  or
 Excel.selection.Comment.Show

Might work...

[Arnaud]

Does not (see syntax in next post)

Visible = 1 makes the comment always visible, not only on hover

The macro approach does not always work...

For example, it does not record everything (changes in the font of the comment), and when it does the code may not be very useful : it gives Comment.Select then selection.Font which cannot be used as it is in Matlab (I did not find how to do this yet)