Process All .xls Files in a Given Folder (script)

'BEGIN DESCRIPTION
' This is used in conjuntion with a syntax file to process all xls files in a given folder
' (it would be easy to change it to process other types of files)
'END DESCRIPTION

'ASSUMPTIONS
' A certain folder contains many xls files which must be processed by a syntax file

'EFFECTS
' 1. A dialog first asks the user to select one of the xls file
' 2. This allows the script to get the path of the folder (the "indir" folder)
' 3. This info is used to create a text file (named list.txt) containing the list of
' 4. all the xls files in that folder
' 5. The script then defines a macro named !indir which contains the path of the folder 

'Author: rlevesque@videotron.ca (posted to SPSSX-L on 2001/07/05)


Option Explicit


Sub Main()
Dim Cmd$, strFullPath$, strPath$, strCommand$
Dim intPos As Integer
	' Ask user to select one of the excel file (this will allow us to get the folder name)
	strFullPath=GetFilePath(, "xls", ,"Please select one of the Excel file", 0)

	' Remove the file name from the full path
	intPos = 1
	While InStr(Mid(strFullPath,intPos),"\\")>0
		intPos=intPos + InStr(Mid(strFullPath,intPos),"\\")
	Wend
	strPath=Left(strFullPath,intPos-1)

	' Change directory to the folder containing xls files
	ChDir strPath

	' Next line creates a txt file containing the list of the xls files in the folder
	Shell("COMMAND.COM /c dir *.xls > list.txt",vbNormalFocus)

	' Define a macro giving the path of the excel files
	strCommand = "DEFINE !indir()" & "'" & strPath & "'" & "!ENDDEFINE."
	objSpssApp.ExecuteCommands strCommand, True

End Sub