Excel: Extract Cell Comments WITH FORMATTING into Cell Text

I can't figure this out for the life of me... even manually in Excel, so that I can sick "Macro Recorder" on it.

Q: How can you copy or get the content ("custom formatted text") of an Excel (2007) cell comment out, and paste it into a cell as a cell's content (again, "formatted text") ?
Solutions accepted manually or programmatically.

Ultimately, I need to turn this into VBA. Right not I'm using something similar to the "showcomments" function here:
http://www.contextures.com/xlcomments03.html

I have figured out how to copy and paste a whole comment as in...

Dim rngSource As Range
Dim rngTarget As Range
Set rngSource = ...
rngSource.Copy
Set rngTarget = ...
rngTarget.PasteSpecial Paste:=xlPasteComments

Open in new window

...but not how to get the comment (and it's formatting) out.

Any ideas?

LVL 1

travisjbennettAsked: 2012-12-27

Who is Participating?

Solutions

LVL 26

redmondbConnect With a Mentor Commented: 2012-12-27

Hi, travisjbennett.

The documentation isn't great, but you can get at least some of the formatting. For example, the line below displays the Font Name for the three characters starting in position 15 of the comment's text...
Debug.Print MyComment.Shape.TextFrame.Characters(15,3).font.name
(If these characters have multiple fonts then "Null" is displayed.)

So, until someone explains the correct way to do this, a horrible workaround would be to work your way through each character looking for changes in the formatting.

Edit: Please see here for examples.

Regards,
Brian.

LVL 53

Martin LissOlder than dirtCommented: 2012-12-27

Assuming that it can be done manually, just record a macro while you do it and then end and edit it to see the code.

LVL 1

travisjbennettAuthor Commented: 2012-12-27

Right, that's what I meant by "I can't figure this out for the life of me... even manually in Excel, so that I can sick "Macro Recorder" on it."

I can't even figure out how to do this manually.

Comments can totally be custom-formatted... fonts, sizes, colors, even superscripts and subscripts. I just can't figure out how to get them out!

LVL 13

Shanan212Commented: 2012-12-27

sub sample
dim r as range
for each r in rngSource 
       copycomment(r)
next r
end sub

function copycomment(commentCell as range)   
        copycomment = commentCell.comment.text   
end function

Open in new window

Just giving an idea. Haven't tested the code.

This however copies your comment INTO the same cell as its is in

Say A1 has a comment of "hi"

Then after this sub, the cell A1 would have "hi" as its content.

Formatting of comment; I am not sure how to copy that. I formatting it via VBA after copying the text.

LVL 1

travisjbennettAuthor Commented: 2012-12-27

Shanan212: Yeah, that's very similar to what I'm using from contextures (link in the original questions). Of course, not the same cell in my case.

I'm specifically after the formatting. There is a boatload of formatting, especially font and super/subscripts, that I need to extract. I've got everything else working.

LVL 53

Martin LissOlder than dirtCommented: 2012-12-27

I'm sitting in a beach in Maui so I don't have a PC in front of me but can't you do something like Copy|Paste Special|Comments?

LVL 1

travisjbennettAuthor Commented: 2012-12-27

Martin: Rough life! lol. What are you doing on EE ???

That only copies and pastes whole comments.
In other words, wherever you paste, there is a brand new comment, same as the copied one.
Sadly, it doesn't get the text out of the comment.

LVL 53

Martin LissOlder than dirtCommented: 2012-12-27

What are you doing on EE ???

That's very similar to what my wife just said:)

LVL 93

Patrick MatthewsCommented: 2012-12-27

The comment text is accessible via VBA, but there is nothing in the object model that will allow you to get at the formatting (font, bold, italic, etc) of the comments.

Sorry :(

LVL 26

redmondbConnect With a Mentor Commented: 2012-12-27

travisjbennett,

Continuining on from my last post, the macro in the attached extracts each cell's text to the comment's cell and formats the cell with the comment's format - as long as that format applies to the entire comment. (Some formats can be extracted for individual characters (e.g. Font Name) but others (e.g. Superscript) apparently can't.)

So, this does not handle multiple formats within a comment (e.g. B2 in the attached).

Also, the sequence of applying a the formatting is probably rubbish (for example, I should probably do the Font name first.)

The code is...

Option Explicit

Sub Extract_Comments()
Dim xComment As Comment
Dim xCell As Range

Application.ScreenUpdating = False

    For Each xComment In Sheets("Sheet1").Comments
        
        Set xCell = xComment.Parent
        xCell = xComment.Text
        With xComment.Shape.TextFrame.Characters.Font
            xCell.Font.Bold = .Bold
            xCell.Font.Color = .Color
            xCell.Font.FontStyle = .FontStyle
            xCell.Font.Italic = .Italic
            xCell.Font.Name = .Name
            xCell.Font.Size = .Size
            xCell.Font.Strikethrough = .Strikethrough
            xCell.Font.Subscript = .Subscript
            xCell.Font.Superscript = .Superscript
            On Error Resume Next
                xCell.Font.ThemeColor = .ThemeColor
                xCell.Font.TintAndShade = .TintAndShade
            On Error GoTo 0
            xCell.Font.ThemeFont = .ThemeFont
            xCell.Font.Underline = .Underline
        End With
        
    Next

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.Extract-Comments.xlsm

LVL 1

travisjbennettAuthor Commented: 2012-12-27

Brian,

Nice find! Bulky, but potentially usable (you can tell I'm despirate).

It might be possible to make it less bulky:
I'd like to put this into use, for a few specific things:
.Bold
.Italic
.Underline
.Strikethrough
.Subscript
.Superscript
I can tackle .color and .colorindex, and maybe .size, later. Much lower priority.

So, in the code below, if I take a given property, I can check the whole comment first to see if it applies at all. As you mentioned, if it doesn't, it'll be false, and if it's mixed, it'll be Null. Note Err.Number = 91 means no comment on that range.

varWholeCommentSubscriptCheck = Range(strTarget).Comment.Shape.TextFrame.Characters.Font.Subscript

Open in new window

Then I plan to use a Select Case (...), or If (...) Then, to handle the value of varWholeCommentSubscriptCheck and determine if I need to go through the comment character-by-character, perhaps first in blocks of 200, then character by character within that (it seems Excel likes it that way when playing with Macro Recorder... whatever).

Without this check, I'd be searching through comments needlessly for properties that don't apply. Now, less bulky.

However, I'd have to repeat code over and over for each of the 6+ properties.

My follow-up question is thus:
Q₂: Can I make a function or subroutine out of this whole kit and caboodle, pass the range to it, and pass a property to it so that I can recycle my subroutine/function for each of my many properites I have to check?

This could make the check described sufficiently unbulky to be usable.

I know in Access, I've used a Me() function to do things like Me(strControlName).BGColor and make up control names on the fly. So, can something like this be done in Excel to apply a variety of properties, something kind of like
Pseudocode:

varOutput = Me("Range(" & strTargetRange & ").Comment.Shape.TextFrame.Characters.Font" & "." & strPropertyName)

Open in new window

... where strPropertyName might be passed in as any of the 6+ above?

(...I suddenly have a feeling this might warrant a whole seperate question.)

LVL 93

Patrick MatthewsConnect With a Mentor Commented: 2012-12-27

I am amazed Brian figured out a way to get there :)

Anyway, this seems to work:

Sub GrabComments()
    
    Dim rng As Range
    Dim cel As Range
    Dim Cmt As Comment
    Dim CmtLen As Long
    Dim tf As TextFrame
    
    With ActiveSheet
        Set rng = Intersect(.UsedRange, .[a:a])
        For Each cel In rng.Cells
            If Not cel.Comment Is Nothing Then
                Set Cmt = cel.Comment
                Set tf = Cmt.Shape.TextFrame
                cel.Offset(0, 1) = Cmt.Text
                For CmtLen = 1 To Len(Cmt.Text)
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Name = tf.Characters(CmtLen, 1).Font.Name
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Bold = tf.Characters(CmtLen, 1).Font.Bold
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Italic = tf.Characters(CmtLen, 1).Font.Italic
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Underline = tf.Characters(CmtLen, 1).Font.Underline
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Strikethrough = tf.Characters(CmtLen, 1).Font.Strikethrough
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Subscript = tf.Characters(CmtLen, 1).Font.Subscript
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Superscript = tf.Characters(CmtLen, 1).Font.Superscript
                    cel.Offset(0, 1).Characters(CmtLen, 1).Font.Size = tf.Characters(CmtLen, 1).Font.Size
                Next
            End If
        Next
    End With
    
End Sub

Open in new window

As for your Part 2, yes it could be done. Just take in a property name as an argument, and use an If...ElseIf...Else construct to determine which property gets used.

LVL 26

redmondbCommented: 2012-12-27

travisjbennett,

Don't worry about the bulky - that was purely proof of concept and could easily be tidied up. However...

... Excel 2010 doesn't seem to play fair with most of the attributes you're concerned about. Bold and Italic are OK, but the other four can only be extracted if they cover the entire comment.

This seems to be a complete show-stopper for 2010. I'll try 2007, but I expect it to be the same.

If you're desperate, I suppose you could get your hands very dirty and pull the data from the Xml files, but I wouldn't even know where to begin with that.

Edit: matthewspatrick, my mistake, I put StrikeThrough in the wrong category. However, your code doesn't handle SuperScript/SubScript/Underline in Excel 2010.

Edit2: They don't work in Excel 2007 either.

Edit3: I had a look at how the comments and their formatting are stored in an xlsx/xlsm file and it's actually quite understandable - so a subscript character has a "vertalign" attribute with a value of "subscript", an italic character is indicated by the presence of an "i" attribute, etc. If this is an important, one-off exercise I'd definitely think about looking into this as a possible approach.

Regards,
Brian.

LVL 85

Rory ArchibaldConnect With a Mentor Commented: 2012-12-28

You could use CallByName if you want to pass a property as a string. I'm not really clear on why you want to do that.

LVL 1

travisjbennettAuthor Commented: 2012-12-28

WOW! It all works (even underline, with a constant instead of a boolean), except for superscript and subscript. Even font colors work! Superscript and subscript aren't worth the extra effort at this point.

Ok, so I'm cleaning up the code, to make it clean and efficient. e.g. No need to loop through every character for a property if it doesn't apply at all. I plan to finish it shortly after the weekend, and post the procedures here. But it works awesomely.

Rorya: CallByName is perfect. It will let me clean up the code. I plan to use a few arrays to manage my list of properties I want copied. An arrays containing the list will be used to at least (1) check the comment as a whole, and (2) get the formatting in the comment per character, and (3) apply that to the cell per character.
It may or may not be more efficient, but it will allow me to easily manage what format properties get commented, and what doesn't.

So again, I'll post the procedures and award points next week. Thank you so much everyone, especially Brian/RedmondB for finding access to the properties, MatthewsPatrick for the character-by-character methodology, and Rorya for exactly what I was looking for to keep my code organized.

Happy New Year!

LVL 1

travisjbennettConnect With a Mentor Author Commented: 2013-01-02

Alright... here it is. Thanks again everybody! I wish I could increase the total point value on this one, but instead I'll just post the results, especially since what's out there hasn't been cultivated yet.

In Summary:
Summarize to a new worksheet, or copy/extract to a range of cells, Microsoft Excel Comments with Formatting. Tested for Excel 2007 and Excel 2010.

Limitations:

Won't work with superscripts.

Won't work with subscripts.

Based on ".TextFrame", so properties and other elements of ".TextFrame2" are not implemented.

Excel 2010's Double-Underline property is not yet implemented.

(Add-in only)Exporting comments without formatting is unimplemented. In the VBA, boolean arguements may be made to the subroutine to skip font name, size, or any other combination of properties, or even skip them all.

The two attachments are the same stuff. Just two different packaging options depending on your desired use. Add-ins are great for end-users, and this one implements it's own ribbon. Bas modules are better suited for those desiring to incorporate it into part of their own projects.

CommentExtractor.xlam -- Unsecured Excel Add-In, Adds a "Comment Tools" ribbon after to the "Review" ribbon.

basCommentHandler.bas - Importable VBA module for Excel 2007 and Excel 2010.

Edit 1: Typo in code, "PUBLIC Sub SummarizeComments(..."
CommentExtractor.xlam
basCommentHandler.bas

LVL 1

travisjbennettAuthor Commented: 2013-01-07

The contributing (non-author) posts were awarded all the points for answering the question with functional methods, and the "Best Solution" flag for the most applicable method.

An example of the implementation of the methods, in an Excel Add-In and VBA Module, are provided in the final author's post. Thus, this post was marked as a solution for reference and those reading through. I awarded myself neither points nor best solution flags.