Saving to different worksheets in the same Excel workbook?

[Moser, Gary]

Hello List,

 

I don't suppose there's any way to save multiple data sets out (like
aggregate outfiles) onto separate pages in the same Excel workbook? I
intend to develop syntax that extracts, manipulates, and aggregates data
into various outfiles. Each outfile will "feed" a graph or chart
developed in Excel, and can be updated by refreshing the data. So, Excel
will be the 'face', and the reference data will be replaced weekly to
update the chart.

 

2 questions in particular that I welcome insight and opinions on:

 

1.) Is it possible to save data to multiple worksheets in the same
workbook?

 

2.) What are your impressions regarding the approach I described for
getting data into a presentable format?

(We have custom tables, but Excel seems to be the superior tool for
flexibility and precision of formatting, therefore we chose Excel as the
'face' of the data).

 

Unrelated insight and opinions are also welcome.

 

Gary Moser

Research Analyst

Institutional Research

Dominican University of California

Ph: 415.482.2400

Fax:[phone]

Email: [hidden email] <mailto:[hidden email]>

 

 

[Hal 9000]

Thank you very much Siraj and also Richard for passing on this advice...I
missed this conversation before I posted my question. For others' benefit,
here's the answer in a nutshell:


SAVE TRANSLATE

/CONNECT='DSN=Excel Files;DBQ=C:\Documents and Settings\Desktop\SPSS
Testing\Avaya.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'

/TABLE="Quarter1"

/TYPE=ODBC

/REPLACE.

exe.



On 6/27/06, Moser, Gary <[hidden email]> wrote:

>
> Hello List,
>
>
>
> I don't suppose there's any way to save multiple data sets out (like
> aggregate outfiles) onto separate pages in the same Excel workbook? I
> intend to develop syntax that extracts, manipulates, and aggregates data
> into various outfiles. Each outfile will "feed" a graph or chart
> developed in Excel, and can be updated by refreshing the data. So, Excel
> will be the 'face', and the reference data will be replaced weekly to
> update the chart.
>
>
>
> 2 questions in particular that I welcome insight and opinions on:
>
>
>
> 1.) Is it possible to save data to multiple worksheets in the same
> workbook?
>
>
>
> 2.) What are your impressions regarding the approach I described for
> getting data into a presentable format?
>
> (We have custom tables, but Excel seems to be the superior tool for
> flexibility and precision of formatting, therefore we chose Excel as the
> 'face' of the data).
>
>
>
> Unrelated insight and opinions are also welcome.
>
>
>
> Gary Moser
>
> Research Analyst
>
> Institutional Research
>
> Dominican University of California
>
> Ph: 415.482.2400
>
> Fax:[phone]
>
> Email: [hidden email] <mailto:[hidden email]>
>
>
>
>
>

[Oliver, Richard]

Yes, the trick is to use SAVE TRANSLATE /TYPE=ODBC, instead of TYPE=XLS. Each sheet in the Excel workbook can then be treated as a separate "table" in the "database". This requires having the necessary ODBC driver for Excel, but I think that's automatically installed with more recent versions of Microsoft operating systems and should also be available in the Microsoft Data Access Pack that ships with SPSS.
 
FWIW, I think Ray Levesque may have an example of this on his web site.

________________________________

From: SPSSX(r) Discussion on behalf of Hal 9000
Sent: Wed 6/28/2006 6:28 PM
To: [hidden email]
Subject: Re: Saving to different worksheets in the same Excel workbook?



Thank you very much Siraj and also Richard for passing on this advice...I
missed this conversation before I posted my question. For others' benefit,
here's the answer in a nutshell:


SAVE TRANSLATE

/CONNECT='DSN=Excel Files;DBQ=C:\Documents and Settings\Desktop\SPSS
Testing\Avaya.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'

/TABLE="Quarter1"

/TYPE=ODBC

/REPLACE.

exe.



On 6/27/06, Moser, Gary <[hidden email]> wrote:

