export to excel problem using macro & oms

[Volker, Gerard]

Hi all,

 

I've written the following macro to generate 2 tables for several variables and exporting them to excel in different sheets:

 

GET
  FILE='C:\spss\test.sav'.

 

*MACRO FOR CROSS TABLES.
DEFINE MakeCrossTab (!positional !charend ('.')).
OMS
/SELECT TABLES
/IF COMMANDS=['Crosstabs' 'CTables'] SUBTYPES=['Case Processing Summary']
/DESTINATION VIEWER=NO.

WEIGHT OFF.
CTABLES
  /VLABELS VARIABLES= !1 Size3 DISPLAY=DEFAULT
  /TABLE !1 [C][COUNT F40.0] by Size3 [C]
  /CATEGORIES VARIABLES=!1 Size3 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES POSITION=AFTER MISSING=EXCLUDE
  /TITLES
    TITLE=!QUOTE( !CONCAT('Frequencies of  ', !1, ' by size of enterprise'))
    CAPTION = 'Unweighted results. Source: Survey 2009). Processing: )DATE'.

WEIGHT BY Weight.
 CTABLES
  /VLABELS VARIABLES=!1 Size3  DISPLAY=DEFAULT
  /TABLE !1 [C] [COLPCT.COUNT PCT40.0] BY Size3 [C]
  /CATEGORIES VARIABLES=!1 Size3 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES POSITION=AFTER MISSING=EXCLUDE
  /TITLES
    TITLE=!QUOTE( !CONCAT('Distribution of  ', !1, ' by size of enterprise'))
    CAPTION = 'Weighted3 results. Source: Survey 2009). Processing: )DATE'.
OUTPUT EXPORT
  /CONTENTS  EXPORT=VISIBLE  LAYERS=PRINTSETTING  MODELVIEWS=PRINTSETTING
  /XLS  DOCUMENTFILE='C:\spss\test.xls'
     OPERATION=CREATESHEET  SHEET=!QUOTE(!1)
     LOCATION=STARTCELL('A2')  NOTESCAPTIONS=YES.

OMSEND.

OUTPUT CLOSE * .
OUTPUT NEW .

!ENDDEFINE.

 

MakeCrossTab var1.
MakeCrossTab var2.

etc.

 

The macro works fine but some of the tables get screwed up in Excel (the tableslook gets lost or worse the table doesn't appear at all in a sheet) or even earlier in the output (tablelook gets lost).

If I run the macro again it creates the same problems but this time with different tables in different sheets. So it is a very unstable procedure.

 

Is there a solution to solve this problem or a better way of doing this, perhaps via a python code? 

 

Thanks,

Gerard Volker

Statistical analyst