PDA

View Full Version : Solved: List all files in folders



Digita
06-09-2008, 07:49 PM
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.

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



Regards


kp

Bob Phillips
06-10-2008, 12:37 AM
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

Charlize
06-10-2008, 12:52 AM
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

Norie
06-10-2008, 06:45 AM
Remove this.

lElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1)
And add this before Loop.

lElement = lElement+1

Digita
06-10-2008, 04:15 PM
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