Sub ListfilesInDirectory()
Dim direc
Dim fs As Variant
Dim LooKin As Validation
Dim FileName As Variant
Dim i As Integer

'direc = Range("ad2")
direc = "C:\Data"
Sheets("Test").Select
Range("a1").Select
Set fs = Application.FileSearch
With fs
.Lookin = direc
.FileName = ".xls"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.FormulaR1C1 = .FoundFiles(i)
ActiveCell.Offset(1, 0).Select
Next i
Else
End If
End With
End Sub

worked fine for me. Make sure that AD2 on the active sheet when you run the
macro has a valid path string.

--
Regards,
Tom Ogilvy


"Geno" wrote in message
...

I tried this routine,but it didn't work any ideas?
I get the error message " Invalid procedure call or argument" for the
line " .LooKin = direc" below

Any help would be tremendously appreciated...

--
Sub ListfilesInDirectory()
Dim direc
Dim fs As Variant
Dim LooKin As Validation
Dim FileName As Variant
Dim i As Integer

direc = Range("ad2")
Sheets("Test").Select
Range("a1").Select
Set fs = Application.FileSearch
With fs
LooKin = direc
FileName = "*.xls"
If .Execute 0 Then
ActiveCell = Range("A1")
For i = 1 To .FoundFiles.count
ActiveCell.FormulaR1C1 = .FoundFiles(i)
ActiveCell.Offset(1, 0).Select
Next i
Else
End If
End With
End Sub


--
Geno
------------------------------------------------------------------------
Geno's Profile:
http://www.excelforum.com/member.php...o&userid=16380
View this thread: http://www.excelforum.com/showthread...hreadid=214248