General discussion

Locked

Access import Excel cell notes/comments

By bradsq&a ·
I am migrating a large Excel97 spreadsheet 15K records to Access97. The spreadsheet has notes/comments throughout the sheet in various cells. Apparently, the user found it easier to insert comments for a given cell rather that create a comment field/column. How can I migrate the inserted notes with the spreadsheet cell data?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access import Excel cell notes/comments

by DKlippert In reply to Access import Excel cell ...

I don't have a quick solution, but here are a couple of code snippets you may be able to massage.
The first pcopies the comments to other columns. The second writes to a text file.

Sub CopyComments()
For Each c In ActiveSheet.Comments
i = i + 1
Cells(i, "F").Value = c.Parent.Row
Cells(i, "G").Value = c.Parent.Column
Cells(i, "H").Value = c.Text
Next c
End Sub

or
---Posted 1 July 1999 by N.Hodge----
Create a text file on your desktop, (or change the reference in the code), calledtest.txt and run the code below.
This will write each comment, on all worksheets, with it's address and sheet no. to the txt file and close it. If you want it to strip off the user name, you'll have to write some code to do that.

Sub writeComments()
Dim mycomment As Comment
Dim mySht As Worksheet
Open "C:\Windows\Desktop\Test.txt" For Output As #1
For Each mySht In Worksheets
For Each mycomment In Worksheets(mySht.Name).Comments
Print #1, "From " & mycomment.Parent.Parent.Name _
& mycomment.Parent.Address _
& " Comes the comment: " _
& mycomment.Text
Next mycomment
Next mySht
Close #1
End Sub
--
written by:
Nick Hodge
Southampton, England
nick_hodge@lineone.net

Collapse -

Access import Excel cell notes/comments

by bradsq&a In reply to Access import Excel cell ...

The suggestions do as coded and with some work, specific to the task, should do the job. Thanks!

Collapse -

Access import Excel cell notes/comments

by bradsq&a In reply to Access import Excel cell ...

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums