Monday, May 11, 2009

Ruby & Excel: Extracting Cell Comments

Someone recently asked how to get the comment text from a particular cell in an Excel worksheet.

You could call the cell's Comment property to obtain a reference to the Comment object, then use the Comment object's Text property to get---or set---the text of the comment.

Imagine that you have a workbook open and you want to get the comment text from cell B2 in the currently selected worksheet. The following code should do the trick:


require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet
cell = ws.Range('B2')
comment = cell.Comment
text = comment.Text

Do you---or would you like to---use comments in your worksheets? I can go into further detail about the Comment object and the Comments collection, but that's all we have time for today.

Thanks for stopping by!

2 comments:

Dave said...

This is very intereting. I have an excel sheet with 300 rows and 5 columns. Each column has data I need to paste into a Content management systems form fields.

I think I can automate this task now.

Dave

David Mullet said...

@Dave-

"I think I can automate this task now."

Great! That's what it's all about.

(Despite what they say about the Hokey Pokey.)

David