Exporting to multiple Excel sheets

[igaboury]

Hi,

I'm trying to export SPSS outputs generated within a macro that loops from 1
to x. I'm trying to generate a new sheet name with the !CONCAT command but
somehow it doesn't create a new sheet name... What's wrong with it?


DEFINE !rapportAA (nogmf= !TOKENS(1))
 
  !DO !i = 1 !TO !nogmf.

COMPUTE filter_$=(gmfqc = !i).
FILTER BY filter_$.
EXECUTE.

FREQUENCIES VARIABLES=var
  /ORDER=ANALYSIS.

OUTPUT EXPORT
  /CONTENTS EXPORT = ALL
  /XLS  DOCUMENTFILE= ' Documents/GMF.xls'
  OPERATION= CREATESHEET
  SHEET = '!CONCAT("GMF", !i)'.  
 
  OUTPUT CLOSE *.

FILTER OFF.
!DOEND.
!ENDDEFINE.

Thank you!




--
Sent from: http://spssx-discussion.1045642.n5.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

[Kornbrot, Diana]

I’d recommend export all .spv files before you lose access, any or all of these formats

.pdf means you will always have access to read

Html is also very useful - it is easy to scan through and add to presentations

.xls or .xlsx is often convenient if you want to extract tables or figures for other documents

You can set SPS so output includes log file, so one always knows setting s for analyses

I NEVER use .spv. what is it good  for?

best

Diana

On 5 May 2020, at 22:23, igaboury <[hidden email]> wrote:

Hi,

I'm trying to export SPSS outputs generated within a macro that loops from 1
to x. I'm trying to generate a new sheet name with the !CONCAT command but
somehow it doesn't create a new sheet name... What's wrong with it?


DEFINE !rapportAA (nogmf= !TOKENS(1))

 !DO !i = 1 !TO !nogmf.

COMPUTE filter_$=(gmfqc = !i).
FILTER BY filter_$.
EXECUTE.

FREQUENCIES VARIABLES=var
 /ORDER=ANALYSIS.

OUTPUT EXPORT
 /CONTENTS EXPORT = ALL
 /XLS  DOCUMENTFILE= ' Documents/GMF.xls'
 OPERATION= CREATESHEET
 SHEET = '!CONCAT("GMF", !i)'.   

 OUTPUT CLOSE *.

FILTER OFF.
!DOEND.
!ENDDEFINE.

Thank you!




--
Sent from: http://spssx-discussion.1045642.n5.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

____________
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
[phone]

[phone]
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot/
skype:  kornbrotme

Save our in-boxes! http://emailcharter.org
 __________________

____________
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
[phone]

[phone]
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot/
skype:  kornbrotme

Save our in-boxes! http://emailcharter.org
 __________________

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

Your OUTPUT EXPORT command needs to specify the sheet name like this.

  SHEET = !CONCAT('"', "GMF", !i, '"').   

You might want to consider using SPLIT FILES SEPARATE for this instead of all those filters.

With that, you might consider using the SPSSINC MODIFY OUTPUT extension command with the included custom function that exports all tables of selected types to Excel sheets.  Here is an example from its doc.

# Export all the custom tables in the Viewer to separate sheets named table1, table2, ...
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Custom Table'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
  sheet='table#',action='CreateWorksheet')".

If you don't already have this installed, you can add it from the Extensions > Extension Hub menu.

On Sat, May 9, 2020 at 7:32 PM igaboury <[hidden email]> wrote:

Hi,

I'm trying to export SPSS outputs generated within a macro that loops from 1
to x. I'm trying to generate a new sheet name with the !CONCAT command but
somehow it doesn't create a new sheet name... What's wrong with it?


DEFINE !rapportAA (nogmf= !TOKENS(1))

  !DO !i = 1 !TO !nogmf.

COMPUTE filter_$=(gmfqc = !i).
FILTER BY filter_$.
EXECUTE.

FREQUENCIES VARIABLES=var
  /ORDER=ANALYSIS.

OUTPUT EXPORT
  /CONTENTS EXPORT = ALL
  /XLS  DOCUMENTFILE= ' Documents/GMF.xls'
  OPERATION= CREATESHEET
  SHEET = '!CONCAT("GMF", !i)'.   

  OUTPUT CLOSE *.

FILTER OFF.
!DOEND.
!ENDDEFINE.

Thank you!




--
Sent from: http://spssx-discussion.1045642.n5.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

--

Jon K Peck
[hidden email]

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

[Javier Figueroa]

Greetings from Guatemala, I hope you are well and with much encouragement, to get ahead with this world situation (COVID-19)

