Discussion:
Running SPSS 11.5 from Excel
(too old to reply)
Lance Hoffmeyer
2003-10-29 23:05:39 UTC
Permalink
Has anyone written an Excel macro to run SPSS syntax from Excel?

Lance
Tom Brown
2003-10-30 00:56:28 UTC
Permalink
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,

Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True

sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Lance Hoffmeyer
2003-10-30 14:19:19 UTC
Permalink
Thanks,

works great.

How do I open a SPSS OUTPUT Viewer window? It does not open
automatically as when one opens SPSS manually

Lance
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Lance Hoffmeyer
2003-10-30 14:32:31 UTC
Permalink
Sub Openfile()
OFile = Application.GetOpenFilename("SPSS Datafile, *.sav")


Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True

Set oOut = oApp.NewOutputDoc
oOut.Visible = True


sCommands = "GET FILE = ' " & OFile & " ' "
oApp.ExecuteCommands sCommands, True

'sDisplayDic = "DISPLAY DICTIONARY"
'oApp.ExecuteCommands sDisplayDic, True
End Sub
Post by Lance Hoffmeyer
Thanks,
works great.
How do I open a SPSS OUTPUT Viewer window? It does not open
automatically as when one opens SPSS manually
Lance
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Lance Hoffmeyer
2003-10-30 15:08:28 UTC
Permalink
I am having problems getting variable info into an excel sheet?
I am trying to use a script to export from Output Viewer into
Excel. Is there a better way?

Lance



Sub Openfile()
'OPEN DIALOG BOX TO OPEN SPSSFILE
OFile = Application.GetOpenFilename("SPSS Datafile, *.sav")

'USE OLE TO OPEN SPSS
Set OApp = CreateObject("SPSS.Application")
Set oDoc = OApp.NewDataDoc
oDoc.Visible = True
'USE OLE TO OPEN OUTPUT DOC
Set oOut = OApp.NewOutputDoc
oOut.Visible = True

'OPEN NEW DATAFILE
sCommands = "GET FILE = ' " & OFile & " ' "
OApp.ExecuteCommands sCommands, True


'DISPLAY VARIABLE INFORMATION AND EXPORT TO EXCEL
sDisplayDic = "DISPLAY DICTIONARY"
OApp.ExecuteCommands sDisplayDic, True

'oOut.Items.Copy

Worksheets("Sheet2").Select
Cells(1, 1).Select
sScript = "script C:\Program Files\SPSS\Scripts\ExportExcel.sbs"
OApp.ExecuteCommands sScript, True


End Sub
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Tom Brown
2003-10-30 18:23:43 UTC
Permalink
Post by Lance Hoffmeyer
I am having problems getting variable info into an excel sheet?
I am trying to use a script to export from Output Viewer into
Excel. Is there a better way?
Lance
Lance,
If you are trying to get variable information, you can pull that directly from
the data file without using the Output Viewer. It's usually easier. What exactly
are you after?

I'll take this opportunity to recommend Raynald's site:
http://pages.infinit.net/rlevesqu/

oh, and most people use an o- or obj- prefix (oApp) to refer to an object, and
an s- or str- prefix to refer to a string (sFile). Saves confusion ;-)
Post by Lance Hoffmeyer
Sub Openfile()
'OPEN DIALOG BOX TO OPEN SPSSFILE
OFile = Application.GetOpenFilename("SPSS Datafile, *.sav")
'USE OLE TO OPEN SPSS
Set OApp = CreateObject("SPSS.Application")
Set oDoc = OApp.NewDataDoc
oDoc.Visible = True
'USE OLE TO OPEN OUTPUT DOC
Set oOut = OApp.NewOutputDoc
oOut.Visible = True
'OPEN NEW DATAFILE
sCommands = "GET FILE = ' " & OFile & " ' "
OApp.ExecuteCommands sCommands, True
'DISPLAY VARIABLE INFORMATION AND EXPORT TO EXCEL
sDisplayDic = "DISPLAY DICTIONARY"
OApp.ExecuteCommands sDisplayDic, True
'oOut.Items.Copy
Worksheets("Sheet2").Select
Cells(1, 1).Select
sScript = "script C:\Program Files\SPSS\Scripts\ExportExcel.sbs"
OApp.ExecuteCommands sScript, True
End Sub
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Lance Hoffmeyer
2003-10-30 19:36:48 UTC
Permalink
Tom,

