I mostly export output windows to Excel files. However Excel has a limit up to 31 characters for a sheet name. If the name is longer than 31 SPSS throws an error and the output doesn't export elements. So I created a macro to avoid this situation.
Code:
define ExportToExcel (!positional !charend('/')
/!positional !default("test.xlsx") !cmdend)
/*CLEANING
output modify
/select all except (charts tables models texts pagetitles)
/deleteobject delete = yes.
/*CUTTING SHEET NAME TO 31 CHARS
!let !strSheetName = !unquote(!1)
!if (!length(!strSheetName) > 31) !then
!let !strSheetName = !substr(!strSheetName,1,31)
!ifend
!let !strSheetName = !quote(!strSheetName)
/*ADDING .XLSX TO FILE NAME ENDING IF NOT PRESENT
!let !strFileName = !unquote(!2)
!if (!index(!strFileName,".xlsx") = 0) !then
!let !strFileName = !concat(!strFileName,".xlsx")
!ifend
!let !strFileName = !quote(!strFileName)
/*EXPORT
output export
/contents export = visible layers = all modelviews = printsetting
/xlsx documentfile = !strFileName
operation = createsheet
sheet = !strSheetName
location = lastcolumn notescaptions = yes.
!enddefine.
For testing purposes I ran this macro twice (with different sheet names of course):
* WORKS FINE.
ExportToExcel "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA".
* ERROR 293:
The position and length given in a macro SUBSTR function are inconsistent with the string argument.
The null string has been used for the result.
ExportToExcel "Abcdefgh".
I totally don't understand why second instance returns an error. Do you have any idea?
------------------------------
Konrad Gałuszko
------------------------------
#SPSSStatistics