can someone simplify this file listing for me

[Gary Keramidas]

this puts all the file names located in the specified folder starting in A1

Option Explicit
Sub test()
Dim lastrow As Long
Dim MyFiles() As String
Dim NumberOfFiles As Long
Dim FilesInPath As String
Dim FileDir As Variant

FileDir = "e:\plu"
lastrow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

FilesInPath = Dir(FileDir & "\*.fxd")
NumberOfFiles = 0
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

Do While FilesInPath <> ""

Worksheets("Sheet1").Range("A" & lastrow).Value = FilesInPath

NumberOfFiles = NumberOfFiles + 1
lastrow = lastrow + 1

ReDim Preserve MyFiles(1 To NumberOfFiles)
MyFiles(NumberOfFiles) = FilesInPath
FilesInPath = Dir()

Loop
End Sub

--


Gary

[Patrick Molloy]

Option Explicit
Sub GetFiles()
Dim FileDir As String
Dim fn As String
Dim index As Long
FileDir = "e:\plu"
fn = Dir(FileDir & "\*.fxd")
Do Until fn = ""
index = index + 1
Worksheets("Sheet1").Cells(index, 1) = fn
fn = Dir()
Loop
MsgBox index & " files found"

End Sub


"Gary Keramidas" wrote:

> this puts all the file names located in the specified folder starting in A1
>
> Option Explicit
> Sub test()
> Dim lastrow As Long
> Dim MyFiles() As String
> Dim NumberOfFiles As Long
> Dim FilesInPath As String
> Dim FileDir As Variant
>
> FileDir = "e:\plu"
> lastrow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
>
> FilesInPath = Dir(FileDir & "\*.fxd")
> NumberOfFiles = 0
> If FilesInPath = "" Then
> MsgBox "No files found"
> Exit Sub
> End If
>
> Do While FilesInPath <> ""
>
> Worksheets("Sheet1").Range("A" & lastrow).Value = FilesInPath
>
> NumberOfFiles = NumberOfFiles + 1
> lastrow = lastrow + 1
>
> ReDim Preserve MyFiles(1 To NumberOfFiles)
> MyFiles(NumberOfFiles) = FilesInPath
> FilesInPath = Dir()
>
> Loop
> End Sub
>
> --
>
>
> Gary
>
>
>
>

[Gary Keramidas]

thanks patrick, i appreciate it.

--


Gary


"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> Option Explicit
> Sub GetFiles()
> Dim FileDir As String
> Dim fn As String
> Dim index As Long
> FileDir = "e:\plu"
> fn = Dir(FileDir & "\*.fxd")
> Do Until fn = ""
> index = index + 1
> Worksheets("Sheet1").Cells(index, 1) = fn
> fn = Dir()
> Loop
> MsgBox index & " files found"
>
> End Sub
>
>
> "Gary Keramidas" wrote:
>
>> this puts all the file names located in the specified folder starting in
>> A1
>>
>> Option Explicit
>> Sub test()
>> Dim lastrow As Long
>> Dim MyFiles() As String
>> Dim NumberOfFiles As Long
>> Dim FilesInPath As String
>> Dim FileDir As Variant
>>
>> FileDir = "e:\plu"
>> lastrow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
>>
>> FilesInPath = Dir(FileDir & "\*.fxd")
>> NumberOfFiles = 0
>> If FilesInPath = "" Then
>> MsgBox "No files found"
>> Exit Sub
>> End If
>>
>> Do While FilesInPath <> ""
>>
>> Worksheets("Sheet1").Range("A" & lastrow).Value = FilesInPath
>>
>> NumberOfFiles = NumberOfFiles + 1
>> lastrow = lastrow + 1
>>
>> ReDim Preserve MyFiles(1 To NumberOfFiles)
>> MyFiles(NumberOfFiles) = FilesInPath
>> FilesInPath = Dir()
>>
>> Loop
>> End Sub
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>>