Export output to multiple excel worksheets

[J P]

Hello,

I have a macro that exports output to an excel workbook, 150 separate outputs. Currently I can send output to 150 separate workbooks, but the person I am writing this for would prefer one workbook, with 150 (named) worksheets.

Is that possible?? is there a way to name the worksheet so it does not overwrite the previous one? I'm already inserting a name for the workbook into the Export command. I'm probably just not looking in the right place, but time is running short.

If not, anyone have a macro for importing multiple worksheets into one workbook? 

Thanks in advance,

John

===================== 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

[Albert-Jan Roskam-2]

Hi,

get file = "C:\Program Files\IBM\SPSS\Statistics\22\Samples\English\Employee data.sav".
 
ctables
 /table minority by prevexp
 /table jobcat by salary.
 
spssinc modify output tables
/if subtype="'custom table'" process=preceding
/custom function="customoutputfunctions.excelexport(file='c:/temp/Tables_SPSS.xls', sheet='output_#', action='CreateWorksheet')".

------------------------------
On Tue, Aug 5, 2014 6:46 PM CEST J P wrote:

>Hello,
>
>I have a macro that exports output to an excel workbook, 150 separate outputs. Currently I can send output to 150 separate workbooks, but the person I am writing this for would prefer one workbook, with 150 (named) worksheets.
>
>
>Is that possible?? is there a way to name the worksheet so it does not overwrite the previous one? I'm already inserting a name for the workbook into the Export command. I'm probably just not looking in the right place, but time is running short.
>
>
>If not, anyone have a macro for importing multiple worksheets into one workbook? 
>
>Thanks in advance,
>John
>
>
>=====================
>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

[Jon K Peck]

There is a Basic script, ExportTablestoExcelFiles available from the SPSS Community website (www.ibm.com/developerworks/spssdevcentral) via the Downloads for SPSS Statistics.  It has a dialog box for details, but it includes a choice of one file or separate files.  The way it actually works is to write a bunch of separate files and then use Excel automation to merge the files.  I'm dubious that 150 in one file is practical, but in principle it would work.

The OUTPUT EXPORT command does allow you to control the sheetname for a specific export, but it doesn't let you select particular tables.

The SPSSINC MODIFY OUTPUT extension command has an excelexport custom function that can be used along with the selection capabilities of MODIFY OUTPUT to export only a subset of tables as identified by their OMS subtype, but to get each on a separate sheet, this would need to be executed using the PREVIOUS option after each procedure.






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




From:        J P <[hidden email]>
To:        [hidden email]
Date:        08/05/2014 10:47 AM
Subject:        [SPSSX-L] Export  output to multiple excel worksheets
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello,

I have a macro that exports output to an excel workbook, 150 separate outputs. Currently I can send output to 150 separate workbooks, but the person I am writing this for would prefer one workbook, with 150 (named) worksheets.

Is that possible?? is there a way to name the worksheet so it does not overwrite the previous one? I'm already inserting a name for the workbook into the Export command. I'm probably just not looking in the right place, but time is running short.

If not, anyone have a macro for importing multiple worksheets into one workbook?

Thanks in advance,
John
===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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