>
> Hello List,
>
>
>
> I don't suppose there's any way to save multiple data sets out (like
> aggregate outfiles) onto separate pages in the same Excel workbook? I
> intend to develop syntax that extracts, manipulates, and aggregates data
> into various outfiles. Each outfile will "feed" a graph or chart
> developed in Excel, and can be updated by refreshing the data. So, Excel
> will be the 'face', and the reference data will be replaced weekly to
> update the chart.
>
>
>
> 2 questions in particular that I welcome insight and opinions on:
>
>
>
> 1.) Is it possible to save data to multiple worksheets in the same
> workbook?
>
>
>
> 2.) What are your impressions regarding the approach I described for
> getting data into a presentable format?
>
> (We have custom tables, but Excel seems to be the superior tool for
> flexibility and precision of formatting, therefore we chose Excel as the
> 'face' of the data).
>
>
>
> Unrelated insight and opinions are also welcome.
>
>
>
> Gary Moser
>
> Research Analyst
>
> Institutional Research
>
> Dominican University of California
>
> Ph: 415.482.2400
>
> Fax:[phone]
>
> Email: [hidden email] <mailto:[hidden email]>
>
>
>
>
>

[rwvanderlaak]

CONTENTS DELETED

The author has deleted this message.

[Parveen Sharma]

Hi Roland,

Try this:

SAVE TRANSLATE
/CONNECT='DSN=Excel Files;DBQ=C:\Documents and
Settings\Owner\Desktop\tes.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
/TABLE="mysheet4"
/KEEP t1 t2
/TYPE=ODBC
/REPLACE.


************************* t1 t2 are the variables you want to write out to
mysheet4 in excel file tes.xls*************.
So you just have to add
/KEEP
to the already mentioned syntax with the list of variables you want to write
out to a specific sheet.

Thanks,
Parveen



On 6/30/06, Roland van der Laak <[hidden email]> wrote:

>
> Hi all,
>
> I've tried this solution but did not manage to write different variable
> list
> to different sheets within one excel file, has anybody got an example that
> would do that?
>
> thanks
> Roland
>
> ----- Original Message -----
> From: "Oliver, Richard" <[hidden email]>
> Newsgroups: bit.listserv.spssx-l
> To: <[hidden email]>
> Sent: Thursday, June 29, 2006 4:55 AM
> Subject: Re: Saving to different worksheets in the same Excel workbook?
>
>
> Yes, the trick is to use SAVE TRANSLATE /TYPE=ODBC, instead of TYPE=XLS.
> Each sheet in the Excel workbook can then be treated as a separate "table"
> in the "database". This requires having the necessary ODBC driver for
> Excel,
> but I think that's automatically installed with more recent versions of
> Microsoft operating systems and should also be available in the Microsoft
> Data Access Pack that ships with SPSS.
>
> FWIW, I think Ray Levesque may have an example of this on his web site.
>
> ________________________________
>
> From: SPSSX(r) Discussion on behalf of Hal 9000
> Sent: Wed 6/28/2006 6:28 PM
> To: [hidden email]
> Subject: Re: Saving to different worksheets in the same Excel workbook?
>
>
>
> Thank you very much Siraj and also Richard for passing on this advice...I
> missed this conversation before I posted my question. For others' benefit,
> here's the answer in a nutshell:
>
>
> SAVE TRANSLATE
>
> /CONNECT='DSN=Excel Files;DBQ=C:\Documents and Settings\Desktop\SPSS
> Testing\Avaya.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
>
> /TABLE="Quarter1"
>
> /TYPE=ODBC
>
> /REPLACE.
>
> exe.
>
>
>
> On 6/27/06, Moser, Gary <[hidden email]> wrote:
> >
> > Hello List,
> >
> >
> >
> > I don't suppose there's any way to save multiple data sets out (like
> > aggregate outfiles) onto separate pages in the same Excel workbook? I
> > intend to develop syntax that extracts, manipulates, and aggregates data
> > into various outfiles. Each outfile will "feed" a graph or chart
> > developed in Excel, and can be updated by refreshing the data. So, Excel
> > will be the 'face', and the reference data will be replaced weekly to
> > update the chart.
> >
> >
> >
> > 2 questions in particular that I welcome insight and opinions on:
> >
> >
> >
> > 1.) Is it possible to save data to multiple worksheets in the same
> > workbook?
> >
> >
> >
> > 2.) What are your impressions regarding the approach I described for
> > getting data into a presentable format?
> >
> > (We have custom tables, but Excel seems to be the superior tool for
> > flexibility and precision of formatting, therefore we chose Excel as the
> > 'face' of the data).
> >
> >
> >
> > Unrelated insight and opinions are also welcome.
> >
> >
> >
> > Gary Moser
> >
> > Research Analyst
> >
> > Institutional Research
> >
> > Dominican University of California
> >
> > Ph: 415.482.2400
> >
> > Fax:[phone]
> >
> > Email: [hidden email] <mailto:[hidden email]>
> >
> >
> >
> >
> >
>

--
__________________________
Parveen Sharma
Ph: 9899790674 (Delhi)