I HAVE THIS:

Sub ListfilesInDirectory()
Dim direc
direc = Range("ad2")
Sheets("Macro").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

Could it be made so that the cells display the filename, instead of th
filename and its directory

--
Message posted from http://www.ExcelForum.com

You could use InstrRev to find the last "\" and then do a Right from that
character on.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ianripping " wrote in message
...
I HAVE THIS:

Sub ListfilesInDirectory()
Dim direc
direc = Range("ad2")
Sheets("Macro").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

Could it be made so that the cells display the filename, instead of the
filename and its directory?


---
Message posted from http://www.ExcelForum.com/

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 Su

--
Gen
-----------------------------------------------------------------------
Geno's Profile: http://www.excelforum.com/member.php...fo&userid=1638
View this thread: http://www.excelforum.com/showthread.php?threadid=21424

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

Thanx a lot, now how do I eliminate the directory name and simpl
display the filenames

--
Gen
-----------------------------------------------------------------------
Geno's Profile: http://www.excelforum.com/member.php...fo&userid=1638
View this thread: http://www.excelforum.com/showthread.php?threadid=21424

If in Excel 2000 or later:

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
varr = Slit(.Foundfiles(i),"\")
ActiveCell.FormulaR1C1 = varr(ubound(varr))
ActiveCell.Offset(1, 0).Select
Next i
Else
End If
End With
End Sub

--
Regards,
Tom Ogilvy



"Geno" wrote in message
...

Thanx a lot, now how do I eliminate the directory name and simply
display the filenames?


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