Excel: Extract Cell Comments WITH FORMATTING into Cell Text

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.

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!

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

Select all 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.

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.

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?

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.

What are you doing on EE ???

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

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 :(

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

Select all 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)

Select all 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.)

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.

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!

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.

Thanks, travisjbennett. I learned a lot on your dime!