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
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