Results 1 to 8 of 8
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    Before we go the VBA route, why not take a look at the options built into XL?

    First of all, check out the choices in the Tools, Options menu under the View tab. (See picture)

    by choosing Comment & Indicator, you can have the comment displaying all the time. And you can print it off that way

    But perhaps your spreadsheet doesn't lend itself to comments scattered across it.

    Then ... (see next post)
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    Continued from previous.

    Then look under the File, Page Setup menu, and choose the Sheet tab.

    Check out the options for printing comments - see picture.
    By choosing at the end of sheet, they will print out all together - in order of cell reference. - No VBA necessary.

    Cheers
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    fragboy
    Guest

    Extraction of "comments" fields? (Excel 2000 SR1)

    Hi,

    This is my fist message on the Lounge, although I have been a long term reader of Woody's newsletter. I read great things about the Lounge, so here's a curly one (IMHO) that I hope someone will be able to help with.

    We've been sent a spreadsheet which contains a _lot_of cells with comments that are critical for understanding the meaning and context of the content. It's a real pain hovering over each cell to read the comment in each cell, we need a way to extract these into a more useful form (spreadsheet cells or another file perhaps).

    With my limited understanding of Excel / VBA, I can't seem to find anything relevant.

    I do know how to show the comments and get them printed. My fallback is to spend a couple of hours expanding and positioning the notes and then printing multiple copies. I hope someone can save me this time.

    Any ideas folks?

    Thanks,

    fragboy

  4. #4
    fragboy
    Guest

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    Caroline,

    Thanks for the prompt comments. I've tried the option to print the comments at the end and this may be "good enough". I will check with the various people that need to use the document. I suspect that we will still need to extract the comments into a reusable form eventually.

    Thanks again,

    fragboy

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    Try something like this in VBA:

    Dim com As Comment
    Dim i As Long
    For Each com In ActiveSheet.Comments
    i = i + 1
    Sheets("Sheet2").Cells(i, 1) = com.Text
    Sheets("Sheet2").Cells(i, 1) = com.Author
    Next
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    The code below whould find all of the comments in Sheet1 and the cell address where they are found + the comment into column A in Sheet2.

    <pre>Public Sub GetComments()
    Dim I As Long
    Dim oCell As Range
    Dim sComm As String
    I = 0
    For Each oCell In Worksheets("Sheet1").UsedRange
    sComm = ""
    On Error Resume Next
    sComm = oCell.Comment.Text
    On Error GoTo 0
    If sComm <> "" Then
    Worksheets("Sheet2").Range("A1").Offset(I, 0) = oCell.Address & " " & sComm
    I = I + 1
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    fragboy,

    I don't have a solution of my own to offer. I do have some of the responses posted to a very similar question around the beginning of the year - I thought this might be useful so I saved it. It looks a little similar to what was posted in response to you by Legare and Geoff. But my VBA is not all that great to know for sure, so I'll leave it to others to see if there are pieces that could be put together to create a "perfect" (or "better") solution.

    I'm attaching a file that has what I saved. As you can see, Jon's solution could put the comments in Word or Excel.

    If you want the whole thread, just search on the subject in the attachment.

    Hope this helps.

    Fred
    Attached Files Attached Files

  8. #8
    fragboy
    Guest

    Re: Extraction of "comments" fields? (Excel 2000 SR1)

    Thanks to all the clever people who offered replies. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    Your code samples were enough to get the result I was after. I managed to massage the information into quite a usable form.

    I appreciate your help.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Andrew (aka fragboy)

    P.S. All of us here in Oz (or at least everyone I've spoken with today) are deeply shocked and saddened by the tragic terrorist acts in the US.

Posting Permissions

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