How to export comments from Excel?

Factual Questions
1

Is there any way to export or extract the comments in Excel as their own column of information? i.e. if I have 1000 cells with comments on them, and I want the comments to appear in their own column, am I just stuck cutting and pasting 1000 times?

I tried saving as csv and tab-delimited text, and opening in Access, but it loses the comments.

2

Know any VBA?

You can pretty easily iterate through all the comments on a sheet with something like (tested in Excel 200):



    Dim x As Comment
    
    For Each x In Worksheets("Sheet1").Comments
    
        MsgBox x.text
    
    Next

If this makes sense to you I can expand on it.

3

Makes sense–instead of the message box, though, how could I get the output into a separate column to line up with the corresponding cell?

I have it iterating through the comments now to come up with a separate comments list.

However, when I try to iterate through the rows and paste the comment next to it, it chokes on cells w/no comment (because Selection.Comment on empty comment cells does not return null and I’m not sure how to otherwise exclude cells without comments)

Or is there a way to retrieve the row and column ID from the Comment object?

4

This is rather crude but you get the idea:




    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    Dim NumRows As Long
    NumRows = ws.Rows.Count 'watch this value it can get quite large and make this loop drag on for a long time

    Dim c As Comment
    
    For i = 1 To NumRows
        Set c = ws.Cells(i, 1).Comment
        If c Is Nothing Then
            'do absolutely nothing
        Else
            ws.Cells(i, 2).Value = c.Text
        End If
    Next
5

This seems to work in Excel 2002 - the Parent of a Comment is really a range object (the no-help files are unclear about this, but it seems to be so), so you can get the row and column properties of the parent, or you can do as follows:

Sub ShowComments()
Dim aComment As Comment, aRange As Range

    For Each aComment In Worksheets("Sheet1").Comments
            Set aRange = aComment.Parent
            aRange.Offset(columnoffset:=1).Value = aComment.Text
    Next

End Sub

6

Simulpost!

7

Civil Guy, much more elegent.

8

Thank you very much!