SPSS Statistics

SPSS Statistics

Your hub for statistical analysis, data management, and data documentation. Connect, learn, and share with your peers! 

 View Only
Back to discussions
Expand all | Collapse all

Code to export SPSS output data into existing worksheets within a macro-enabled Excel workbook works for SPSS 26 but not SPSS 27?

  • 1.  Code to export SPSS output data into existing worksheets within a macro-enabled Excel workbook works for SPSS 26 but not SPSS 27?

    Posted Fri June 25, 2021 02:00 PM

    Would really appreciate some help on this!

    Our organisation recently moved from SPSS Statistics Version 26 to Version 27. A critical part of our work is exporting SPSS tables into existing Excel macro enabled workbooks with blank tables/charts etc. These are populated via the SPSS output data being exported directly into hidden sheets within the workbook.

    This has worked perfectly in previous versions of SPSS. However the new version does not seem to allow the Output Export function to export data into an existing Excel Macro-enabled workbook (.xlsm)?

    When you try to do so the error message appears "Cannot create this file. Please verify the correct file name was given. "

    The syntax below works fine in Version 26. However it doesn't work in Version 27 unless code is modified to create a new Excel .xlsm file, or export data into an existing standard Excel .xlsx workbook, neither of which are suitable for our needs.

    Example syntax of problem below:

    *Table for running out Department and Business Area (Bus_area) counts

    CTABLES

    /VLABELS VARIABLES=Department Bus_area DISPLAY=LABEL

    /TABLE Bus_area [C] > Department [C]

    /CATEGORIES VARIABLES=Department Bus_area ORDER=A KEY=VALUE EMPTY=EXCLUDE

    /CRITERIA CILEVEL=95

    /TITLES TITLE = "Org name".

    OUTPUT EXPORT

    /CONTENTS EXPORT=VISIBLE LAYERS=PRINTSETTING MODELVIEWS=PRINTSETTING

    /XLSM DOCUMENTFILE='C:\Documents\Business Area Report.xlsm'

    OPERATION=MODIFYSHEET SHEET='Data'

    LOCATION=STARTCELL('A1') NOTESCAPTIONS=YES.

    OUTPUT CLOSE ALL.

    A previous user reported a similar fault for SPSS 21 - so it may again require a Fix Pack update for SPSS 27?

    https://www.ibm.com/support/pages/apar/PM88119

    Thanks in advance for your help, be great to know what the issue is!



    ------------------------------
    Conán McCaughey
    ------------------------------

    #SPSSStatistics


  • 2.  RE: Code to export SPSS output data into existing worksheets within a macro-enabled Excel workbook works for SPSS 26 but not SPSS 27?

    Posted Mon June 28, 2021 05:56 PM
    Hmm, I have 27 FP 1 installed (on my Mac), and this worked fine.
    Could it be a permission problem?  This error tends to pop up if the file is not writeable for some reason.

    ------------------------------
    LOUIS Kittock
    ------------------------------

    Original Message