Re: [S] Loading Excel *.xla from Splus.

John.Gavin@wdr.com
Tue, 15 Sep 1998 09:56:09 +0100


Hi,

I had only one reply to my query.

I agree with the Statsci developer's suggestion but it didnt work for me.
I get an error about connecting to the library
so I am asking again to see if someone can help.
Below are three failed attempts.

The problem
-----------
The objective is to test a function written by someone else
in C++ and accesable to users via an Excel addin.
I want to test it from within SPlus so I need to call excel
and then load and activate the xla library before
calling the user's function within excel and
passing the answer back to splus.

Loading the dll directly into Splus and
bypassing excel is not an option for various reasons.

Thanks in advance for any help you can offer,

John.

# Attempt 1
# ---------
# create a visible excel object
pExcel <- create.ole.object( "excel.application" )
ExcelVisible <- get.ole.property( pExcel, "visible" )$visible
if ( !ExcelVisible ) set.ole.property( pExcel, list( visible=T ) )

# the following VBA works when done manually with File-Open-'xla file',
# as excel loads and executes the xls file so I can see the functions
# in the loaded library (e.g. using the excel function wizard).
# Workbooks.Open(FileName:="c:\\temp\\abc.XLA"). _
# RunAutoMacros Which:=xlAutoOpen

# here is the corresponding splus code
pWorkBooks <- get.ole.property( pExcel, "workbooks" )[[1]]
# need new workbook to see 'Add-Ins' in 'Tools' menu
pNewBook <- call.ole.method( pWorkBooks, "Add" )
pOpenBook <- call.ole.method( pWorkBooks, "Open", "c:\\temp\\abc.XLA" )
xlAutoOpen <- 1; xlAutoClose <- 2; xlAutoActivate <- 3; xlAutoDeactivate <-4
call.ole.method( pOpenBook, "RunAutoMacros" , xlAutoOpen)
# After a while SPlus issues an error connecting to library
# The request cannot be completed cos the Excel spreadsheet is not responding.
# I have the option to 'switch to' or 'retry', neither works
# so I have to kill the Excel app to regain control of SPlus

# Attempt 2
# ---------
# Another approach which also failed is to use the 'addins' method to excel
# then 'add' the *.xla file and finally use the 'installed' property.

# create a visible excel object
pExcel <- create.ole.object( "excel.application" )
ExcelVisible <- get.ole.property( pExcel, "visible" )$visible
if ( !ExcelVisible ) set.ole.property( pExcel, list( visible=T ) )
pAddIns <- get.ole.property( pExcel, "addins" ) [[1]]
pNewAddIn <- call.ole.method( pAddIns, "add", "c:\\temp\\abc.xla" )
# install the addin
if (!get.ole.property( pNewAddIn, "installed" ) $installed )
{ set.ole.property( pNewAddIn, list(installed=T) )
print(get.ole.property( pNewAddIn, "installed" ) $installed)
}

# Attempt 3
# ---------
# Other approaches which I tried but failed to hack include
# using the 'AddinInstall' event but I cant see how to access an event
# as opposed to a method or property.

# create a visible excel object
pExcel <- create.ole.object( "excel.application" )
ExcelVisible <- get.ole.property( pExcel, "visible" )$visible
if ( !ExcelVisible ) set.ole.property( pExcel, list( visible=T ) )

pWorkBooks <- get.ole.property( pExcel, "workbooks" )[[1]]
pNewBook <- call.ole.method( pWorkBooks, "Add" )
pOpenBook <- call.ole.method( pWorkBooks, "Open", "c:\\temp\\abc.XLA" )
call.ole.method( pOpenBook, "Isaddin" )
call.ole.method( pOpenBook, "Activate" )
# AddinInstall is an event. How do I call this?
# set.ole.property( pOpenBook, "AddinInstall" )
# WorkbookAddinInstall is an event. How do I call this?
# call.ole.method( pOpenBook, "WorkbookAddinInstall" )

______________________________ Reply Separator _________________________________
Subject: Re: [S] Loading Excel *.xla from Splus.
Author: leee (leee@statsci.com) at unix,mime
Date: 10/09/1998 19:15

John,

I passed this on to one of our developers, Chris Disdero, and here is his
response:

>RunAutoMacros is simply a method of a workbook, according to Excel on-line
>help. So, you should simply be able to call this method on the pNewBook2
>pointer you obtained in the S-PLUS script:
>
>xlAutoOpen <- 1
>xlAutoOpen <- 2
>xlAutoActivate <- 3
>xlAutoDeactivate <-4
>call.ole.method( pNewBook, "RunAutoMacros", xlAutoOpen ) >
>Note that the numeric values of the constants were obtained by using the VB
>object browser on Excel constants.

Hope this helps,

Lee Edlefsen
VP of Development

At 04:33 PM 9/8/98 +0100, John.Gavin@wdr.com wrote: >Hi,
>
>I want to use Splus to load and execute an excel *.xla file >(to make some user
defined excel functions visible to Splus). >
>The following code loads the file but doesnt execute it >
>pExcel <- create.ole.object( "excel.application" ) >pVisible <-
set.ole.property( pExcel, list( visible=T ) ) >pWorkBooks <- get.ole.property(
pExcel, "workbooks" ) [[1]]
>pNewBook2 <- call.ole.method( pWorkBooks , "Open", "c:\\temp\\book.xla") >
>The VBA equivqlent of what I think I need to tell Excel is >
>Workbooks.Open(Filename:="c:\\temp\\book.xla").RunAutoMacros Which:=xlAutoOpen
>
>but I cant see how to do this using SPlus. >
>I have looked in splus4\samples\oleauto\splus
>and the programmer's guide chp 15 without success. >
>I am on NT4.0 with SPlus 4.5 Professional Release 2. >
>Thanks,
>
>John.
>

-----------------------------------------------------------------------
This message was distributed by s-news@wubios.wustl.edu. To unsubscribe
send e-mail to s-news-request@wubios.wustl.edu with the BODY of the
message: unsubscribe s-news