Exporting Excel Comments in to Access tables

  • Thread starter Thread starter Ian Millward
  • Start date Start date
I

Ian Millward

I want to combine several Excel spreadsheets into an Access DB. The
Spreadsheets have "Comments" dotted about all over the place. I want to copy
them into a single field which I would then like to export as an Access
"Memo" field. I am stuck on turning the Comments content into Text in the
Excel cell. I believe it is something to do with the Comments.Text being
both a property and a method.



Anybody give me a hint?



Ian Millward

Edinburgh
 
Ian Millward said:
I want to combine several Excel spreadsheets into an Access DB. The
Spreadsheets have "Comments" dotted about all over the place. I want to copy
them into a single field which I would then like to export as an Access
"Memo" field. I am stuck on turning the Comments content into Text in the
Excel cell. I believe it is something to do with the Comments.Text being
both a property and a method.
Click to expand...

I see no way to import them into a single field in Access unless they are in
a single column in Excel. I think you are going to have to copy and paste
the comments to a single Excel column. Perhaps there is some code that can
find them in Excel and write them to a column. A visit to the Excel
newsgroups may provide you with more information.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin,

<< I see no way to import them into a single field in Access unless they are
in
a single column in Excel. >>

That's the bit I'm stuck on. How to turn the Comment into text and put it in
a new single column called "Comments" which I will then export as a "Memo"
type field.

Thanks anyway
 
You might want to try this (adapted) code that I found in Excels help:

*******************************************************
Sub Comments2Rows()
Set cmt = Worksheets(1).Comments 'name of worksheet
rowcounter = 1
For Each c In cmt
c.Visible = True
Cells(rowcounter, 2).Value = c.Text '2 = column where you want result posted. 1=A, 2=B, ...
rowcounter = rowcounter + 1
Next
End Sub
*****************************************

hope this helps,
Jacques.
 
Back
Top