Outputting Worksheet Comments
All
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
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
www.nickhodge.co.uk
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
Leave a comment