Retrieving data from cell Comments

RogerB · #1 06-13-2014, 11:37 PM

I have a worksheet with many cell comments, each comment in exactly the same format, (author, date, amount). I now wish to collate all this data into an MS Access database, but cannot find out how to extract the comment data via Visual Basic.

Any help would be much appreciated.

Regards RogerB

BobBridges · #2 06-14-2014, 09:11 AM

I don't fool with comments much, but I did have a program update comments once, long ago. Let's see, here....

Well, it looks like all you have to do is look at the Comment property of a given cell. If there's no comment on that cell, the Comment property is Nothing. If it's not nothing, then you can look at Comment.Text to see what's in it. That's not all there is to comments, of course, but maybe that's all you need to get started.

charlesdh · #3 06-14-2014, 02:06 PM

Hi,

Check this site to see if it helps. If you still need help come on back.

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

macropod · #4 06-16-2014, 03:04 AM

Extracting cell comments is quite simple. For example:

Code:

Sub ExportComments()
  Dim InputRange As Range, StrCmnt as String
  Set InputRange = Application.InputBox (prompt:= "What Range?",Type:=8)
  On Error Resume Next
  For Each cell in InputRange
    StrCmnt = StrCmnt & Cell.Address & ": " & Mid(Cell.Comment.Text, Len(Cell.Comment.Author) + 3) & vbCr
  Next
MsgBox StrCmnt
End sub

This macro will prompt for a range of cells from which to extract the comments. It will then display those comments in a message box, with the cell addresses.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying data from one cell to another where condition is third cell	fairyca	Excel	4	03-30-2014 08:22 AM
Retrieving data from a list or database.	Vic01	Word	14	11-14-2013 12:46 AM
Retrieving deleted data	charles	Outlook	0	12-23-2012 10:58 PM
Copy all comments & cell contents (i.e. data) to word?	IanM	Excel	0	07-03-2010 11:14 PM
Cell Comments in a Protected spreadsheet	dmgerber	Excel	1	06-06-2010 05:10 AM