Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
I have figured out how to copy and paste a whole comment as in...
Any ideas?
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
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
Any ideas?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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!
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
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 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.
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.
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 :(
Sorry :(
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.
(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.
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.
(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.
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...
Brian.Extract-Comments.xlsm
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
Brian.Extract-Comments.xlsm
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.
Then I plan to use a Select Case (...), or If (...) Then, to handle the value of varWholeCommentSubscriptCh
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
Pseudocode:
(...I suddenly have a feeling this might warrant a whole seperate question.)
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
Then I plan to use a Select Case (...), or If (...) Then, to handle the value of varWholeCommentSubscriptCh
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
Pseudocode:
varOutput = Me("Range(" & strTargetRange & ").Comment.Shape.TextFrame.Characters.Font" & "." & strPropertyName)
(...I suddenly have a feeling this might warrant a whole seperate question.)
I am amazed Brian figured out a way to get there :)
Anyway, this seems to work:
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.
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
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.
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/Unde
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.
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/Unde
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.
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.
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!
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!
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:
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.
Edit 1: Typo in code, "PUBLIC Sub SummarizeComments(..."
CommentExtractor.xlam
basCommentHandler.bas
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial