locked
How to add comment with lenght > 32767 RRS feed

  • Question

  • Hello,

    I have the following C# 4.5 code using  Microsoft.Office.Interop.Excel

    using Excel = Microsoft.Office.Interop.Excel;
    string s1 = "", s2 = "", msg = "";
    int i, i1 = 2, i2 = 100, i3 = i2;
    int l1 = 0, l2 = 0, ld = 0;
    bool eq = false;
    
    Excel.Range ar = null;
    Excel.Comment cm = null;
    Excel.Comment next = null;
    try
    {
        ar = ExcelHelper.ActiveCell;
    
        // make clear that there are no comments
        ar.ClearComments();
    
        i3 = (int)Math.Pow(i2, i1);
        for (i = 0; i < i3; ++i)
        {
            s1 += i + "a";
        }
    
        l1 = s1.Length;
    
        ar.AddComment(s1);
    
        cm = ar.Comment;
        s2 = cm.Text();
        next = cm.Next();
        l2 = s2.Length;
        ld = l1 - l2;
    
        eq = s1 == s2;
    }
    catch (Exception ex)
    {
        msg = ex.ToString();
    }
    finally
    {
        ExcelHelper.cleanupComObject(ref next);
        ExcelHelper.cleanupComObject(ref cm);
        ExcelHelper.cleanupComObject(ref ar);
    }
    

    The string s1 I want to add as comment has a length of about 49000 characters, but the string s2 I get back from the comment has only a size of 32767.

    Is there a way to add a bigger cooment than 32767 to a range and get the whole string back ?

    tia

      Hendrik Schmieder

    Monday, January 26, 2015 1:11 PM

Answers

  • Hi Tia,

    Based on the descrption, you want to set a comment which length is more than 32767.

    As far as I test, this is a limitation of the length of comment. When we set a string which length is bigger than 32767, it would be cut off. This limitation also extis when we set the comment manually.

    Here is the test code for your reference:

    Sub AddComment()
    Dim a As String
    For i = 1 To 32767
        a = a + "a"
    Next i
    a = a + "b"
    Range("F3").Comment.Text (a)
    End Sub
    

    I suggest that submit feedback from link below if you want to Excel support the length of comment more than 32767.

    Submit Feedback - Microsoft Support

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 27, 2015 3:18 AM
  • Hi Hendrik Schmider,

    >>I have a XML which I must save with a range, so that the XML is copied if the range is copied.

    Comment would be a good candidate for this except that the length is limited.

    Are there any other possibilites ?<<

    A possible workaround is that resotre the XML to the file out of Excel and we can insert the filepath into the comment for a specific range.

    Hope it is helfpul.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 28, 2015 5:43 AM
  • Hi Hendrik Schmieder,

    >>  Is there an Event for copying sheet In Excel ?<<

    No, there is no such event in Excel. However may I the reaonse that you want to copy the XML when copy the range or worksheet?

    Based on understanding, if the commnent is same, they can point to the same file. And if you want to change the comment then you can create another XML file and point the new one.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 29, 2015 6:28 AM

All replies

  • Hi Tia,

    Based on the descrption, you want to set a comment which length is more than 32767.

    As far as I test, this is a limitation of the length of comment. When we set a string which length is bigger than 32767, it would be cut off. This limitation also extis when we set the comment manually.

    Here is the test code for your reference:

    Sub AddComment()
    Dim a As String
    For i = 1 To 32767
        a = a + "a"
    Next i
    a = a + "b"
    Range("F3").Comment.Text (a)
    End Sub
    

    I suggest that submit feedback from link below if you want to Excel support the length of comment more than 32767.

    Submit Feedback - Microsoft Support

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 27, 2015 3:18 AM
  • Maybe I should say something about my use case.

    I have a XML which I must save with a range, so that the XML is copied if the range is copied.

    Comment would be a good candidate for this except that the length is limited.

    Are there any other possibilites ?

      Hendrik Schmieder

    Tuesday, January 27, 2015 12:27 PM
  • Hi Hendrik Schmider,

    >>I have a XML which I must save with a range, so that the XML is copied if the range is copied.

    Comment would be a good candidate for this except that the length is limited.

    Are there any other possibilites ?<<

    A possible workaround is that resotre the XML to the file out of Excel and we can insert the filepath into the comment for a specific range.

    Hope it is helfpul.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 28, 2015 5:43 AM
  • Hello,

    I already thought of storing the XML somewhere else and to store in the comment only a reference to the storage place.

    The Problem with this solution is copying the cell.

    example A1 of sheet1 get a comment.

    a) copying sheet1!A1 to Sheet2!B7

        Then Sheet2!B7 has the same comment as sheet1!A1.

       This can be handled via sheetchange Event:

      Check for CutCopyMode and if Target has some properties,

      then Copy XML to another place and update comment of Sheet2!B7

    b) copying sheet1:

       In this case Sheet1 (2)!A1 has the same comment as Sheet1!A1 and would Point to the same XML,

      not to a copy of the XML.

       Is there an Event for copying sheet In Excel ?

       If so I could make something similiar like in a)

    with best regards

     Hendrik Schmieder

    Wednesday, January 28, 2015 2:45 PM
  • Hi Hendrik Schmieder,

    >>  Is there an Event for copying sheet In Excel ?<<

    No, there is no such event in Excel. However may I the reaonse that you want to copy the XML when copy the range or worksheet?

    Based on understanding, if the commnent is same, they can point to the same file. And if you want to change the comment then you can create another XML file and point the new one.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 29, 2015 6:28 AM
  • Sorry for the delay.

    You are correct, copying isn't really the problem.

    A problem will occur if the XML should be changed.

    But in this case we can check for duplicate refeerences and store the XML under a new reference. 

    Thursday, February 5, 2015 10:21 AM