Help with require in win32ole

alex-osu3 · November 18, 2006, 4:00pm

Hi all,

I recently play with win32ole library. In order to use it I put a line
at the top of the script

require ‘win32ole’

so that Ruby knows where to find the library. It is my understanding I
should be able to access all the classes/modules within the file. But
this is not the case. In order to get access to ExcelConst I need to add
two more lines

module ExcelConst
end

near the top of the script.

I am a little bit confused. Why is that? Is this only special to
win32ole library or common to many Ruby libraries?

Any comments will be appriciated.

Li

And the follows are the fragment of the script:

require ‘win32ole’

module ExcelConst
end

excel = WIN32OLE.new(“Excel.Application”)
workbook = excel.Workbooks.Add
excelchart = workbook.Charts.Add
…
WIN32OLE.const_load(excel, ExcelConst)
excelchart.ChartType = ExcelConst::XlConeCol

###screen output
C:>irb
irb(main):001:0> require ‘win32ole’
=> true
irb(main):002:0>
irb(main):003:0* module ExcelConst
irb(main):004:1> end
=> nil
irb(main):005:0>
irb(main):006:0* excel = WIN32OLE.new(“Excel.Application”)
=> #WIN32OLE:0x2c268f0
irb(main):007:0> workbook = excel.Workbooks.Add
=> #WIN32OLE:0x2c24154
irb(main):008:0> excelchart = workbook.Charts.Add
=> #WIN32OLE:0x2c21954
irb(main):009:0>
irb(main):010:0* WIN32OLE.const_load(excel, ExcelConst)
=> nil
irb(main):011:0> puts excelchart.ChartType = ExcelConst::XlConeCol
105
=> nil

alex-osu3 · November 19, 2006, 11:42am

On 11/18/06, Li Chen [email protected] wrote:

two more lines

excel = WIN32OLE.new(“Excel.Application”)
workbook = excel.Workbooks.Add
excelchart = workbook.Charts.Add
…
WIN32OLE.const_load(excel, ExcelConst)
excelchart.ChartType = ExcelConst::XlConeCol

You have to define the moduke ExcelConst, because it is not a part of
the library, it is your module that the library will fill with
constants (see the line with WIN32OLE.const_load). You can use any
name for it (i.e. MySuperExcelModuleForConstants). You can even reuse
any existing module for that (including any class).

If you read the documentation you can omit the module name (the second
parameter to WIN32OLE) and the constants will be added to WIN32OLE
itself.

You can add the constants to Kernel module, or Object class so that
you can use them without prefix (and thus polluting the global
namespace, although sometimes it might be handy).

alex-osu3 · November 19, 2006, 4:40pm

Jan S. wrote:

You have to define the moduke ExcelConst, because it is not a part of
the library, it is your module that the library will fill with
constants (see the line with WIN32OLE.const_load).

If it isn’t part of the library of WIN32OLE, then where does it come
from, from the Excel library?

Thanks,

Li

alex-osu3 · November 19, 2006, 5:28pm

On 11/19/06, Li Chen [email protected] wrote:

Jan S. wrote:

You have to define the moduke ExcelConst, because it is not a part of
the library, it is your module that the library will fill with
constants (see the line with WIN32OLE.const_load).

If it isn’t part of the library of WIN32OLE, then where does it come
from, from the Excel library?

Sorry I wasn’t clear enough.

It’s a design decision of WIN32OLE that it doesn’t create the
constants by default. However, it provides the possiblity to create
them on demand. So in the case you need the constants, you say where
you want them, and the library creates them for you. The place where
you want them is the part you have to provide (any module/class).

Obviously the constants come from the OLE object (in this case from
Excel). WIN32OLE reads them and after a bit of translation creates
them in the place you’ve told it.

In conclusion what I wanted to say by that line is that:

The library will work without the ExcelConst module as happily
It’s you who has to create the module if you want the constants
However you can name it anyway you want - e.g. XlConst is as good as
any other name
It’s possible to do without even creating the module by using
WIN32OLE or Kernel

alex-osu3 · November 19, 2006, 7:07pm

In conclusion what I wanted to say by that line is that:

The library will work without the ExcelConst module as happily

It’s you who has to create the module if you want the constants

However you can name it anyway you want - e.g. XlConst is as good as
any other name

It’s possible to do without even creating the module by using
WIN32OLE or Kernel

Hi Jan,

Thank you very much for the explanations.

Could you please give me a small example of “doing it without even
creating the module by using WIN32OLE or Kernel”?

Li

alex-osu3 · November 20, 2006, 2:47pm

On 11/19/06, Li Chen [email protected] wrote:

Thank you very much for the explanations.

Could you please give me a small example of “doing it without even
creating the module by using WIN32OLE or Kernel”?

(not tested: I’ve made up this from documentation, the change is in
the last two lines)

require ‘win32ole’

excel = WIN32OLE.new(“Excel.Application”)
workbook = excel.Workbooks.Add
excelchart = workbook.Charts.Add
…
WIN32OLE.const_load(excel) # missing …, ExcelConst)
excelchart.ChartType = WIN32OLE::XlConeCol # ExcelConst → WIN32OLE

