# How to export comments from Excel?

**URL:** <https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206>\
**Category:** Factual Questions\
**Created:** [June 6, 2005, 8:32pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206 "2005-06-06T20:32:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![tiltypig](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tiltypig](https://boards.straightdope.com/u/tiltypig)\
**Post date:** [June 6, 2005, 8:32pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/1 "2005-06-06T20:32:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![larsenmtl](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@larsenmtl](https://boards.straightdope.com/u/larsenmtl)\
**Post date:** [June 6, 2005, 8:51pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/2 "2005-06-06T20:51:22Z")

</div>

> [@tiltypig](#):
>
> extract the comments in Excel as their own column of information?

Know any VBA?

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

```auto

    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.

---

<div class="post-metadata">

**Author:** ![tiltypig](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tiltypig](https://boards.straightdope.com/u/tiltypig)\
**Post date:** [June 6, 2005, 10:11pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/3 "2005-06-06T22:11:00Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![larsenmtl](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@larsenmtl](https://boards.straightdope.com/u/larsenmtl)\
**Post date:** [June 7, 2005, 3:18am UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/4 "2005-06-07T03:18:24Z")

</div>

This is rather crude but you get the idea:

```auto

    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

```

---

<div class="post-metadata">

**Author:** ![Civil\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Civil\_Guy](https://boards.straightdope.com/u/Civil_Guy)\
**Post date:** [June 7, 2005, 3:43am UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/5 "2005-06-07T03:43:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Civil\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Civil\_Guy](https://boards.straightdope.com/u/Civil_Guy)\
**Post date:** [June 7, 2005, 3:45am UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/6 "2005-06-07T03:45:42Z")

</div>

Simulpost!

---

<div class="post-metadata">

**Author:** ![larsenmtl](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@larsenmtl](https://boards.straightdope.com/u/larsenmtl)\
**Post date:** [June 7, 2005, 1:08pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/7 "2005-06-07T13:08:15Z")

</div>

Civil Guy, much more elegent.

---

<div class="post-metadata">

**Author:** ![tiltypig](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tiltypig](https://boards.straightdope.com/u/tiltypig)\
**Post date:** [June 7, 2005, 5:26pm UTC](https://boards.straightdope.com/t/how-to-export-comments-from-excel/307206/8 "2005-06-07T17:26:10Z")

</div>

Thank you very much!