Importing inserted comments from Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am currently considering setting up an Access database to more efficiently
manage several spreadsheets of contacts.

However some of the contacts' data in the Excel spreadsheet (I understand
that Excel data can be imported into Access), includes "inserted comments".
Before I begin setting up a database, could anybody tell me if is possible to
also import the hundreds of inserted comments?!

Many thanks in advance for any help, Laura
 
Hi Laura,

You can't import them directly. One approach is to add a column to the Excel
sheet and fill it with formulas that use a custom worksheet function to
retrieve comments from cells in another column. Here's a function I've used
to do this for one-off import tasks. The call to Replace() changes the line
breaks used in Excel comments to ones that will work in an Access field but
show up as two boxes in an Excel cell.

Public Function GetCellComment(R As Range) As String
On Error Resume Next
GetCellComment = Replace(R.Cells(1).Comment.Text, vbLf, vbCrLf)
If Err.Number <> 0 Then GetCellComment = ""
On Error GoTo 0
End Function
 
Back
Top