Hi

Better use an UDF


Here are 3 UDF's, combining which allows you to list all files of certain
type in folder, the workbook is saved in, and/or in all of it's subfolders

Public Function GetSubfolder(MyFolder As String, FolderNum As Integer,
Optional MyTime As Date)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function

Public Function GetFileName(MyFolder As String, FileNum As Integer,
MyExtension As String, Optional MyTime As Date)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set fc = f.Files
i = 0
For Each f1 In fc
If Right(f1.Name, 3) = MyExtension Then
i = i + 1
If i = FileNum Then GetFileName = f1.Name
End If
Next
End Function


Public Function GetThisFolder(Optional MyTime As Date)
GetThisFolder = ThisWorkbook.Path
End Function


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"nc" wrote in message
...
Hi

At present I am running a macro which list the files in a specified
directory on a worksheet using the following events,

Sub Worksheet_Activate()
Sub Worksheet_SelectionChange(ByVal Target As Range).

Can I run the macro whenever a file is saved to this directory?

Thanks.