How to write xls comment nodes (red corners) from Matlab ?
5 views (last 30 days)
Show older comments
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:
0 Comments
Accepted Answer
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
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.
More Answers (1)
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
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...
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!