Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA (Excel 2002)

    The following code comes from the Lounge. Just wondering how one could modify it so comments from a certain column or two would be copied to Word. In a sheet I am working on I have comments in column A and column C. I want only the comments from column C to copied to a Word document. I believe that the change would happen in the For Each cmt section. However I don't know enough about VBA to select a certain column (if possible)

    Thanks.
    Sub CopyCommentsToWord()

    Dim cmt As Comment
    Dim WdApp As Object

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

    With WdApp
    .Visible = True
    .Documents.Add DocumentType:=0

    For Each cmt In ActiveSheet.Comments
    .Selection.TypeText cmt.Parent.Address _
    & vbTab & cmt.Text
    .Selection.TypeParagraph
    Next
    End With

    Set WdApp = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel 2002)

    Change the For ... Next loop to the following:

    For Each cmt In ActiveSheet.Comments
    ' Test if the cell containing the comment is in column C
    If Not Intersect(cmt.Parent, Range("C:C")) Is Nothing Then
    .Selection.TypeText cmt.Parent.Address _
    & vbTab & cmt.Text
    .Selection.TypeParagraph
    End If
    Next

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2002)

    Thanks Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •