Excel: Extract Cell Comments WITH FORMATTING into Cell Text

Posted on 2012-12-27
Medium Priority
Last Modified: 2013-01-07
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:

I have figured out how to copy and paste a whole comment as in...
Dim rngSource As Range
Dim rngTarget As Range
Set rngSource = ...
Set rngTarget = ...
rngTarget.PasteSpecial Paste:=xlPasteComments

Open in new window

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

Any ideas?
Question by:travisjbennett
  • 7
  • 4
  • 3
  • +3
LVL 50

Expert Comment

by:Martin Liss
ID: 38725235
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.

Author Comment

ID: 38725281
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

Expert Comment

ID: 38725291
sub sample
dim r as range
for each r in rngSource 
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.
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 38725302
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 50

Expert Comment

by:Martin Liss
ID: 38725314
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?

Author Comment

ID: 38725320
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 50

Expert Comment

by:Martin Liss
ID: 38725327
What are you doing on EE ???

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

Expert Comment

by:Patrick Matthews
ID: 38725405
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

Accepted Solution

redmondb earned 1000 total points
ID: 38725428
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.

LVL 26

Assisted Solution

redmondb earned 1000 total points
ID: 38725546

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

Application.ScreenUpdating = True

End Sub

Open in new window


Author Comment

ID: 38725564

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 600 total points
ID: 38725628
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
            End If
    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

Expert Comment

ID: 38725632

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.

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 38726292
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.

Author Comment

ID: 38727807
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!

Assisted Solution

travisjbennett earned 0 total points
ID: 38737429
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.

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(..."

Author Closing Comment

ID: 38750282
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.
LVL 26

Expert Comment

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question