Scrounging for WIN32OLE <--> MS-Excel Examples

[dhtapp]

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.

 

[Bernhard Leicher]

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://
www.cpearson.com/excel/cells.htm

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

 

[daz]

# this should also be possible
sheet.cells(4,1).value=7

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


daz