Comment Formatting is lost when setting value


  • When using

    range.Value[XlRangeValueDataType.xlRangeValueXMLSpreadsheet] = someXml

    font for comments is not applied. This can be even tested using

    range.Value[XlRangeValueDataType.xlRangeValueXMLSpreadsheet] = range.Value[XlRangeValueDataType.xlRangeValueXMLSpreadsheet];
    Other character formatting seems to be alright. Is there any workaround to this?

    Monday, March 14, 2011 9:03 AM

All replies

  • Hi Igor,

    Thank you for posting and we are glad to help with you.

    After reading your post, I felt that I am not very clear about your problem. What's the type of your project? What's the version of your Office? It will be better if you can show us more detailed code snippet.

    I think you may reset the format of the comment. Here is the article about manuplating Excel comments via VBA:

    I hope this can help you and feel free to follow up after you have tried.

    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 17, 2011 7:45 AM
  • Hi Bruce,

    I'm using Shared Add-In project and Office 2007. To reproduce the problem I've created a sample project. You can download it here: It includes excel file XMLProblem.xlsx. To reproduce the problem click on the cell with comment and click on the ribbon button XMLProblem. The code behind the button is:

    var app = ((Application) applicationObject);
    app.ActiveCell.Value[XlRangeValueDataType.xlRangeValueXMLSpreadsheet] = app.ActiveCell.Value[XlRangeValueDataType.xlRangeValueXMLSpreadsheet];

    So if I just assign the active cell the same XML it had, the comments loose some formatting.

    Another problem which I found is that color formatting for cell contents is "lost". For example - in the same file, the cell contents of cell E10 have color #8B0000 (R: 139 G: 0 B: 0). After clicking the XMLProblem button (when the cell is selected), the color resets to #800000 (R: 128 G: 0 B: 0), which is one of Standard colors.

    The link you provided doesn't help, as they don't work with comments as formatted text, but as a plain text. I need to retrieve and set comments as formatted text.



    Thursday, March 24, 2011 8:22 AM