Scrounging for WIN32OLE <--> MS-Excel Examples

ruby-talk
1

Hi,

I’ve suddenly developed an interest in populating an Excel worksheet from
within a little simulation I’m running.

I’ve Googled for docs & examples on using the WIN32OLE class, and been able
to cobble together some basic functionality with the help of what I’ve found
(plus Dave & Andy’s book), but I haven’t been able to branch out much.

I’ve also tried recording a couple of basic Excel macros and examining the
output. Unfortunately, the number of hours I’ve spent in VB can be counted
on a dog’s left paw, and I’m not having much luck translating the syntax
into stuff that WIN32OLE recognizes.

And, I’ve perused Microsoft’s tech site. For instance, they list multiple
ways that Excel ranges can be accessed, including a simple 2d index (i, j),
which would be perfect. But so far, I can only get the standard
"A1:A5"-style notation to work.

Does anyone know of some docs or guidelines posted somewhere that I may have
missed?

Thanks,

  • dan

PS I know there’s a specialized Excel library to write worksheets directly,
but that appears to be semi-broken under 1.8, and I’m not nearly experienced
enough to know how to patch it yet.

2

In vQErb.11593$7B2.11385@fed1read04 dhtapp wrote:

I’ve suddenly developed an interest in populating an Excel worksheet
from within a little simulation I’m running.

Does anyone know of some docs or guidelines posted somewhere that I
may have missed?

Hi,

there’s a page on the Rubygarden Wiki: http://www.rubygarden.org/ruby?
ScriptingExcel
(maybe I’ll find the time to add to the page what I learned so far)

A great resource for general and programming Excel knowhow is Chip
Pearson’s site, for addressing cells in a range, take a look at http://

Some examples of addressing cells by index:

supposed that “sheet” contains a reference to an Excel worksheet

sheet.range(“a1”).item(2,1).value=5 # 5 goes to B1

or get cell A1 first

range = sheet.range(“a1”)

and use that from now on

range.item(3,1).value=6

this should also be possible

sheet.cells(4,1).value=7

Parameters are in the order: rowindex, columnindex.

In VBA you can omit item and value, as they are somehow default
properties:
VBA: range(“a1”)(3,1)=5
Ruby: range(“a1”).item(3,1).value=5

Regards, Bernhard

3

Not my question, Bernhard, but thanks for the reply.

I spent some time looking at the problem (thinking that
it should be fairly simple) and got nowhere.

Now …

Multiplication Table without alphanumeric cell references:

require ‘win32ole’
excel = WIN32OLE.new(“Excel.application”)
excel.visible = true
excel.Workbooks.Add()

for row in (1…12)
for col in (1…12)
excel.cells(row, col).value = row * col
end
end

OK, it was easy - with the right help :slight_smile:

daz

···

“Bernhard Leicher” bernhard.leicher@t-online.de wrote:

this should also be possible

sheet.cells(4,1).value=7