importing an excel spreadsheet with comment in the cells

  • Thread starter Thread starter Craig M via AccessMonster.com
  • Start date Start date
C

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?
 
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.
 
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


John said:
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
Click to expand...
[quoted text clipped - 3 lines]
Can i export them to a database, text file or whatever and then import them
to access?
Click to expand...
Click to expand...
 
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


John said:
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
Click to expand...
[quoted text clipped - 3 lines]
Can i export them to a database, text file or whatever and then import them
to access?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top