Extract Comments from EXCEL-sheets

[Stefan]

Good morning everybody!

Does anybody know a (simple) solution to transfer content
from comments back into cells, e.g. in a new column. I
found a solution, however, it is rather complicated: under
page properties one selects in tab table: comments = at
end of page, then select printer = "Microsoft Office Image
Writer", Option "in file". There one can translate the
printout with OCR-recognition, and the resulting file can
be re-imported into EXCEL, where you use cell ID-s to link
apply a lookup-function... There should be way to directly
create an ASCII-file of the comments...

Kind regards,

Stefan

 

[Gord Dibben]

Stefan

Debra Dalgleish provided this code to list comments and cell addresses from
all worksheets on a new worksheet.

Sub ListComms()
Dim cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Comments"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> csh.Name Then
For Each cell In sh.UsedRange
If Not cell.Comment Is Nothing Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Name & " " & cell.Address
.Offset(0, 1).Value = cell.Comment.text
End With
End If
Next cell
End If
Next sh
End Sub

Copy/paste to a general module in your workbook.

If unfamiliar with VBA and macros, see David McRitchie's "getting started"
site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

 

[Jim Rech]

A macro is your best bet. This one is just to get you started. Customize
as needed.

Sub ListActiveSheetComments()
Dim SrcRg As Range
Dim Cell As Range
Dim Counter As Integer
On Error GoTo NoComments
Set SrcRg = Cells.SpecialCells(xlCellTypeComments)
Workbooks.Add xlWorksheet
For Each Cell In SrcRg
Counter = Counter + 1
Cells(Counter, 1).Value = Cell.Address(False, False)
Cells(Counter, 2).Value = Cell.Comment.Text
Next
Exit Sub
NoComments:
MsgBox "No comments found on this worksheet"
End Sub


--
Jim Rech
Excel MVP
| Good morning everybody!
|
| Does anybody know a (simple) solution to transfer content
| from comments back into cells, e.g. in a new column. I
| found a solution, however, it is rather complicated: under
| page properties one selects in tab table: comments = at
| end of page, then select printer = "Microsoft Office Image
| Writer", Option "in file". There one can translate the
| printout with OCR-recognition, and the resulting file can
| be re-imported into EXCEL, where you use cell ID-s to link
| apply a lookup-function... There should be way to directly
| create an ASCII-file of the comments...
|
| Kind regards,
|
| Stefan

 

[Guest]

Thanks for the answer - it helps!

Kind regards, Stefan