Thursday, March 29, 2012

Printing Comments To Word

This macro will print all of the cell comments to Microsoft Word.  The Word application will remain
open and active.  You may then save or print the cell comment document.    Make sure that
you have enabled references to Word objects, from the Tools->References menu.

Public Sub PrintCellComments()

Dim Cmt As String
Dim C As Range
Dim I As Integer
Dim WordObj As Object
Dim ws As Worksheet
Dim PrintValue As Boolean
Dim res As Integer
On Error Resume Next
Err.Number = 0

res = MsgBox("Do want to print cell values with comments?", _
    vbYesNoCancel + vbQuestion, "Print Cell Comments")
Select Case res
    Case vbCancel
        Exit Sub
    Case vbYes
        PrintValue = True
    Case Else
        PrintValue = False
End Select

Set WordObj = GetObject(, "Word.Application")
If Err.Number = 429 Then
    Set WordObj = CreateObject("Word.Application")
    Err.Number = 0
End If

WordObj.Visible = True
WordObj.Documents.Add
With WordObj.Selection
.TypeText Text:="Cell Comments In Workbook: " + ActiveWorkbook.Name
.TypeParagraph
.TypeText Text:="Date: " + Format(Now(), "dd-mmm-yy hh:mm")
.TypeParagraph
.TypeParagraph
End With

For Each ws In Worksheets
    For I = 1 To ws.Comments.Count
        Set C = ws.Comments(I).Parent
        Cmt = ws.Comments(I).Text
        With WordObj.Selection
        .TypeText Text:="Comment In Cell: " + _
            C.Address(False, False, xlA1) + " on sheet: " + ws.Name
        If PrintValue = True Then
            .TypeText Text:=" Cell Value: " + Format(C.Value)
        End If
        .TypeParagraph
        .TypeText Text:=Cmt
        .TypeParagraph
        .TypeParagraph
        End With
    Next I
Next ws

Set WordObj = Nothing
MsgBox "Finished Printing Comments To Word", vbInformation, _
    "PrintCellComments"

End Sub

No comments:

Post a Comment