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
Posting code? Use <pre> tags for VBA and <code> tags for inline.