importing an excel spreadsheet with comment in the cells

[Craig M via AccessMonster.com]

I have a spreadsheet that has comments in several cells
How do you import the comments? It just seems to not even see they are back
there.
I can edit them in excel but they dont seem to be transfered when i import
them to access
Can i export them to a database, text file or whatever and then import them
to access?

 

[John Nurick]

Hi Craig,

Access tables have nothing corresponding to Excel's cell comments, and
as you've found they are just ignored when importing. You'll need to
paste the comments into a column of their own in the Excel sheet, and
import this along with the rest of the data.

If there are a lot of comments, it's fairly simple to write Excel VBA
code to pull them into a column of cells.

 

[Craig M via AccessMonster.com]

at least I can get the data
because the spread sheet is full of comments
Just not sure how i can link a cell to a particular comment that I will
extract and paste into a new cell

Thanks for your insight and help

Hi Craig,

Access tables have nothing corresponding to Excel's cell comments, and
as you've found they are just ignored when importing. You'll need to
paste the comments into a column of their own in the Excel sheet, and
import this along with the rest of the data.

If there are a lot of comments, it's fairly simple to write Excel VBA
code to pull them into a column of cells.

I have a spreadsheet that has comments in several cells
How do you import the comments? It just seems to not even see they are back

[quoted text clipped - 3 lines]

Can i export them to a database, text file or whatever and then import them
to access?

 

[John Nurick]

I've used this Excel VBA function to extract the text of comments:

Public Function GetComment(C As Excel.Range) As String
'If the first cell in C has a comment, returns
'the .Text. Otherwise returns empty string.
GetComment = vbNullString
On Error Resume Next
GetComment = C.Comment.Text
On Error GoTo 0
Err.Clear
End Function

You can use it as a worksheet function, although it doesn't recalculate
automatically. For instance, this formula returns the text of any
comment in cell A2:

=GetComment(A2)

You'll find that any line breaks in the comment show up as square boxes
in the Excel cell; but I think they'll look right in an Access textbox.

at least I can get the data
because the spread sheet is full of comments
Just not sure how i can link a cell to a particular comment that I will
extract and paste into a new cell

Thanks for your insight and help

Hi Craig,

Access tables have nothing corresponding to Excel's cell comments, and
as you've found they are just ignored when importing. You'll need to
paste the comments into a column of their own in the Excel sheet, and
import this along with the rest of the data.

If there are a lot of comments, it's fairly simple to write Excel VBA
code to pull them into a column of cells.

I have a spreadsheet that has comments in several cells
How do you import the comments? It just seems to not even see they are back

[quoted text clipped - 3 lines]

Can i export them to a database, text file or whatever and then import them
to access?

 

[Craig M via AccessMonster.com]

Worked sweet, Thanks

 

[John Nurick]

Thanks for the feedback.