Exporting Comments using VBA
January 18, 2010 • JP • 3 Comments • Rate This Article
• Links to this article
Reading comments is hard. Well, it is for me. You have to mouse around and wait for the comment to appear. But it's easy with a bit of programming. Here's how.
I've written a procedure that writes all comments in a workbook to a new sheet in that workbook. As written, it acts on all open workbooks and only creates a new sheet if there are comments. Also, it writes ALL comments in the workbook to the new sheet, and lets you know what sheet and cell the comment was in. Best part is the comments are untouched afterwards. This is a variation of code you can find on other sites such as Comment Auditing. Scroll down for a link to a sample workbook.
' export all comments in all workbooks
' to a new sheet in the same workbook
On Error GoTo ErrorHandler
Dim cmt As Excel.Comment
Dim cmts As Excel.Comments
Dim wkbk As Excel.Workbook
Dim wkbks As Excel.Workbooks
Dim wksht As Excel.Worksheet
Dim newWksht As Excel.Worksheet
Dim nextRow As Long
Dim headerValues As Variant
Dim headerRng As Excel.Range
Dim hasComments As Boolean
Application.ScreenUpdating = False
' get Workbooks collection
Set wkbks = GetWorkbooks
' loop through each worksheet in each workbook,
' if any workbook or sheet has comments, add a new
' sheet and write the comments
For Each wkbk In wkbks
For Each wksht In wkbk.Worksheets
' check if current sheet has comments
Set cmts = GetComments(wksht)
If cmts.Count > 0 Then
' add worksheet ONLY if we haven't already
If Not hasComments Then
Set newWksht = AddWorksheet(wkbk)
newWksht.Name = "Exported Comments"
' set up header row
headerValues = Array("Author", "Text", "Sheet")
Set headerRng = newWksht.Range(Cells(1, 1), Cells(1, UBound(headerValues) + 1))
With headerRng
.Value = headerValues
.Font.Bold = True
End With
' (re)set row counter for comments loop
nextRow = 2
' set comments flag for current workbook
hasComments = True
Else
Set newWksht = wkbk.Worksheets("Exported Comments")
End If
For Each cmt In wksht.Comments
newWksht.Cells(nextRow, 1).Value = cmt.Author
newWksht.Cells(nextRow, 2).Value = cmt.Text
newWksht.Cells(nextRow, 3).Value = wksht.Name & " - " & cmt.Parent.Address
nextRow = nextRow + 1
Next cmt
' prettify the range
With headerRng.CurrentRegion
.Columns.AutoFit
.Rows.AutoFit
End With
End If
Next wksht
' reset comments flag for next workbook
hasComments = False
Next wkbk
ProgramExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
Function AddWorksheet(ByRef wkbk As Excel.Workbook) As Excel.Worksheet
Set AddWorksheet = wkbk.Worksheets.Add(After:=wkbk.Worksheets(wkbk.Worksheets.Count))
End Function
Function GetComments(wksht As Excel.Worksheet) As Excel.Comments
Set GetComments = wksht.Comments
End Function
Function GetWorkbooks() As Excel.Workbooks
Set GetWorkbooks = Excel.Workbooks
End Function
I've included some short comments throughout the code, but let's briefly review what's going on.
First we set an object reference to the Workbooks Collection using a custom function called GetWorkbooks, so we can loop through it. We then loop through the Worksheets Collection of each Workbook. For each worksheet, we check if there are any comments (cmts.Count > 0). If not, we'll skip to the next worksheet, otherwise we set up the new worksheet. Then we loop through the Comments Collection and write values to the new worksheet, which we'll call "Exported Comments".
This worked great when there was only one worksheet. But what about if you have multiple sheets with comments? We don't want to create a new "Exported Comments" worksheet for every worksheet that has comments; we only want ONE worksheet with ALL comments from the workbook. So I edited the worksheet loop and added a boolean flag: the new worksheet is only added if the flag is false, and it is set to true after the worksheet is created the first time. So it won't be created again, but will be reused if subsequent sheets in the same workbook have comments.
The AddWorksheet function takes a given workbook (in this case, whatever the current workbook is in the loop) and adds a worksheet to it, then returns it to the calling function. It always adds the new worksheet as the last one. GetComments returns the Comments Collection for a given worksheet. GetWorkbooks returns the Workbooks Collection for the current Application Object.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Another take on Data Validation
Next Post: Listing built-in Access database properties
Good approach… Of course, you can also use a simple UDF like (http://chandoo.org/wp/2009/09/03/get-cell-comments/) to get the cell comments from a given cell.
Is it possible to left-click sheet or cell reference to "navigate" to that specific comment?
Yes. If you use the approach outlined in the link I posted at the beginning of the article, you can create hyperlinks to each cell that contains a comment.