lIST dIRECTORY

[ianripping]

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

 

[Bob Phillips]

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)

 

[Geno]

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

 

[Tom Ogilvy]

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.

 

[Geno]

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

 

[Tom Ogilvy]

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