How to extract comments to Word from a specific keyword

[potnoodle]

Hi Guys
I need to extract comments from an Excel workbook based on a name - we are using the comments feature to add data to how a student has behaved that day, ie George was a pain in the butt . I have found the code online to extract all of the comments in the sheet to Word, how can I amend this to just look for those with a students name i.e. George in the comment? Many thanks in advance

Sub CopyCommentsToWord()



Dim xComment As Comment
Dim wApp As Object
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set wApp = CreateObject("Word.Application")
End If
wApp.Visible = True
wApp.Documents.Add DocumentType:=0
For Each xComment In Application.ActiveSheet.Comments
wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text
wApp.Selection.TypeParagraph
Next
Set wApp = Nothing
End Sub

 

[RobertSF]

Hi Guys
I need to extract comments from an Excel workbook based on a name - we are using the comments feature to add data to how a student has behaved that day, ie George was a pain in the butt . I have found the code online to extract all of the comments in the sheet to Word, how can I amend this to just look for those with a students name i.e. George in the comment? Many thanks in advance

The comment text is in the comments Text property, so something like this should work.

Sub CopyCommentsToWord()
Dim xComment As Comment
Dim wApp As Object

On Error Resume Next

Set wApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
     Err.Clear
     Set wApp = CreateObject("Word.Application")
End If

wApp.Visible = True

wApp.Documents.Add DocumentType:=0

For Each xComment In Application.ActiveSheet.Comments
    If instr(xComment.Text, "George") then
         wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text
         wApp.Selection.TypeParagraph
    End If
Next

Set wApp = Nothing

End Sub

I hope that helps!

 

[potnoodle]

The comment text is in the comments Text property, so something like this should work.

Sub CopyCommentsToWord()
Dim xComment As Comment
Dim wApp As Object

On Error Resume Next

Set wApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
     Err.Clear
     Set wApp = CreateObject("Word.Application")
End If

wApp.Visible = True

wApp.Documents.Add DocumentType:=0

For Each xComment In Application.ActiveSheet.Comments
    If instr(xComment.Text, "George") then
         wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text
         wApp.Selection.TypeParagraph
    End If
Next

Set wApp = Nothing

End Sub

I hope that helps!

That's great many thanks Robert