opening SPSS and a macro from Excel VBA

[alphapoint05]

I have Excel VBA code that opens and passes a worksheet to SPSS. This
has not been a problem. Right now, Excel now tells the user to open and
run a file called Run.sbs once SPSS opens. I would like to skip this.
In other words, how would I program Excel to open SPSS and then run the
syntax with no user operations?

Thanks,
Jeff Miller

 

[Guest]

What exactly are you up to with SPSS. If it is Essbase then there are a whole
pile of API's that you can access. If it is Showcase then you are much more
limited. Post your code and give us a better description of what you are up
to.

 

[alphapoint05]

No, I'm just using the Base statistical package. I sending data from
Excel to SPSS. The macro in SPSS permits the user to run statistical
techniques that aren't available in Excel. It then ships the results
back to Excel. Excel then creates charts from the results.

MsgBox "Please close existing SPSS applications and click OK."
Msg = ("SPSS opens automatically provided that it's path is c:\Program
Files\SPSSGP\spsswin.exe")
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & ("If you see a 'What Would You Like To Do?' window, click
Cancel")
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & ("Once SPSS opens, open and run the syntax
'c:\AdSAM\macros\Run.sps'")
Msg = Msg & vbNewLine & vbNewLine & ("When through with SPSS, return to
Excel and click OK.")
Msg = Msg & vbNewLine & vbNewLine & ("Ideally, Excel will open SPSS and
run the SPSS syntax without you needing to open the Run.sps file")
Msg = Msg & vbNewLine & vbNewLine & ("Still working on this....")


Msg = Msg & vbNewLine & vbNewLine & "Ready to open SPSS?"
Msg = Msg & vbNewLine & vbNewLine
openspssnow = MsgBox(Msg, vbYesNo, "Opening SPSS")
If openspssnow = vbYes Then
On Error Resume Next


Program = "c:\Program Files\SPSSGP\spsswin.exe"
TaskID = Shell(Program, 1)
If Err <> 0 Then
MsgBox "Unable to start " & Program, vbCritical, "Error"
End If
End If

 

[Guest]

Sorry I am not familiar with the base stats package. I am using Essbase here.

 

[Nigel]

Hi Jeff
SPSS has an API and scripting language using SaxBasic, the object model is
documented by SPSS. Not sure if this is online. I suspect the syntax file
is creating some xls output that you then import into Excel.

You can create an SPSS session using VBA, then open and run the SPSS syntax,
create the output and load the resultant xls file back into your open Excel
session. This is probably the best method.

To provide some specific code please confirm this is what you are trying to
do? I'm quite happy to work with you to solve this.

 

[alphapoint05]

Hi Nigel,

Yes, this is what I want it to do. However, I was hoping I could
automate it so that the Excel code opens SPSS, ships the data, runs the
SPSS code, and ships results back to Excel. The Excel VBA code will
open SPSS and ship the data to SPSS. The SPSS code will run the
analyses and ship results to Excel. I can't seem to link the two where
Excel will not only open SPSS, ship the data, but also run the SPSS
analysis syntax and scripts.

 

[Nigel]

Hi Jim
OK what you ask is possible, I have some code I use in my office which I
will retrieve. The method works by running the Excel VBA code to create an
SPSS session, load a syntax file and run it, you need to write the syntax to
pick up the data from an Excel.xls file or CSV with appropriate file
definition.
The syntax also needs to write the results back to an xls sheet which the
Excel VBA code will open and use, it also closes the SPSS session. There
is a method for SPSS to return a completion code back to Excel to run the
post SPSS process.

I'll get back to you.

 

[Bill TOng]

I would like to run a regression in SPSS from Excel and save the results
back to Excel. ALthough I have done quite a bit of programming in VB in
Excel, I have no experience linking to SPSS. Can someone please help
me?

Thanks

 

[yogis_a]

hi nigel.
thanks for this piece of information.
you mentioned a piece of code that does the task in question.
could u mail me at (e-mail address removed)

thanks.

 

[niladri]

Hi Nigel,

Can you please send me the piece of code that you mentioned here in (e-mail address removed)?

Thanks,
Niladri

 

[Fei-Fang]

Hi Nigel,
I would like to run a regression in SPSS from Excel and save the results
back to Excel.
you mentioned a piece of code ,could u mail me at ([email protected])

thanks a lot!

 

[telosua89]

Hi Nigel,

Could you also send me the piece of code that runs SPSS from excel to this email ([email protected])
Thanks