read directory into excel 1

[ninash]

Hi All

Can anyone tell me how to read a directory into an excel sheet where every file is placed into it's own cell in a column.

Tony

 

[rmikesmith]

Tony,

Try this procedure. Change the column number in Cells as needed.

Sub ListAllFiles()
Dim FName As String
Dim Path As String
Dim FCount As Integer

  Path = "F:\Tek-Tips\"
  FCount = 0
  FName = Dir(Path & "*.*", vbNormal)
  If FName = "" Then
    MsgBox "There are no files in " & Path, vbInformation + vbOKOnly, "List Files"
  Else
    Application.ScreenUpdating = False
    FCount = FCount + 1
    ActiveSheet.Cells(FCount, 1).Value = FName
    Do
      FName = Dir
      FCount = FCount + 1
      ActiveSheet.Cells(FCount, 1).Value = FName
    Loop Until FName = ""
    Application.ScreenUpdating = True
  End If

End Sub

Regards,
M. Smith

 

[ninash]

Thanks very much that is just what I needed

 

[acron]

Try the following code :

Sub GetDirectoryListing()
Dim fso As Object, fldr As Object, oFile As Object
Dim x As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(FolderPath)
    For Each oFile In fldr.Files
        [A1].Offset(x, 0) = oFile.Name
        x = x + 1
    Next
End Sub

replacing FolderPath the path to the directory you wish to list, and A1 with the cell to receive the first filename.

A.C.