Thanks for all the help.

I have written Excel code that takes output from 1 Way ANOVA and Basic
Tables and combines them into a profile of segments (Groups from 2-11).

Currently I run the analysis syntax from SPSS (ANOVA and BASIC Tables,
run a script in SPSS to export this output into Excel (SAX script).
Finally, I run the macro in Excel to reformat the Excel worksheets.

I wish to run all of this from Excel (via OLE SPSS).

1) Open SPSS datafile (DONE)
2) Get list of variables in SPSS dataset and copy into Excel sheet
(say Sheet2, Col A) so I can choose multiple variables to use to
run ANOVA and Basic tables (create syntax in Excel and run
OApp.ExecuteCommands. I will use an Excel "multi-selection list box"
to choose variables. I don't want to use the dialog boxes from
http://pages.infinit.net/rlevesqu/ because I want to be able to drag
the mouse down a number of variables and it did not appear I could do
that with the SPSS dialog boxes (highlight 20 variables or more
before selecting them).
3) Once I have list of SPSS variables in Excel, choose a certain number
for analysis (through Excel "multi-selection list box")
4) Run analysis (through Excel via OApp.ExecuteCommands)
5) Run script already created to export each pivot table in OUTPUT
viewer into it's own Excel worksheet
(Through Excel OApp.ExecuteCommands)
6) Run Excel macro to format sheets


I think that once I have figured out how to get the SPSS dataset
variable names into Excel then things should go smoothly until I
get to running the SBS script. Didn't appear to work for DATA
DICTIONARY but that is a few steps away.

Lance
Post by Tom Brown
Post by Lance Hoffmeyer
I am having problems getting variable info into an excel sheet?
I am trying to use a script to export from Output Viewer into
Excel. Is there a better way?
Lance
Lance,
If you are trying to get variable information, you can pull that directly from
the data file without using the Output Viewer. It's usually easier. What exactly
are you after?
http://pages.infinit.net/rlevesqu/
oh, and most people use an o- or obj- prefix (oApp) to refer to an object, and
an s- or str- prefix to refer to a string (sFile). Saves confusion ;-)
Post by Lance Hoffmeyer
Sub Openfile()
'OPEN DIALOG BOX TO OPEN SPSSFILE
OFile = Application.GetOpenFilename("SPSS Datafile, *.sav")
'USE OLE TO OPEN SPSS
Set OApp = CreateObject("SPSS.Application")
Set oDoc = OApp.NewDataDoc
oDoc.Visible = True
'USE OLE TO OPEN OUTPUT DOC
Set oOut = OApp.NewOutputDoc
oOut.Visible = True
'OPEN NEW DATAFILE
sCommands = "GET FILE = ' " & OFile & " ' "
OApp.ExecuteCommands sCommands, True
'DISPLAY VARIABLE INFORMATION AND EXPORT TO EXCEL
sDisplayDic = "DISPLAY DICTIONARY"
OApp.ExecuteCommands sDisplayDic, True
'oOut.Items.Copy
Worksheets("Sheet2").Select
Cells(1, 1).Select
sScript = "script C:\Program Files\SPSS\Scripts\ExportExcel.sbs"
OApp.ExecuteCommands sScript, True
End Sub
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Tom Brown
2003-10-31 07:41:10 UTC
Permalink
Post by Lance Hoffmeyer
Tom,
Thanks for all the help.
I have written Excel code that takes output from 1 Way ANOVA and Basic
Tables and combines them into a profile of segments (Groups from 2-11).
Currently I run the analysis syntax from SPSS (ANOVA and BASIC Tables,
run a script in SPSS to export this output into Excel (SAX script).
Finally, I run the macro in Excel to reformat the Excel worksheets.
I wish to run all of this from Excel (via OLE SPSS).
1) Open SPSS datafile (DONE)
2) Get list of variables in SPSS dataset and copy into Excel sheet
(say Sheet2, Col A) so I can choose multiple variables to use to
run ANOVA and Basic tables (create syntax in Excel and run
OApp.ExecuteCommands. I will use an Excel "multi-selection list box"
to choose variables. I don't want to use the dialog boxes from
http://pages.infinit.net/rlevesqu/ because I want to be able to drag
the mouse down a number of variables and it did not appear I could do
that with the SPSS dialog boxes (highlight 20 variables or more
before selecting them).
3) Once I have list of SPSS variables in Excel, choose a certain number
for analysis (through Excel "multi-selection list box")
4) Run analysis (through Excel via OApp.ExecuteCommands)
5) Run script already created to export each pivot table in OUTPUT
viewer into it's own Excel worksheet
(Through Excel OApp.ExecuteCommands)
6) Run Excel macro to format sheets
I think that once I have figured out how to get the SPSS dataset
variable names into Excel then things should go smoothly until I
get to running the SBS script. Didn't appear to work for DATA
DICTIONARY but that is a few steps away.
I see,
syntax strings need dots and linefeeds just like the real thing.
you could use something like this:


