Solved: List all files in folders

[Digita]

Hello everyone,

I'm sure there are many ways to list files in a directory using VBA. The following code do the same thing except it misses the first file. I have a mental blank at the moment. Would someone show me what's wrong with it please. Thanks in advance for your responses.

[vba]Option Explicit
Option Base 0
Public Function AllFiles(ByVal DirPath As String) As String()
Dim sFile As String
Dim lElement As Long
Dim sAns() As String
ReDim sAns(0) As String
sFile = Dir(DirPath)
If sFile <> "" Then
sAns(0) = sFile
Do
sFile = Dir
If sFile = "" Then Exit Do
lElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1)
ReDim Preserve sAns(lElement) As String
sAns(lElement) = sFile
Range("A" & Cells.Rows.Count).End(xlUp)(2, 1) = sAns(lElement)
Loop
End If
AllFiles = sAns
MsgBox Range("A2").CurrentRegion.Rows.Count ' Returning the number of files in folder
End Function
[/vba]


Regards


kp

[Bob Phillips]

[vba]

Public Function AllFiles(ByVal DirPath As String) As String()
Dim sFile As String
Dim lElement As Long
Dim sAns() As String
ReDim sAns(0) As String
sFile = Dir(DirPath, vbNormal)
If sFile <> "" Then
sAns(0) = sFile
Do
Range("A" & Cells.Rows.Count).End(xlUp)(2, 1) = sAns(lElement)
sFile = Dir
If sFile = "" Then Exit Do
lElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1)
ReDim Preserve sAns(lElement) As String
sAns(lElement) = sFile
Loop
End If
AllFiles = sAns
MsgBox Range("A2").CurrentRegion.Rows.Count ' Returning the number of files in folder
End Function
[/vba]

[Charlize]

[VBA]Public Function AllFiles(ByVal DirPath As String) As String()
Dim sFile As String
Dim lElement As Long
Dim sAns() As String
sFile = Dir(DirPath)
Do While sFile <> ""
lElement = lElement + 1
ReDim Preserve sAns(lElement - 1)
sAns(lElement - 1) = sFile
Range("A" & Cells.Rows.Count).End(xlUp)(2, 1) = sAns(lElement - 1)
sFile = Dir
Loop
AllFiles = sAns
MsgBox Range("A2").CurrentRegion.Rows.Count ' Returning the number of files in folder
End Function[/VBA]

[Norie]

Remove this.
[vba]
lElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1) [/vba]
And add this before Loop.
[vba]
lElement = lElement+1
[/vba]

[Digita]

Hi Bob, Charlize & Norie,

Thank you so much for help in debugging my code. They now work beautifully.

Have a great evening.

My best regards


kp