Excel Integration

[Deepanshu Bhalla]

CONTENTS DELETED

The author has deleted this message.

[David Marso]

In this case I would suggest you run SPSS from your Excel macro using references to the SPSS Com Objects.
Add the SPSS libraries you require under References and you will get Intellisense help.
See CreateObject method and go on from there.
FWIW:  Way back in the previous century (SPSS 7.5 ?1996+-?) I designed and wrote the Excel sample DKExcel which was one of the samples shipped with the SPSS product.  It used to be under the /Developer folder on the CD.  I have no idea what IBM/SPSS are shipping these days WRT such samples or whether this gem is even distributed anymore.
DDE?  No comment ???masochistic are we???;-)  

Deepanshu Bhalla wrote

Hi Team

Is there any way we can integrate SPSS with excel . Basically i have formatted excel template and i wish the excel macro to be run within spss running time . Right now , i have the spss syntax which store output file in some folder, after that i run macro to get the final formatted report .

In SAS i know this thing can be done using DDE language .

Thanks in advance !

[Jon K Peck]

You can use the SPSS VBA equivalent, just called Basic scripting, to invoke and control Excel from within your Statistics job  by referencing the Excel type libraries.  You can see an example of this in the ExportTablesToExcelFiles package posted on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral).  That package takes selected Viewer output and creates one or more Excel files and manipulates these in Basic.

You would invoke your Basic code in a syntax stream by using the SCRIPT command.

This can also be done using Python scripting if you install the win32 extensions for Python, but the Basic approach would be more direct.

HTH,

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Deepanshu Bhalla <[hidden email]>
To:        [hidden email]
Date:        04/03/2012 11:19 AM
Subject:        [SPSSX-L] Excel Integration
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi Team

Is there any way we can integrate SPSS with excel . Basically i have
formatted excel template and i wish the excel macro to be run within spss
running time . Right now , i have the spss syntax which store output file in
some folder, after that i run macro to get the final formatted report .

In SAS i know this thing can be done using DDE language .

Thanks in advance !

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-Integration-tp5615849p5615849.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Art Kendall]

David gave a way to do what you said.  However,  if you explain in more detail just what you are doing it is likely that you can do it right in SPSS.

On 4/3/2012 1:12 PM, Deepanshu Bhalla wrote:

Hi Team

Is there any way we can integrate SPSS with excel . Basically i have
formatted excel template and i wish the excel macro to be run within spss
running time . Right now , i have the spss syntax which store output file in
some folder, after that i run macro to get the final formatted report .

In SAS i know this thing can be done using DDE language .

Thanks in advance !

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-Integration-tp5615849p5615849.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Deepanshu Bhalla]

CONTENTS DELETED

The author has deleted this message.

[Jon K Peck]

SPSS Community: www.ibm.com/developerworks/spssdevcentral
Downloads for SPSS Statistics
Utilities
ExportTablesToExcelFiles.zip  (might not be on the first page of that listing)

A direct url (careful with the line wrapping): https://www.ibm.com/developerworks/mydeveloperworks/files/app?lang=en#/person/270002VCWN/file/8e0dfcb6-aa57-4639-a20e-1780010cfe83

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Deepanshu Bhalla <[hidden email]>
To:        [hidden email]
Date:        04/04/2012 10:39 AM
Subject:        Re: [SPSSX-L] Excel Integration
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks Jon for sharing the link but am sorry to say i cudnt find the particular export to excel example...It would be great if you share detailed link of the example

Thanks in advance !

Sent from Yahoo! Mail on Android

---

From: Jon K Peck [via SPSSX Discussion] <[hidden email]>;
To: Deepanshu Bhalla <[hidden email]>;
Subject: Re: Excel Integration
Sent: Tue, Apr 3, 2012 6:16:31 PM

You can use the SPSS VBA equivalent, just called Basic scripting, to invoke and control Excel from within your Statistics job  by referencing the Excel type libraries.  You can see an example of this in the ExportTablesToExcelFiles package posted on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral).  That package takes selected Viewer output and creates one or more Excel files and manipulates these in Basic.

You would invoke your Basic code in a syntax stream by using the SCRIPT command.

This can also be done using Python scripting if you install the win32 extensions for Python, but the Basic approach would be more direct.

HTH,

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Deepanshu Bhalla <[hidden email]>
To:        [hidden email]
Date:        04/03/2012 11:19 AM
Subject:        [SPSSX-L] Excel Integration
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi Team

Is there any way we can integrate SPSS with excel . Basically i have
formatted excel template and i wish the excel macro to be run within spss
running time . Right now , i have the spss syntax which store output file in
some folder, after that i run macro to get the final formatted report .

In SAS i know this thing can be done using DDE language .

Thanks in advance !

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-Integration-tp5615849p5615849.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

---

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Excel-Integration-tp5615849p5616019.html
To unsubscribe from Excel Integration, click here.
NAML

---

View this message in context: Re: Excel Integration
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

[Deepanshu Bhalla]

CONTENTS DELETED

The author has deleted this message.

[Matthew Fuller-Tyszkiewicz]

Hi,

I'm away on leave until Monday 16th April. I will not have access to my email account while away, and willl endeavour to return your emails shortly after my return.

Cheers,
Matt.

 

[Jon K Peck]

There are some small examples in the Basic help (search for create via the Find tab), but "not succeeded" leaves a lot of room for guesswork.  I usually work from Statistics to Excel rather than the other way round, which is what the example I cited does, but either can work.  The code in the export script shows that you can do pretty much anything from Basic in Statistics that you could do directly in Excel.  The VBA environment in Excel has more features than the SPSS Basic editor, so sometimes I use both.  (But I mostly avoid Basic altogether.)

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Deepanshu Bhalla <[hidden email]>
To:        [hidden email]
Date:        04/05/2012 09:34 AM
Subject:        Re: [SPSSX-L] Excel Integration
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks a ton Jon .. I have downloaded the script and execute it from the
utilities > Run script menu. I found it very useful when exporting tables to
export but not serving my purpose .

Requirement : I use Aggregate function nd create a new dataset containing
the aggregated variables and export the sav file to excel file format. After
that i do some merging , bordering and color formating to the sheet and
break the sheet into lots of workbooks (depending on the number of options
the break variables have ) using *excel macro* . I want all these work
within spss .

David Marso suggested me run SPSS from your Excel macro using references to
the SPSS Com Objects. ( Thanks DM for giving me the way for this problem  )
. I added SPSS Statistics type library and explore create object methods to
control spss from excel but not succeeded . Is there any example for the
same in web so i can read and found solution for the problem myself ??

Thanks David , Jon , Art Kendall once again for help !

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-Integration-tp5615849p5620681.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[susiemelindy]

simple........u copy and paste the excel into spss.  think about it.

[David Marso]

You are missing the point ;-(
Your "thinking about it" subsumes only the most trivial conception of "integration"!!
---

susiemelindy wrote

simple........u copy and paste the excel into spss.  think about it.

[susiemelindy]

Hit the paste button and not the open.  I'm doing this in a class right now and we had to get our information from excel.  

[David Marso]

Surely you mean GET TRANSLATE not PASTE! But you haven't read the original thread so it is totally irrelevant in this case anyway!