Excel Comments

M

Matthew

How would I go about taking the comments that are in an Excel document and
putting them in their own cell as the text of that cell. The reason I ask is
because I want to import an Excel worksheet into Access and the document is
full of comments that I would also like in the database. This is the only way
I can think of to do this. If you can help me that would be great.

Thank you,
Matthew
 
H

Harlan Grove

Matthew wrote...
How would I go about taking the comments that are in an Excel document and
putting them in their own cell as the text of that cell. The reason I ask is
because I want to import an Excel worksheet into Access and the document is
full of comments that I would also like in the database. This is the only way
I can think of to do this. If you can help me that would be great.
Click to expand...

It requires VBA. Most flexible using user-defined functions.

Press [Alt]+[F11] to bring up the Visual Basic Editor (VBE). Run the
menu command Insert > Module. That'll open a new, blank code window in
the VBE. Enter the following into it.

'-- begin VBA ------
Option Explicit

Function ct( _
r As Range, _
Optional s As Boolean = True _
) As String
'-------------------------------
Dim p As Long
Set r = r.Areas(1).Cells(1, 1)
If r.NoteText <> "" Then
ct = r.Comment.Text
If s Then
p = InStr(1, ct, ":" & vbLf)
If p > 0 Then ct = Mid(ct, p + 2)
End If
End If
End Function
'-- end VBA ------

The formula =ct(A1) will return cell A1's comment text if any or a zero
length string, "", if A1 has no comment. You can put such formulas
anywhere you want, which is what makes this approach most flexible.

The optional second parameter specifies whether or not to include the
typical comment header of user name followed by colon followed by
newline. I wouldn't want it myself, so the udf above defaults to True.
Change it to False if you want comment headers by default.
 
Top