Exporting Comments

[Rebecca]

Hello,

I have a large Excel spreadsheet with many comments
throughout it. I would like to export this spreadsheet to
an Access database for better management. I was wondering
though if there is a way to export the comments as well
from the spreadsheet into Access. I have tried just doing
a regular import of the file from Access, but the comments
are not brought over. I am using Excel 2000. Any
assistance you can offer would be greatly appreciated.
Thanks in advance for your time!

 

[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
'=======================================