Outputting Worksheet Comments


The code below will look at all comments in the activeworkbook and save them to a text file on the root drive (C:Test.txt).

The comments will be one to a line, with the sheet and range address from whence they came, who added the comment and it’s text. Being in a text file should make it easy to import elsewhere if needed.

Sub writeComments()
Dim mycomment As Comment
Dim mySht As Worksheet
Open “C:Test.txt” For Output As #1
For Each mySht In ActiveWorkbook.Worksheets
    For Each mycomment In ActiveWorkbook.Worksheets(mySht.Name).Comments
        Print #1, “From “ & mycomment.Parent.Parent.Name _
            & “!” & mycomment.Parent.Address _
            & ” comes the comment: “ _
            & mycomment.Text
    Next mycomment
Next mySht
Close #1
End Sub

Happy Holidays
Nick Hodge
Microsoft MVP – Excel

Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.