Export to Excel

[Ratna Wynn-2]

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
 
Thanks,
 
Ratna

[Jon K Peck]

There are several options - see below.

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




From:        Ratna Wynn <[hidden email]>
To:        [hidden email]
Date:        11/04/2011 11:24 AM
Subject:        [SPSSX-L] Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
>>>Newer versions of Statistics include an option for CTABLES to put the significance markers in the main table.  This, however, uses APA style, and lots of users don't like it.  An alternative is to use the SPSSINC MERGE TABLES extension command to put the significance results into the main table in the more traditional style.  For many table structures, this is very easy to do.  For some complicated layouts, it may require writing a small custom function to be used by MERGE TABLES to match up the rows and columns of the two tables.

Once merged, of course, the multiple-sheet problem goes away.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
>>>If you simply run an OUTPUT CLOSE command after each export, you will get a new empty Viewer window for the next batch of output.
 
HTH,
Jon Peck

Thanks,
 
Ratna

[Rick Oliver-3]

Oops, that should be "output new" rather than "output file new"

Rick Oliver
Senior Information Developer
Business Analytics (SPSS)
E-mail: [hidden email]



From:        Rick Oliver/Chicago/IBM
To:        Ratna Wynn <[hidden email]>
Cc:        [hidden email]
Date:        11/04/2011 12:33 PM
Subject:        Re: Export to Excel

---

I don't think you need a script to do this. You should be able to do it all in syntax.

output file new.
[commands to produce output]
output export....[excel format, workbook name, sheet name...]
output file new.
[commands to produce output]
output export...




From:        Ratna Wynn <[hidden email]>
To:        [hidden email]
Date:        11/04/2011 12:21 PM
Subject:        Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
 
Thanks,
 
Ratna

[Rick Oliver-3]

I don't think you need a script to do this. You should be able to do it all in syntax.

output file new.
[commands to produce output]
output export....[excel format, workbook name, sheet name...]
output file new.
[commands to produce output]
output export...



From:        Ratna Wynn <[hidden email]>
To:        [hidden email]
Date:        11/04/2011 12:21 PM
Subject:        Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
 
Thanks,
 
Ratna

[Ratna Wynn-2]

Thank you - I think this can work. Is there anyway other than suppressing output using OMS entry that I can tell SPSS to only export the pivot tables. I want the notes/logs to show in the output file but do not want them in the exported output.

On Fri, Nov 4, 2011 at 1:41 PM, Rick Oliver <[hidden email]> wrote:

Oops, that should be "output new" rather than "output file new"

Rick Oliver
Senior Information Developer
Business Analytics (SPSS)
E-mail: [hidden email]



From:        Rick Oliver/Chicago/IBM
To:        Ratna Wynn <[hidden email]>
Cc:        [hidden email]
Date:        11/04/2011 12:33 PM
Subject:        Re: Export to Excel

---

I don't think you need a script to do this. You should be able to do it all in syntax.

output file new.
[commands to produce output]
output export....[excel format, workbook name, sheet name...]
output file new.
[commands to produce output]
output export...




From:        Ratna Wynn <[hidden email]>
To:        [hidden email]
Date:        11/04/2011 12:21 PM
Subject:        Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
 
Thanks,
 
Ratna

[Jon K Peck]

The export mechanisms give you a choice of exporting only visible items, so  you could set your preferences to hide all the items you don't want to export and then change them back after the exports.  Alternatively you could use the SPSSINC MODIFY OUTPUT command to hide all these items before the export, e.g.
SPSSINC MODIFY OUTPUT LOGS NOTES TEXTS WARNINGS
/IF PROCESS=ALL
/VISIBILITY VISIBLE=FALSE.


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




From:        Ratna Wynn <[hidden email]>
To:        [hidden email]
Date:        11/04/2011 11:55 AM
Subject:        Re: [SPSSX-L] Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thank you - I think this can work. Is there anyway other than suppressing output using OMS entry that I can tell SPSS to only export the pivot tables. I want the notes/logs to show in the output file but do not want them in the exported output.

On Fri, Nov 4, 2011 at 1:41 PM, Rick Oliver <oliverr@...> wrote:
Oops, that should be "output new" rather than "output file new"

Rick Oliver
Senior Information Developer
Business Analytics (SPSS)
E-mail: oliverr@...



From:        Rick Oliver/Chicago/IBM
To:        Ratna Wynn <ratnawynn@...>
Cc:        [hidden email]
Date:        11/04/2011 12:33 PM
Subject:        Re: Export to Excel

---

I don't think you need a script to do this. You should be able to do it all in syntax.

output file new.
[commands to produce output]
output export....[excel format, workbook name, sheet name...]
output file new.
[commands to produce output]
output export...




From:        Ratna Wynn <ratnawynn@...>
To:        [hidden email]
Date:        11/04/2011 12:21 PM
Subject:        Export to Excel
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi
 
I have a lot of syntax I am running and would like each block of syntax to export the output to a separate sheet within an excel workbood. I found the a script file on dev central (ExportTablesToExcelFiles.wwd) that exports output to individual excel tabs in excel. This works great but I run into a problem when I run custom tables.  If I run statistical tests, the output splits out the statistical testing into a separate sheet and the related data into another sheet .
 
Does anyone know how to change this so it does not do this and keeps both the table and related statistical testing on the same sheet in excel.
 
Another alternate would be if I could ask via syntax that the last output run be exported to a separate sheet. The only syntax I found export all output or only all visible output. Any help would be appreciated.
 
Thanks,
 
Ratna