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!


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.


David Mullet said...


"I think I can automate this task now."

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

(Despite what they say about the Hokey Pokey.)