What is the best method to extract data from Comment Box

[Charlotte]

Is there an easy way to do a 'Select all' on a comment box? I've
inherited a spreadsheet that has many comment boxes that contain pages
and pages of text in each box. I want to move the data contained in
these comment boxes to an Access memo field. What is the best way to
extract this data from the comment box? I'll be fine with manually
doing a cut/paste, but 'CTRL-A' for 'Select All' doesn't seem to work
with these comment boxes.

 

[Gord Dibben]

How much is "pages and pages"?

I think you are limited in Access to 255 characters but not sure.

You can use a Debra Dalgeish macro to copy the Comment text to an adjacent
cell and import those cells into Access.

http://www.contextures.com/xlcomments03.html#CopyAdjacent


Gord Dibben MS Excel MVP

 

[ShaneDevenshire]

Hi Charlotte,

If you click inside the comment and press Ctrl+Home and then Shift+Ctrl+End
you get all the text.

Here is a macro you can run to take all the comments in your spreadsheet and
put them into cell in column A of a sheet named Sheet1. If may not help you
but...
Comment often contain hard carrage returns so check the cells to see if the
are really empty. You may also want to manually set the row height for Sheet1
before you run the macro.

Sub GetComments()
Dim cell As Range
Dim ws As Worksheet
Dim I As Long, Y As String
I = 1
On Error Resume Next
For Each ws In Worksheets
ws.Activate
Selection.SpecialCells(xlCellTypeComments).Select
MsgBox Err.Number
If Err.Number = 0 Then
For Each cell In Selection
Sheets("Sheet1").Cells(I, 1) = cell.Comment.Text
Y = cell.Comment.Text
I = I + 1
Next cell
Else
Err.Clear
End If
Next ws
End Sub

 

[Still Learning]

I have tried this Debra macro and can't get it to work. It keeps telling me
"no comments found" but there is one there. what am I missing? I have typed
the macro exactly as it is listed.

Thanks.

 

[MartinW]

Hi,

I'm not sure what you are trying to do but you can right-click
on the cell that contains the comment and select 'Edit Comment'
then highlight the text in the comment box and copy and paste it to
wherever you need it. Since you are talking about macros you
may also be talking about batch copying of the comments.

If that is the case then ignore this post, but you haven't given
a great deal of detail here.

Cheers
Martin

 

[Don Guillett]

Merry Xmas.
I have just tested that macro successfully. Instead of typing it>COPY/paste
it. Try again