Cell Comments (97 SR2)

[reimer]

I am using Excel 97 SR2 on an NT system. 800 MHz Compaq with 512 Mb RAM.
I would like to know how to use VBA to consolidate all cell comments found in a worksheet in a blank column of the same worksheet, one comment per cell continuing down until all comments are listed. I read through many posts and did searches on cell comments. I found Post 131532 by grahaml. The thread led me to the VBA code, found at Chip Pearson's site, that will put all the comments into a Word document.
I prefer to have the comments listed down a column to the right of the data. The comment should start with the cell location followed by the actual comment. It is not necessary to include author information.
I can do the code for the correct starting place (including the erasing of old comments), but I am not sure how to convert the macro to put the comments into cells in Excel instead of a Word document.
In reading the posts, I am amazed at how fast and effective Woody

[Dreamboat]

Is it a one-time deal, reimer?

If so, all you'd need to do (sounds like), is select the text after it's in Word, hit Table-Convert text to table, copy/paste into Excel.

[Andrew Cronnolly]

The following code assumes the existence of an author name in the comment, and replaces it with the cell address. This address together with the actual comment text is then recorded in a a column starting at Z1 , which you can alter to meet to your requirements. <pre>Sub ListComments()
Dim oCell As Range
Dim lngCount As Long
lngCount = 0
For Each oCell In Cells.SpecialCells(xlCellTypeComments)
Range("Z1").Offset(lngCount, 0) = _
Replace(oCell.Comment.Text, oCell.Comment.Author & ":" & _
vbLf, oCell.Address & " : ")
lngCount = lngCount + 1
Next
End Sub</pre>

All the above code works on the active worksheet.

Andrew C

[reimer]

I should have been more clear. It is for a co-worker and his style is such that it would not be a one-time, but something he would be updating many times throughout the day and would want the comment list updated.
I have been working on changing the macro that puts the comments in Word and I am finding that it gathers all the coments for the entire Workbook. My preference would be to restrict it to the sheet the macro run from.
Thanks for your fast responce.

[Andrew Cronnolly]

Sorry Reimer,

but too late I noticed you are using XL97, which will cause problems with Replace, as this function is not supported in VBA for office 97. Add the following code as a seperate function, and change the word Replace in the code above to udfReplace.<pre>Private Function udfReplace(strOrigin As String, strFind As String, strReplace As String)
Dim lPos As Long, lPrevPos As Long, iFindLen As Integer
lPos = InStr(1, strOrigin, strFind)
iFindLen = Len(strFind)
lPrevPos = lPos
Do Until lPos = 0
strOrigin = Left(strOrigin, lPos - 1) & strReplace & Right _
(strOrigin, Len(strOrigin) - (lPos - 1 + iFindLen))
lPos = InStr(lPrevPos + Len(strReplace), strOrigin, strFind)
lPrevPos = lPos
Loop
udfReplace = strOrigin
End Function</pre>

Andrew C

[macropod]

Here's an alternative macro that will allow your friend to nominate the range to extract the comments from. As requested, the macro discards the author's name. This macro prompt for the range of cells to extract the comments from and inserts them on the same row, 5 columns to the right of the selection

[reimer]

Many thanks to everybody that took time to reply.
Thanks to:
Dreamboat - that would be a great solution for a one time project.
Andrew Cronnolly - I had trouble getting it to work until I got you second post. Now it works GREAT!
Macropod - It works great -but there are multiple cell comments on the same row. He needs them listed down a column.

Thank you all for the effort. I now have a solution. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

[sjreynolds]

Thanks folks! You've solved my problem, too!!


All I wanted to do was get the cell address of the cells with comments in them. You'd have thought that Microsoft might have put it in the Comments collection or the Comment property (which is the only thing help talks about in relation to comments), but no <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

But if I use that Cells.SpecialCells(xlCellTypeComments) thing, I can get at the row and column of all the comments. Hurrah! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

At this rate, I might have some hair left by the time I've finished!!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>