Showing files that exist

[Todd Huttenstine]

Hey guys

I have 2 files in a directory. How do I show all the
files in a specified directory. For instance lets say the
directory is C:\Test\. I would like to list all the file
names in range A:A starting in cell 1.

Thank you

 

[smalltalk_vb]

if you're using Excel 2000/2003 use the FileSearch object
It has a lot features to set. this example sorts by file
name in ascending order. use .Execute() if you don't want
sorting done.

Sub ff()
With Application.FileSearch
.LookIn = "C:\test\"

If .Execute(msoSortbyFileName,msoSortOrderAscending) >
0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With


End Sub

 

[Bob Phillips]

Sub ListFiles()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Long

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("c:\Test\")
i = 1
With Range("A1")
For Each oFile In oFolder.Files
If oFile.Type = "Microsoft Excel Worksheet" Then
.Cells(i, 1).Value = oFile.Name
i = i + 1
End If
Next
End With
End Sub

--

HTH

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

 

[Rob van Gelder]

I've for a "List (Sub)Folders" link on my website