Exporting Excel Comments in to Access tables
 

I want to combine several Excel spreadsheets into an Access DB. The
Spreadsheets have "Comments" dotted about all over the place. I want to copy
them into a single field which I would then like to export as an Access
"Memo" field. I am stuck on turning the Comments content into Text in the
Excel cell. I believe it is something to do with the Comments.Text being
both a property and a method. Anybody give me a hint?



Ian Millward

Edinburgh

Exporting Excel Comments in to Access tables
 

An example, putting the comment text in column J:

Sub ExtractComments()
Dim Cell As Range
On Error GoTo EndThis
For Each Cell In Range("A1:A10").SpecialCells(xlCellTypeComments)
Cell.Offset(0, 9).Value = Cell.Comment.Text
Next
Exit Sub
EndThis:
MsgBox "No comments"
End Sub

I'm not sure what you're going to do if more than one column has comments.

--
Jim Rech
Excel MVP