Can a "Comment" attached to a Cell Be Exported ?

[Tony Brown]

A friend has given me a spreadsheet with comments attached to certain cells.
I'd like to export the file into access but cant find a way to inclued the
comments

Any Suggestions

T on Y



x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 800 Gigs/Day - $8.95/Month
x-- UNLIMITED DOWNLOAD

 

[Debra Dalgleish]

You could copy the comment text to a new worksheet, and import that
sheet into Access.

'===================================
Sub ShowComments()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curWks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curWks = ActiveSheet

On Error Resume Next
Set commrange = curWks.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

Set newwks = Worksheets.Add

newwks.Range("A11").Value = _
Array("Address", "Name", "Value", "Comment")
newwks.Name = curWks.Name & " Comments"

i = 1
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
.Cells(i, 2).Value = mycell.Name.Name
.Cells(i, 3).Value = mycell.Value
.Cells(i, 4).Value = mycell.Comment.text
End With
Next mycell

Application.ScreenUpdating = True

End Sub
'=======================================