sFileSpec = "c:\data\data.sav"

Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.OpenDataDoc(sFileSpec)
oDoc.Visible = True

Set oInfo = oApp.SpssInfo

For i = 1 To (oInfo.NumVariables)

varName = oInfo.VariableAt(i - 1)
varLabel = oInfo.VariableLabelAt(i - 1)
If Len(varLabel) = 0 Then varLabel = varName

Sheets(1).ListBox1.AddItem varName
Sheets(1).Cells(i, 1) = varName
Sheets(1).Cells(i, 2) = varLabel

Next
Post by Lance Hoffmeyer
Post by Tom Brown
Post by Lance Hoffmeyer
I am having problems getting variable info into an excel sheet?
I am trying to use a script to export from Output Viewer into
Excel. Is there a better way?
Lance
Lance,
If you are trying to get variable information, you can pull that directly from
the data file without using the Output Viewer. It's usually easier. What exactly
are you after?
http://pages.infinit.net/rlevesqu/
oh, and most people use an o- or obj- prefix (oApp) to refer to an object, and
an s- or str- prefix to refer to a string (sFile). Saves confusion ;-)
Post by Lance Hoffmeyer
Sub Openfile()
'OPEN DIALOG BOX TO OPEN SPSSFILE
OFile = Application.GetOpenFilename("SPSS Datafile, *.sav")
'USE OLE TO OPEN SPSS
Set OApp = CreateObject("SPSS.Application")
Set oDoc = OApp.NewDataDoc
oDoc.Visible = True
'USE OLE TO OPEN OUTPUT DOC
Set oOut = OApp.NewOutputDoc
oOut.Visible = True
'OPEN NEW DATAFILE
sCommands = "GET FILE = ' " & OFile & " ' "
OApp.ExecuteCommands sCommands, True
'DISPLAY VARIABLE INFORMATION AND EXPORT TO EXCEL
sDisplayDic = "DISPLAY DICTIONARY"
OApp.ExecuteCommands sDisplayDic, True
'oOut.Items.Copy
Worksheets("Sheet2").Select
Cells(1, 1).Select
sScript = "script C:\Program Files\SPSS\Scripts\ExportExcel.sbs"
OApp.ExecuteCommands sScript, True
End Sub
Post by Tom Brown
Post by Lance Hoffmeyer
Has anyone written an Excel macro to run SPSS syntax from Excel?
Lance
Yes,
Set oApp = CreateObject("SPSS.Application")
Set oDoc = oApp.NewDataDoc
oDoc.Visible = True
sCommands ="GET FILE = 'whatever'."
oApp.ExecuteCommands sCommands, True
Continue reading on narkive:
Loading...