Exporting comments

[Guest]

Can someone supply me with some code which examines a workbook, finds
comments in cells and then copies the contents of those comments into a
seperate workbook as the code runs through each worksheet in a workbook.

Mark

 

[Guest]

This will copy it to a WORD application.

Sub CopyCommentsToWord()

Dim cmt As Comment
Dim WdApp As Object
Dim aWS As Worksheet

On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set WdApp = CreateObject("Word.Application")
End If
Debug.Print "after err.number"

With WdApp
.Visible = True
.Documents.Add DocumentType:=0
For Each aWS In ActiveWorkbook.Worksheets
For Each cmt In aWS.Comments
.Selection.TypeText cmt.Parent.Address _
& vbTab & cmt.text
.Selection.TypeParagraph
Next
End With
Next aWS

Set WdApp = Nothing

End Sub

 

[Zone]

Mark,
I happened to have a subroutine that copies the text of comments
that reside on one sheet of a workbook to another sheet in the same
workbook. The Word solution that was posted is very nice, but if you
want to stay in Excel, this should get you part of the way there.
HTH, James

Sub ShowComments
Dim j as Integer
ThisWorkbook.Sheets("Comments").activate
cells.clearcontents
With ThisWorkbook.Sheets("Sheet1")
for j=1 to .Comments.Count
cells(j,"a")=.Comments(j).Text
next j
End With
End Sub