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]

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: [url]http://www.rubygarden.org/ruby?[/url]
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://
[url]www.cpearson.com/excel/cells.htm[/url]

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]

"Bernhard Leicher" <bernhard.leicher@t-online.de> wrote:

>
> # 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

[Berger, Daniel]

> -----Original Message-----

> From: dhtapp [mailto:dhtapp@cox.net]
> Sent: Sunday, November 16, 2003 6:21 PM
> To: [email]ruby-talk@ruby-lang.org[/email]
> Subject: Scrounging for WIN32OLE <--> MS-Excel Examples

> Hi,
>
> 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?

<snip>

>
> 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.

Hi Dan,

Are you referring to my "spreadsheet" package? Last I checked it worked
fine under 1.8.x, though it's possible I've used deprecated methods
somewhere. I'll double check this week.

My package isn't broken, but it does have some limitations. The big two
are a lack of support for formulas, and the inability to create
workbooks over 7 MB. Also, my package is "write only", so if you need
to read data back out the spreadsheet, you're out of luck. However,
none of this sounds like an issue in your case.

If you do happen to find any bugs, please submit them to the SourceForge
site at [url]http://rubyspreadsheet.sourceforge.net/[/url].

Regards,

Dan