PDA

View Full Version : [SOLVED] Function to get list of Excel files in a folder



Edmond
05-16-2019, 07:37 AM
Hello everyone,

I am trying to get a list of the excel files I have in a folder

I have found this function on google:



Public Function GetFilesDir(ByVal sPath As String, _ Optional ByVal sFilter As String) As String()

'dynamic array for names
Dim aFileNames() As String
ReDim aFileNames(0)

Dim sFile As String
Dim nCounter As Long

If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If

If sFilter = "" Then
sFilter = "*.*"
End If

'call with path "initializes" the dir function and returns the first file
sFile = Dir(sPath & sFilter)

'call it until there is no filename returned
Do While sFile <> ""

'store the file name in the array
aFileNames(nCounter) = sFile

'subsequent calls without param return next file
sFile = Dir

'make sure your array is large enough for another
nCounter = nCounter + 1
If nCounter > UBound(aFileNames) Then
'preserve the values and grow by reasonable amount for performance
ReDim Preserve aFileNames(UBound(aFileNames) + 255)
End If

Loop

'truncate the array to correct size
If nCounter < UBound(aFileNames) Then
ReDim Preserve aFileNames(0 To nCounter - 1)
End If

'return the array of file names
GetFilesDir = aFileNames()
End Function

Pretty dumb question, but how do I call the function? I have a type mismatch error every time I try.


Thank you all for your help!

大灰狼1976
05-16-2019, 07:20 PM
Hi Edmond!
something like below:

Sub test()
Dim path$
path = "C:\Users\mdl1202\Desktop\EXCEL SAMPLE" 'input path what you want
MsgBox Join(GetFilesDir(path, "*.xls"), Chr(10))
End Sub

Public Function GetFilesDir(ByVal sPath As String, Optional ByVal sFilter As String) As String()
'dynamic array for names
Dim aFileNames() As String
ReDim aFileNames(0)
Dim sFile As String
Dim nCounter As Long
If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If
If sFilter = "" Then
sFilter = "*.*"
End If
'call with path "initializes" the dir function and returns the first file
sFile = Dir(sPath & sFilter)
'call it until there is no filename returned
Do While sFile <> ""
'store the file name in the array
aFileNames(nCounter) = sFile
'subsequent calls without param return next file
sFile = Dir
'make sure your array is large enough for another
nCounter = nCounter + 1
If nCounter > UBound(aFileNames) Then
'preserve the values and grow by reasonable amount for performance
ReDim Preserve aFileNames(UBound(aFileNames) + 255)
End If
Loop
'truncate the array to correct size
If nCounter < UBound(aFileNames) Then
ReDim Preserve aFileNames(0 To nCounter - 1)
End If
'return the array of file names
GetFilesDir = aFileNames()
End Function

snb
05-17-2019, 04:47 AM
The simplest:


Sub M_snb()
msgbox createobject("wscript.shell").exec("cmd /c Dir G:\OF\*.xls /b/a-d").stdout.readall
End Sub

大灰狼1976
05-17-2019, 04:55 AM
@snb
Wow~great!:bow:


--Okami

Edmond
05-17-2019, 07:02 AM
Thank you so much to both of you for your help!!