Thank you, I was asking for this for a long time.
but I have a question and it is the following:

I have an output with 100 boxes that is the result of 100 questions, as identified by no. of pictures that I want on each sheet?

Thank you.

Sincerely,

JF Javier Figueroa

El dom., 10 may. 2020 a las 9:17, Jon Peck (<[hidden email]>) escribió:

Your OUTPUT EXPORT command needs to specify the sheet name like this.

  SHEET = !CONCAT('"', "GMF", !i, '"').   

You might want to consider using SPLIT FILES SEPARATE for this instead of all those filters.

With that, you might consider using the SPSSINC MODIFY OUTPUT extension command with the included custom function that exports all tables of selected types to Excel sheets.  Here is an example from its doc.

# Export all the custom tables in the Viewer to separate sheets named table1, table2, ...
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Custom Table'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
  sheet='table#',action='CreateWorksheet')".

If you don't already have this installed, you can add it from the Extensions > Extension Hub menu.

On Sat, May 9, 2020 at 7:32 PM igaboury <[hidden email]> wrote:

Hi,

I'm trying to export SPSS outputs generated within a macro that loops from 1
to x. I'm trying to generate a new sheet name with the !CONCAT command but
somehow it doesn't create a new sheet name... What's wrong with it?


DEFINE !rapportAA (nogmf= !TOKENS(1))

  !DO !i = 1 !TO !nogmf.

COMPUTE filter_$=(gmfqc = !i).
FILTER BY filter_$.
EXECUTE.

FREQUENCIES VARIABLES=var
  /ORDER=ANALYSIS.

OUTPUT EXPORT
  /CONTENTS EXPORT = ALL
  /XLS  DOCUMENTFILE= ' Documents/GMF.xls'
  OPERATION= CREATESHEET
  SHEET = '!CONCAT("GMF", !i)'.   

  OUTPUT CLOSE *.

FILTER OFF.
!DOEND.
!ENDDEFINE.

Thank you!




--
Sent from: http://spssx-discussion.1045642.n5.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

--

Jon K Peck
[hidden email]

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

I don't understand what the question is.

On Wed, May 13, 2020 at 9:15 AM Javier Figueroa <[hidden email]> wrote:

Greetings from Guatemala, I hope you are well and with much encouragement, to get ahead with this world situation (COVID-19)

Thank you, I was asking for this for a long time.
but I have a question and it is the following:

I have an output with 100 boxes that is the result of 100 questions, as identified by no. of pictures that I want on each sheet?

Thank you.

Sincerely,

JF Javier Figueroa

El dom., 10 may. 2020 a las 9:17, Jon Peck (<[hidden email]>) escribió:

Your OUTPUT EXPORT command needs to specify the sheet name like this.

  SHEET = !CONCAT('"', "GMF", !i, '"').   

You might want to consider using SPLIT FILES SEPARATE for this instead of all those filters.

With that, you might consider using the SPSSINC MODIFY OUTPUT extension command with the included custom function that exports all tables of selected types to Excel sheets.  Here is an example from its doc.

# Export all the custom tables in the Viewer to separate sheets named table1, table2, ...
SPSSINC MODIFY OUTPUT TABLES
/IF SUBTYPE="'Custom Table'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
  sheet='table#',action='CreateWorksheet')".

If you don't already have this installed, you can add it from the Extensions > Extension Hub menu.

On Sat, May 9, 2020 at 7:32 PM igaboury <[hidden email]> wrote:

Hi,

I'm trying to export SPSS outputs generated within a macro that loops from 1
to x. I'm trying to generate a new sheet name with the !CONCAT command but
somehow it doesn't create a new sheet name... What's wrong with it?


DEFINE !rapportAA (nogmf= !TOKENS(1))

  !DO !i = 1 !TO !nogmf.

COMPUTE filter_$=(gmfqc = !i).
FILTER BY filter_$.
EXECUTE.

FREQUENCIES VARIABLES=var
  /ORDER=ANALYSIS.

OUTPUT EXPORT
  /CONTENTS EXPORT = ALL
  /XLS  DOCUMENTFILE= ' Documents/GMF.xls'
  OPERATION= CREATESHEET
  SHEET = '!CONCAT("GMF", !i)'.   

  OUTPUT CLOSE *.

FILTER OFF.
!DOEND.
!ENDDEFINE.

Thank you!




--
Sent from: http://spssx-discussion.1045642.n5.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

--

Jon K Peck
[hidden email]

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

--

Javier Figueroa
Procesamiento y Análisis de bases de datos

Cel: 5927-4748 / 4970-1940

Casa: 2289-0184

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