How can I read in multiple excel files in SPSS using a macro?

I'm trying to write a Macro to import 15 files, all in the same format. The name format is the "monyy PSF Extract". So I can use the code below to read in the 1 file for Nov11. I've tried to find a way of using the macro to read in also the other 14 files. I can't seem to make it work. I'm new to SPSS - I knew how to do this in SAS. I also want to set the dataset created to be named monyy. I will also want to rename some variables as original_name_monyy.

Can someone help me on this please?It is drivign me nuts!

define !XLSFILE() !quote(!con("S:\Credit Risk\Credit Risk\Elisabeth\",!unquote(!eval(!cq)), ".xlsx")) !enddefine.
define !cq(mon = !DEFAULT ("Nov11") !token(1) /name = !DEFAULT ("PSF Extract") !TOKENS(2)) !quo(!con(!unq(!mon),!unq(" "), !unq(!name))) !enddefine.


/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.

EXECUTE.

DATASET NAME  test WINDOW=FRONT.

Solution

You can pass a list of file name pre-fixes in a macro call and loop through loading the files. Below is how I would approach this. It is a bit restrictive in that you need to pass the list of months, but it is fairly trivial a task.

*******************************************************.
DEFINE !XLSFILE(location = !TOKENS(1)
                /names = !CMDEND).

!DO !monthfile !IN (!names)

!LET !XLSFILE = !QUOTE(!CONCAT(!UNQUOTE(!location),!monthfile," PSF Extract.xlsx"))

/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
*Name dataset.
dataset name !monthfile.

**PLACE RENAME COMMANDS HERE.
*Example changing [XVAR1] and [XVAR2] to [XVAR1_monyy] and [XVAR2_monyy].
rename variables (XVAR1 = !CONCAT("XVAR1","_",!monthfile))
(XVAR2 = !CONCAT("XVAR2","_",!monthfile)).

!DOEND.

*now do whatever you want with the datasets, eg add files them together.
!ENDDEFINE.
*******************************************************.

*call the macro.
set mprint on.
!XLSFILE location = "S:\Credit Risk\Credit Risk\Elisabeth\"
names = Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 Dec11.