Results 1 to 4 of 4
  1. #1
    rachel
    Guest

    Manipulating cell comments

    I think that there was recently an article on this subject in WOW, but I couldn't find it....

    The problem is that I have included a number of cell comments in a spreadsheet that I have used to tabulate the results of a survey. (Main responses a,b,c went into the cells and people's little side notes went into the comments, so that they'd be out of the way.) Now I want to be able to move the comments into a Word document in an orderly way. For instance, taking all the comments that were in row 10, and making each one a paragraph. Even better might be if I could simply move them into a row of cells of their own, so that I could manipulate them with the other data.

    The only thing that I could think of was printing the spreadsheet to a file, with the comments printing at the end. Any more elegant solutions?

    Excel 97, btw.

    THANKS!

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating cell comments

    The following macro will look at all cells on a worksheet, and put the comment text into a new word document and save that to the current directory as Test.doc.

    Sub test()
    Dim s As Range
    Dim w As Word.Document

    Set w = New Word.Document

    For Each s In ActiveSheet.UsedRange.Cells
    If Not s.Comment Is Nothing Then
    w.Paragraphs.Add
    w.Paragraphs(w.Paragraphs.Count).Range = s.Comment.Text
    End If
    Next
    w.SaveAs "test.doc"
    w.Close
    Set w = Nothing
    End Sub

    As I have no idea about your comfort level with macros, please yell if you have no idea what this is doing, or how to use it.

    Jon

    PS You need to include a reference to the Microsoft Word Object library in your module to use this (same disclaimer as above).

  3. #3
    rachel
    Guest

    Re: Manipulating cell comments

    Thanks! The regrettable truth is that I am not at all comfortable with macros (or including references to a Word Object in my module, whatever that means). I don't think I have written one since I switched from Lotus 123 8 yers ago.
    So, any instructions on using this would be appreciated, but above the call of duty. I can presumably find that in a manual. But if it is easy to just tell me what to do to make it work, that would be pleasantly expeditious.

    BTW,
    a) does this macro go through by column or by row?
    does it insert the cell address in frot of each comment?

    Risking being greedy, would it be easier to just make a function based on a bit of this macro that simply "copied" any given comment into a different cell?

    Again, Thanks!

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating cell comments

    Ok. Instructions on how to use this we can do fairly easily:
    - go to Tools>Macro>Macros
    - Type a name like ReadComments, and press create
    - You will be cast into the Visual Basic Editor, and you'll get something that looks like
    Sub ReadComments()
    End Sub

    If you want to use the code I came up with before, paste it (without the starting sub and ending end sub lines) into the macro you have just created. The final step (to reference Word) is to go to Tools>References (from the Visual Basic Editor), and scroll down the list until you find Microsoft Word Object Library (it might have a version number like 8.0 in it) and click that on and say ok.

    If you want to put the cell comments into other cells, the steps above are the same (except you don't need Word), and the code is something like the following:

    Sub ReadComments()
    Dim s As Range
    For Each s In ActiveSheet.UsedRange.Cells
    If Not s.Comment Is Nothing Then
    s.Offset(0, 1) = s.Comment.Text
    End If
    Next
    End Sub

    This will put the comment text in the cell to the right of the cell with the comment (does that make sense?).

    In answer to your other questions:
    a) this goes through row by row
    No. You would need to change the version that writes to Word to:

    sub ReadComments()
    Dim s As Range
    Dim w As Word.Document

    Set w = New Word.Document

    For Each s In ActiveSheet.UsedRange.Cells
    If Not s.Comment Is Nothing Then
    w.Paragraphs.Add
    w.Paragraphs(w.Paragraphs.Count).Range = s.address & " " & s.Comment.Text
    End If
    Next
    w.SaveAs "test.doc"
    w.Close
    Set w = Nothing
    End Sub

    That may be a bit of macro overload for somebody who is new to them. Let me know if I got a bit carried away.

    Jon

Posting Permissions

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