Getting SPSS Data to Excel (Automation from Excel)

[Hari]

Hi,

Is it possible to copy all CASES from SPSS to Excel for some VARIABLES
using excel automation. (assuming that number of cases in my SPSS Data
file doesnt exceed the 65536 limit of number of rows in an Excel
Worksheet)

a) Iam comfortable with excel and have been able to adopt code Zilched
from web-sites like http://peltiertech.com/Excel/XL_PPT.html for
automating other applications from Excel.
But I dont know whether it is possible to copy specific variables (All
cases within that variable) from a open SPSS data file and paste in to
Excel using the method outlined in Jon peltier's method. Please tell me
if the same is possible.

b) I came across a method described in
http://www.indiana.edu/~statmath/stat/all/odbc/printable.pdf where it
is possible to transfer data using ODBC (I have not worked with
databases and dont have any idea). But it says that "Remember, however,
that unlike SAS, SPSS can read only ODBC data sources and cannot serve
as an ODBC data source." Does this limitation exist in new versions of
SPSS also?

Is there any method through which I could accomplish transfer of data
from SPSS to Excel while sitting in Excel (Automation).

Please guide me.

Regards,
Hari
India

[neila...@msn.com]

Hari,
To answer the question as posted:
Here is the simplest example -but in my humble opinion,
methodologically suspect-

Check the script help system for restrictions etc.
See also SelectTextData and SelectCells methods.

FWIW: I abhor using the clipboard and despise
applications which do so for data transfer.
To do this "correctly" have SPSS translate to XLS,
or if you're a true gearhead use the APIs from spssio32.dll
Neila

Option Explicit

Dim objSpssApp As Object
' ISPSSApp after setting reference to SPSSWin.tlb
'SPSS is running with active file.... etc...

Sub Main()
Set objSpssApp = GetObject(, "SPSS.Application")
With objSpssApp.Documents.GetDataDoc(0)
.SelectVariables "sex", "life"
.Copy
End With
Application.ActiveWorkbook.ActiveSheet.Paste
End Sub

[Hari]

Hi Neila,

Thanks for the code, it works fine. I have a follow-up doubt. After
setting references to SPSS in excel , I get the arguments for
SelectVariables as StartVariable and EndVariable. If there are any
variables between "Sex" and "life" then they also get copied. Is there
any other Property/method in SPSS which would enable me to copy only
variables listed in the code. Also, in case the variables of my
interest are seperated by more than 256 columns then Copy method fails.
thats why wanted to know whether it would be possible to copy only
defined variables using some other method (automation).

I would try to explore (learn) the API method.

Regards,
Hari
India

[neila...@msn.com]

Hari,

To do this "correctly" have SPSS translate to XLS,

(Supports a DROP or KEEP subcommand).
Neila

[neila...@msn.com]

Hari,
Well, here is another way (but using the clipboard sucks).
MATCH FILES / FILE * / KEEP varlistyouwish.
then do the copy/paste thing, or better: SAVE TRANSLATE.....
HTH, Neila

[Hari]

Hi Neila,

Thnaks a lot for your help.

Regards,
hari
India

[Hari]

Hi Neila,

Thanks a lot for your help.

Regards,
Hari
India