Win32ole and excel

Hi,

I’m trying to open a file using win32ole in excel:

@app = WIN32OLE.new("excel.application")
@app.visible = true
template = "racetemplate.xls"
@book = @app.Workbooks.Open(template)
@sheet = @book.Worksheets(1)

results in the following error:

(druby://127.0.0.1:9001) ./excelmaker.rb:85:in `method_missing’: Open
(WIN32OLERuntimeError)
OLE error code:800A03EC in Microsoft Excel
’racetemplate.xls’ could not be found. Check the spelling of the file
name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used
files on the File menu, make sure that the file has not been renamed, moved,
or deleted.

HRESULT error code:0x80020009
  Exception occurred
    from (druby://127.0.0.1:9001) ./excelmaker.rb:85:in `makeSheet'
etc.....

Has anyone got a sample of loading an xls or xlt template into Excel via
win32ole?

regards,

Martin

Try to specify full path of racetemplate.xls.

template = “C:\folder\racetemplate.xls”

Hope this helps.

Regards,
Masaki Suketa

···

In message “win32ole and excel” on 02/08/01, Martin Stannard martin@massive.com.au writes:

Hi,

I’m trying to open a file using win32ole in excel:

@app = WIN32OLE.new("excel.application")
@app.visible = true
template = "racetemplate.xls"
@book = @app.Workbooks.Open(template)
@sheet = @book.Worksheets(1)

“Martin Stannard” martin@massive.com.au wrote in message
news:C1126400B227D411AC1200A00CC456B501550FBA@emailserver.massive.com.au

Hi,

I’m trying to open a file using win32ole in excel:

@app = WIN32OLE.new("excel.application")
@app.visible = true
template = "racetemplate.xls"
@book = @app.Workbooks.Open(template)
@sheet = @book.Worksheets(1)

results in the following error:

(druby://127.0.0.1:9001) ./excelmaker.rb:85:in `method_missing’: Open
(WIN32OLERuntimeError)
OLE error code:800A03EC in Microsoft Excel
’racetemplate.xls’ could not be found. Check the spelling of the
file
name, and verify that the file location is correct.

Has anyone got a sample of loading an xls or xlt template into Excel via
win32ole?

My best guess is that the current directory is not what you think it is.
It will probably work if you specify the full path.

I just got the latest WIN32OLE compiled and tested it.
I got a segmentation fault on the ie.rb sample, but I could get it work on
other components.

However, my long term problem with WIN32OLE remains:
components with an optional parameter seems to not be working. I got this
document object with a Save method taking a filename and an optional second
parameter. This won’t execute.

Mikkel

I just got the latest WIN32OLE compiled and tested it.
I got a segmentation fault on the ie.rb sample, but I could get it work on
other components.

Oops, the ie.rb sample is not correct. I’ll repack win32ole-0.4.8.1-01.zip
with correct sample ie.rb.
But, the segmentation fault is still problem, so I’ll fix in near future.

However, my long term problem with WIN32OLE remains:
components with an optional parameter seems to not be working. I got this
document object with a Save method taking a filename and an optional second
parameter. This won’t execute.

Could you show me the script which is not working?

Regards,
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/04, “MikkelFJ” mikkelfj-anti-spam@bigfoot.com writes:

“Masaki Suketa” masaki.suketa@nifty.ne.jp wrote in message
news:200208041151.UAA28336@smtp2.nifty.ne.jp

However, my long term problem with WIN32OLE remains:
components with an optional parameter seems to not be working. I got
this

document object with a Save method taking a filename and an optional
second

parameter. This won’t execute.

Could you show me the script which is not working?

First of all the following Visual Basic script does result in a file:

Set mdm = CreateObject( "MDM.Document.2") mdm.Save ("testfile.mdd")

I cannot do the same with Ruby.

The problem is with the Save method of the following interface (only the
essential part shown):

[
odl,
uuid(C95896B9-F8E6-4CFE-B960-6A667FFA6658),
helpstring(“IDocument Interface”),
dual,
oleautomation
]
interface IDocument : IMDMLabeledObject {
[id(0x0000000b), propget, helpstring(“property CurrentVersion”)]

HRESULT Versions([out, retval] IVersions** pVal);
[id(0x00000022), helpstring("method Save")]
HRESULT Save([in, optional] VARIANT destination);


};

Here is the script and the runtime results. I think this has something to do
with optional parameters. I did the same test about a year ago. I did not
have problems using VB script for example.

require 'win32ole'

mralias = WIN32OLE.new(‘mrAliasMap.Mapper’)
puts mralias.CreateAlias("this is a long

name").Name
mdm = WIN32OLE.new(‘MDM.Document.2’)
mdm.Save (‘testfile.mdd’)
puts ‘done’

The yields

C:\test\w32\win32ole\sample>ruby test.rb
thialona
test.rb:6:in `method_missing’: Save (WIN32OLERuntimeError)
OLE error code:80070057 in

HRESULT error code:0x80020009
Undtagelse opstod
from test.rb:6

The first output line indicates that another COM component is working
(truncating a long name).

The Com error is partly in danish: “Undtagelse opstod” which translates to
"an exception occurred". Althoug the error says “method missing” this isn’t
true. If I change the Save to use two parameters I get a different error;

require 'win32ole'

mralias = WIN32OLE.new(‘mrAliasMap.Mapper’)
puts mralias.CreateAlias("this is a long

name").Name
mdm = WIN32OLE.new(‘MDM.Document.2’)
mdm.Save (‘testfile.mdd’, 0)
puts ‘done’

This yields

C:\test\w32\win32ole\sample>ruby test.rb
thialona
test.rb:6:in `method_missing’: Save (WIN32OLERuntimeError)
OLE error code:0 in

HRESULT error code:0x8002000e
Ugyldigt antal parametre
from test.rb:6

The danish message is know:
“Ugyldigt antal parametre” which translates to “Illegal number of
parameters”. So clearly the save method is recognized.

If I change the Save method to a non-existing name “Safe”, I get the
following output:

C:\test\w32\win32ole\sample>ruby test.rb
thialona
test.rb:6:in method_missing': Unknown property or method :Safe’
(WIN32OLERunt
imeError)
HRESULT error code:0x80020006
Ukendt navn
from test.rb:6

The danish text “Ukendt navn” translates to “Unknown name”.

Mikkel

mdm = WIN32OLE.new(‘MDM.Document.2’)
mdm.Save (‘testfile.mdd’)

(snip)

C:\test\w32\win32ole\sample>ruby test.rb
thialona
test.rb:6:in `method_missing’: Save (WIN32OLERuntimeError)
OLE error code:80070057 in

HRESULT error code:0x80020009
Undtagelse opstod
from test.rb:6

The Com error is partly in danish: “Undtagelse opstod” which translates to
"an exception occurred". Althoug the error says “method missing” this isn’t
true. If I change the Save to use two parameters I get a different error;

FYI, The “method_missing” is because of the trick of Win32OLE.
The mdm is WIN32OLE object and mdm does not have ‘Save’ method.

mdm.methods.include?(‘Save’) # ==> false

So, WIN32OLE#method_missing is called and method_missing invoke 'Save’
OLE method.

[id(0x00000022), helpstring(“method Save”)]
HRESULT Save([in, optional] VARIANT destination);

Hmm, what happen when you omit optional argument?

mdm.Save

Unfortunately, WIN32OLE can not handle VARIANT type argument correctly.

For example, AppendChunk of SQLServer (ADODB.Connection) accept one
VARIANT type argument. But, to use AppendChunk from Win32OLE, you
must use _invoke as following.

obj._invoke(0x00000453, [data.unpack(‘C*’)], [VT_ARRAY|VT_UI1])

So, How about

mdm._invoke(0x00000022, [‘testfile.mdd’], [VT_VARIANT])

or specify correct type directly.

data = 'testfile.mdd’
mdm._invoke(0x0000022, [convert_correct_type(data)], [???])

Regards,
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/05, “MikkelFJ” mikkelfj-anti-spam@bigfoot.com writes:

Masaki Suketa masaki.suketa@nifty.ne.jp wrote in
news:200208051208.VAA05514@ums509.nifty.ne.jp:

FYI, The “method_missing” is because of the trick of Win32OLE.

Right - I see that now.

[id(0x00000022), helpstring(“method Save”)]
HRESULT Save([in, optional] VARIANT destination);

Hmm, what happen when you omit optional argument?

mdm.Save

I get a “Missing FileName” exception, but it is what you would expect.
If the document had been opened first, it would save with that filename.
Or, as is the case in the following script where I actually manage to
save the document, the second call to Save do not need a name - and it
does work.

require 'win32ole’
include WIN32OLE::VARIANT

mralias = WIN32OLE.new(‘mrAliasMap.Mapper’)
puts mralias.CreateAlias(“this is a long name”).Name
mdm = WIN32OLE.new(‘MDM.Document.2’)
mdm._invoke(0x00000022, [‘testfile_invoke.mdd’], [VT_BSTR])
mdm.Save
puts ‘done’

Unfortunately, WIN32OLE can not handle VARIANT type argument
correctly.

Will that be fixed?

I haven’t tried the following, but a prerelease of OCam’Ole has just been
released a few days ago. There might be some useful VARIANT code in the C
interface.

http://tech.motion-twin.com/

For example, AppendChunk of SQLServer (ADODB.Connection) accept one
VARIANT type argument. But, to use AppendChunk from Win32OLE, you
must use _invoke as following.

obj._invoke(0x00000453, [data.unpack(‘C*’)], [VT_ARRAY|VT_UI1])

So, How about

mdm._invoke(0x00000022, [‘testfile.mdd’], [VT_VARIANT])

after including WIN32OLE::VARIANT it could run, but generated type error.

or specify correct type directly.

data = 'testfile.mdd’
mdm._invoke(0x0000022, [convert_correct_type(data)], [???])

As you can see in the script, VT_BSTR did the job.

So this is great in the sense that it now works, but it isn’t very
practical as a lot of COM components do use the VARIANT type.

Regards

Mikkel

Unfortunately, WIN32OLE can not handle VARIANT type argument
correctly.

Will that be fixed?

I am not sure but I hope to fix.

I haven’t tried the following, but a prerelease of OCam’Ole has just been
released a few days ago. There might be some useful VARIANT code in the C
interface.

http://tech.motion-twin.com/

I do not know OCaml, but it seems to me that you need to specify VT_XXX
type directly. (This is because I read excel1.ml only, so I had mistaken.)
And this seems same as _invoke approach of Ruby.

As you can see in the script, VT_BSTR did the job.

This information might help me. Thank you.
I’ll investigate more.

So this is great in the sense that it now works, but it isn’t very
practical as a lot of COM components do use the VARIANT type.

Win32OLE sometimes works fine.
For example,
Workbooks.SaveAs of Excel VBA has VARIANT type argument.

require 'win32ole’
excel = WIN32OLE.new(‘Excel.Application’)
excel.visible = true
book = excel.workbooks.add
save_as_info = book.ole_method_help(‘SaveAs’)
save_as_info.params.each do |param|
print param.ole_type + " " + param.name
print " [IN]" if param.input?
print " [OPTIONAL]" if param.optional?
print "\n"
end

book.SaveAs

book.SaveAs(‘file1.xls’)

book.SaveAs(‘file1.txt’, -4158)

The result is

VARIANT Filename [IN] [OPTIONAL]
VARIANT FileFormat [IN] [OPTIONAL]
VARIANT Password [IN] [OPTIONAL]
VARIANT WriteResPassword [IN] [OPTIONAL]
VARIANT ReadOnlyRecommended [IN] [OPTIONAL]
VARIANT CreateBackup [IN] [OPTIONAL]
XlSaveAsAccessMode AccessMode [IN] [OPTIONAL]
VARIANT ConflictResolution [IN] [OPTIONAL]
VARIANT AddToMru [IN] [OPTIONAL]
VARIANT TextCodepage [IN] [OPTIONAL]
VARIANT TextVisualLayout [IN] [OPTIONAL]

and

book.SaveAs
book.SaveAs(‘file1.xls’)
book.SaveAs(‘file1.txt’, -4158)

works fine.
But sometimes, you must use _invoke and specify VT_XXX type directly.

Regards,
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/06, MikkelFJ mikkelfj-anti-spam@bigfoot.com writes:

I do not know OCaml, but it seems to me that you need to specify VT_XXX
type directly. (This is because I read excel1.ml only, so I had mistaken.)
And this seems same as _invoke approach of Ruby.

I think they generate wrapper code from the typelib. So the wrapper uses
invoke but the user doesn’t.

As you can see in the script, VT_BSTR did the job.

This information might help me. Thank you.
I’ll investigate more.

Strange problem.
Here is an idea:

COM objects implement the IDispatch interface as they choose.
It could be that the Excel implementation is more tolerant towards different
types than the MDM object.

I think the MDM objects uses the Microsoft ATL standard implementation (99%
of all new C++ based COM objects do that).

Mikkel

I think they generate wrapper code from the typelib. So the wrapper uses
invoke but the user doesn’t.

FYI, sample/olegen.rb creates wrapper code from the typelib.

olegen.rb ‘Microsoft Excel 9.0 Object Library’

COM objects implement the IDispatch interface as they choose.
It could be that the Excel implementation is more tolerant towards different
types than the MDM object.

Could you try the following patch for 0.4.8.1?
(I have not tested because I have no mdm object.)

win32ole.c.org Sun Aug 4 00:00:00 2002
+++ win32ole.c Wed Aug 7 21:29:44 2002
@@ -1762,11 +1762,8 @@
VariantInit(&realargs[n]);
VariantInit(&op.dp.rgvarg[n]);
param = rb_ary_entry(paramS, i-cNamedArgs);

···

In message “Re: win32ole and excel” on 02/08/07, “MikkelFJ” mikkelfj-anti-spam@bigfoot.com writes:

    ole_val2variant(param, &realargs[n]);
  •        V_VT(&op.dp.rgvarg[n]) = VT_VARIANT | VT_BYREF;
    
  •   V_VARIANTREF(&op.dp.rgvarg[n]) = &realargs[n];
    
  •   ole_val2variant(param, &op.dp.rgvarg[n]);
       }
    

    }
    /* apparent you need to call propput, you need this */

    Regards,
    Masaki Suketa

Could you try the following patch for 0.4.8.1?
(I have not tested because I have no mdm object.)

Well, I tried patch, but I’ve never done that before and got
patching file `win32ole.c’
Hunk #1 FAILED at 1762.
1 out of 1 hunk FAILED – saving rejects to win32ole.c.rej

Mikkel

Hmmm…

Is there following code in the original win32ole.c?

#define WIN32OLE_VERSION “0.4.8.1”

Anyway, I’ll send you full win32ole.c later.

Regards
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/09, “MikkelFJ” mikkelfj-anti-spam@bigfoot.com writes:

Could you try the following patch for 0.4.8.1?
(I have not tested because I have no mdm object.)

Well, I tried patch, but I’ve never done that before and got
patching file `win32ole.c’
Hunk #1 FAILED at 1762.
1 out of 1 hunk FAILED – saving rejects to win32ole.c.rej

Well, I tried patch, but I’ve never done that before and got
patching file `win32ole.c’
Hunk #1 FAILED at 1762.
1 out of 1 hunk FAILED – saving rejects to win32ole.c.rej

Sorry, I sended email to you, but I recieved following message:
----- The following addresses had permanent fatal errors -----
mikkelfj-anti-spam@bigfoot.com

Could you try following file?
http://homepage1.nifty.com/markey/ruby/win32ole/win32ole.c.zip

Regards,
Masaki Suketa

“Masaki Suketa” masaki.suketa@nifty.ne.jp wrote in message
news:200208101237.VAA02828@smtp2.nifty.ne.jp

Well, I tried patch, but I’ve never done that before and got
patching file `win32ole.c’
Hunk #1 FAILED at 1762.
1 out of 1 hunk FAILED – saving rejects to win32ole.c.rej

Sorry, I sended email to you, but I recieved following message:
----- The following addresses had permanent fatal errors -----
mikkelfj-anti-spam@bigfoot.com

You should remove “-anti-spam”

Could you try following file?
http://homepage1.nifty.com/markey/ruby/win32ole/win32ole.c.zip

Now that is what I call progress - it is smaller than the old file, but it
works a lot better.
Thanks - if this is a general solution, perhaps Ruby can finally be used to
test COM components (and drive them) This is really great news.

This works:

require 'win32ole’
mdm = WIN32OLE.new(‘MDM.Document.2’)
mdm.Save ‘testfile.mdd’
#mdm._invoke(0x00000022, [‘testfile_invoke.mdd’], [VT_BSTR])
mdm.Save
puts ‘done’

thanks :slight_smile:

Mikkel

Thanks - if this is a general solution, perhaps Ruby can finally be used
to
test COM components (and drive them) This is really great news.

I’m pretty ignorant of COM in general, but
are you familiar with Ralph Mason’s RubyCOM?

I’ve used it in some situations to drive
apps (with COM interfaces) via Ruby.

Hal

···

----- Original Message -----
From: “MikkelFJ” mikkelfj-anti-spam@bigfoot.com
Newsgroups: comp.lang.ruby
To: “ruby-talk ML” ruby-talk@ruby-lang.org
Sent: Saturday, August 10, 2002 11:26 AM
Subject: Re: win32ole and excel

I understand what is the problem now.

As you noticed, the win32ole.c is smaller than old file and some
features of old Win32OLE are lost. This is because I wanted to find
out what was the problem at first. The ommiting features way is easier
than fixing this problem without losing any features.

The next step is how to fix this problem without losing any features.
I think I’ll fix this problem because I understand what is the problem.
Anyway thanks for your help.

Regards,
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/11, “MikkelFJ” mikkelfj-anti-spam@bigfoot.com writes:

Could you try following file?
http://homepage1.nifty.com/markey/ruby/win32ole/win32ole.c.zip

Now that is what I call progress - it is smaller than the old file, but it
works a lot better.
Thanks - if this is a general solution, perhaps Ruby can finally be used to
test COM components (and drive them) This is really great news.

This works:

I’m pretty ignorant of COM in general, but
are you familiar with Ralph Mason’s RubyCOM?

I’m not sure - I think I am.

Someone posted something that could create COM objects in Ruby last autumn
or so, and which also could call COM from Ruby. Because of my WINOLE32
problems I tested this and pointed out the same problem as with WINOLE32,
but there didn’t seem to come any fixes so I reluctantly dropped the fact
that Ruby and COM was to coexist.

I’ve used it in some situations to drive
apps (with COM interfaces) via Ruby.

Yeah it works sometimes, but that isn’t enough.

Now it seems a different story though. I really hope this is a general fix
which possible also applies to RubyCOM?

I don’t know that status of RubyCOM but if it can implement vtable
interfaces, it would be really cool. As I recall it reflects Ruby into
IDispatch. This allows any Ruby object to be a COM object, which is really
cool. But it doesn’t help you implement existing interfaces, which is really
a requirement for COM development.

Mikkel

Done. Please try Win32OLE 0.5.0.

Regards,
Masaki Suketa

···

In message “Re: win32ole and excel” on 02/08/11, Masaki Suketa masaki.suketa@nifty.ne.jp writes:

The next step is how to fix this problem without losing any features.
I think I’ll fix this problem because I understand what is the problem.
Anyway thanks for your help.

“Masaki Suketa” masaki.suketa@nifty.ne.jp wrote in message
news:200208131212.VAA26863@ums509.nifty.ne.jp

···

In message “Re: win32ole and excel” > on 02/08/11, Masaki Suketa masaki.suketa@nifty.ne.jp writes:

The next step is how to fix this problem without losing any features.
I think I’ll fix this problem because I understand what is the problem.
Anyway thanks for your help.

Done. Please try Win32OLE 0.5.0.

Works on my test script :slight_smile:

Mikkel