Process All .xls Files in a Given Folder

* This is "Process all xls files in folder part 2.SPS" 

* The script "Process all xls files in folder part 1.SBS" must be run before this syntax file.
* rlevesque@videotron.ca.

*/////////////////////////////.
SET MPRINT=no.
* Change next path to name of folder where sav files are to be saved.
DEFINE !outdir()'c:\\temp\\'
!ENDDEFINE.


* Next macro does whatever is required on a given excel file before 
	saving it as a *.sav file. This macro is called at the end of this file.
*//////////////////.
DEFINE !psf2 (fnumb=!TOKENS(1) /fname=!CMDEND )
!LET !infile =!QUOTE(!CONCAT(!fname,'.xls'))

/* Next command assumes SPSS version 10 or above */.
/* Change the sheet name as required */.
GET DATA
  /TYPE=XLS
  /FILE=!infile
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on .

/*************************************************************/.
/* Insert here any set of commands you need           */.
/*************************************************************/.

/* Next line assumes the sav file name will be the same as that of 
	the corresponding excel file name. If you prefer to name the file
	with consecutive numbers, replace fname by fnumb in the next 
	macro command*/.

!LET !outfile=!QUOTE(!CONCAT(!UNQUOTE(!EVAL(!outdir)),!fname,'.sav'))
SAVE OUTFILE=!outfile.

!ENDDEFINE.
*/////////////////////////////.


* Read all the xls file names.
FILE TYPE MIXED FILE=!indir + "list.txt" RECORD=REC_ID 10-12 (A).
RECORD TYPE 'XLS'.
DATA LIST FIXED  / fname 1-8(A).
END FILE TYPE.
LIST.

* Write a syntax file which will call the above macro for each one of the xls files.
COMPUTE fnumb=$CASENUM.
FORMATS fnumb (F8.0).
WRITE OUTFILE=!indir + "macro calls.sps"  /"!psf2 fnumb=" fnumb " fname=" fname.
EXECUTE.

* Next command ensures macro expansions will show up in the Output window.
SET MPRINT=yes.
* Call the syntax file just created.
INCLUDE FILE=!indir + "macro calls.sps".