alex-osu3 · November 30, 2006, 10:19am

Hey

I just tried requiring ‘win32ole’ in my Linux console but it doesn’t
seem to find it. I’m running MS Office under Linux with Crossover
Office, and was hoping I’d be able to do my MS Office development under
Linux from now on.

Would getting the win32ole libraries help at all?

Is there something small I’m missing or is this simply too ambitious?

Thanks in advance
Gustav P.
[email protected]

alex-osu3 · November 30, 2006, 10:51am

Gustav P. wrote:

Hey

I just tried requiring ‘win32ole’ in my Linux console but it doesn’t
seem to find it. I’m running MS Office under Linux with Crossover
Office, and was hoping I’d be able to do my MS Office development under
Linux from now on.

Would getting the win32ole libraries help at all?

Is there something small I’m missing or is this simply too ambitious?

Thanks in advance
Gustav P.
[email protected]

As far as I know WIN32OLE is a built-in library in the Ruby. In order to
use it you need to put this line

require ‘wine32ole’

near the top of your script.

Li

alex-osu3 · November 30, 2006, 11:21am

On 11/30/06, Gustav P. [email protected] wrote:

Would getting the win32ole libraries help at all?
use it you need to put this line
Thanks for the reply. I tried it in irb, but I get

Directories, which I can’t find under
/usr/lib/ruby/1.8

Might it be necessary to download the win32ole library?
Is there one for Linux?

Thanks, I’d really love to be able to develop from Linux, but if all
else fails, I suppose I’ll make do with Windows :]

The problem is, that win32ole is a windows extension, you have linux
ruby and office running under wine. So you have to make them talk to
each other. You need to somehow compile the win32ole extension under
linux to use wine to talk to msoffice. I would say that:

it should be possible
many people will thank you for that
certainly it is not easy - you’d need to know a bit of ruby
internals, a bit of wine, a bit of OLE and you’d need to write C code
during the process.

alex-osu3 · November 30, 2006, 11:35am

Hi

Thanks, I can see why people would be thankful!
I’ll give it a shot, can’t be that difficult ;]

I had a vague inkling that it wouldn’t be simple,
Thanks for the reply!

Gustav

alex-osu3 · November 30, 2006, 11:07am

Li Chen wrote:

require ‘wine32ole’

near the top of your script.

Li

Hi

Thanks for the reply. I tried it in irb, but I get

irb(main):001:0> require ‘win32ole’
LoadError: no such file to load – win32ole
from (irb):1:in `require’
from (irb):1

I tried ‘wine32ole’ as well, same problem.
I noticed that on my Windows machine I’ve got
c:\ruby\lib\ruby\1.8\win32
c:\ruby\lib\ruby\1.8\win32ole

Directories, which I can’t find under
/usr/lib/ruby/1.8

Might it be necessary to download the win32ole library?
Is there one for Linux?

Thanks, I’d really love to be able to develop from Linux, but if all
else fails, I suppose I’ll make do with Windows :]

Gustav

alex-osu3 · November 30, 2006, 3:58pm

On Thu, 30 Nov 2006, Li Chen wrote:

Is there something small I’m missing or is this simply too ambitious?

near the top of your script.

i’ve managed this before. i installed the one-click installer under
crossover
office and the used that. another option is installing msys, compiling
ruby
under that, and then using that ruby. in any case you need to
install/build
another ruby under the crossover environment.

regards.

-a

alex-osu3 · December 6, 2006, 12:55pm

Gustav P. wrote:

internals, a bit of wine, a bit of OLE and you’d need to write C code

I bailed on the 1-2-3, as I decided to try some easier sounding ways
first, one of which turned out to work!

In the end I installed office 2000 with Crossover, then I used the
windows installer to install windows ruby into the same bottle as
office. Finally I put the following bash script in my /usr/bin directory
so I can run my ruby files with ‘#> wruby some_script.rb’

The script is as follows, I’ve never done bash scripting before, so I
think I cheated by using the ‘env ruby’ part, but hey, why code bash
when you can code ruby?

==/usr/bin/wruby

#!/usr/bin/env ruby
/opt/cxofficebeta/bin/wine --bottle myBottle --cx-app ruby.exe #{ARGV.join(" ")}

In one instance, I have a .rb file that opens a xls file, grabs some
data from it, opens another file, puts the collected data in it, then
finally shows the last file, post-modifications, to the user.

Its working beautifully thus far. As crossover maps the linux root (’/’)
to z:\ you can have your xls files anywhere on your disk and reference
it accordingly.

In the end, getting it working this way was quite easy, though my
crossover didn’t like Excel 2003. Works perfectly with Excel 2000
though, macros record and run perfectly, I’ve got lots of VBA code in
one of the xls files, and it works without flaw. When I tried installing
Office 2000 premium with the crossover installer, it kept breaking, so I
selected the ‘install unsupported software’ option and let it create a
new bottle (win98) for office, this worked perfectly and I followed it
with the windows ruby installer (which took ages but executed without
error), which I installed into the same bottle as office.

Anyway, just thought I’d share this in case someone else out there felt
similarly irritated by having to boot in windows every now and then and
wished there was an alternative.

Cheery-o
Gustav P.