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

9 views (last 30 days)
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:

Accepted Answer

Image Analyst
Image Analyst on 27 Aug 2014
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
  2 Comments
Image Analyst
Image Analyst on 27 Aug 2014
Actually, looking it over, it looks like it just adds to the "Active" sheet. It also requires ExcelCol from the File Exchange.
Arnaud
Arnaud on 27 Aug 2014
Edited: Arnaud on 27 Aug 2014
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

Sign in to comment.

More Answers (1)

Iain
Iain on 27 Aug 2014
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
  2 Comments
Iain
Iain on 27 Aug 2014
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
Arnaud on 27 Aug 2014
Edited: Arnaud on 27 Aug 2014
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)

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!