Extracting Excel comments to Access table

By scox2500 ·
I am extracting an Excel spreadsheet to an Access dbase table, and the xls has Comment notes that I need to insert into the Access table as part of the import. I am not a programmer.

This conversation is currently closed to new comments.

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

All Answers

Collapse -

One way

by TobiF In reply to Extracting Excel comments ...

It's easier to copy something that is visible in a cell.
You coul'd add an empty column and then write a macro function to reveal the comment in a cell on the same row.

In Excel 2003, do like this:
Press Alt-F11 to open the VBA window.
Menu - Insert - Module

Paste the following text into the module window

Function ShowComment(ThePointer As Range)
ShowComment = ThePointer.Cells(1, 1).Comment.Text
End Function

Now you can close the VBA window. The module will be a hidden attachment to the Excel file.
In the Excel sheet, you can now use your new function. I.e. In order to show the comment from Cell A1, in whatever position you want, you enter the formula =ShowComment(A1)

Note1: This code is NOT fool proof.
Note2: Security barriers in Excel may block this macro after you've saved and reopened the file. With self-signing certificate for VBA, you may mark this macro in such a way, so that the macro will run even if you block other macros.

Collapse -

Separate Comment Line Items

by scox2500 In reply to One way

thank you for your response--it was very helpful. I have another problem:
I need to separate each line item inside the Comment note to a different cell. Can u help me with this? Here is an example of the data in the comment:

DRPV held 6/12/10 (new cell)
DRPV.1 held 7/12/10 (new cell)
DRPV.2 to be held 9/1/10 (new cell)

Collapse -

Can do...

by TobiF In reply to Separate Comment Line Ite ...

Where you write (new cell), do you have any particular character?
I guess you have varying count of rows in different comments, just to crack a neat table design (This typically happens when you try to put in Relational data into a 2-dimensional matrix...)

If you have a specific character to search for, then you can use (I'm referring to Excell 2003, if needed some smart people with mouse-arm will tell us where to find the same things in Office2007)
Menu-Data-"text to columns", indicate "delimited" and enter that special character in the "Other" field. (Unfortunately, you must have one single delimiter character, "DRP" won't do,
UNLESS you do a little trick, select the extracted data, press CTRL+H and replace all occurrences of "DRPV" with "@DRPV".

Now you have your delimiter! :)

You can, of course also write a nice formula for splitting up this, that approach was touched recently in another thread here. "Extract a string from a variable start position"

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

Related Discussions

Related Forums