Home » Development » How to export all comments from an Excel file to Word

How to export all comments from an Excel file to Word

Here is the VBA macro which export the comments in the active sheet to a newly created Word file:

Sub extractComments()
    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.Value & vbTab & xComment.Parent.Address & vbTab & xComment.Text
wApp.Selection.TypeParagraph
    Next

Set wApp = Nothing
End Sub

Source. Added cell value into the code above

Ned Sahin

Blogger for 20 years. Former Microsoft Engineer. Author of six books. I love creating helpful content and sharing with the world. Reach me out for any questions or feedback.

Leave a Comment