I am trying to return an array of folder names without using FSO. Is there anyway Dir can be used to return folders only?
Below is code I am trying to alter to return folder names
Option Explicit
Public Function fnavarGet1dArrFiles_ByDir(ByVal strStartFolder As String, _
Optional ByVal strFileFilter As String = "*", _
Optional ByVal blnReturnFullName As Boolean _
) As Variant
'// Returns an array of file names that match strFileFilter
'// Use argument to choose whether you want FullNames or FileNames only
'// If no matching files are found, it returns Empty
'// Credits: adapted from function by John Walkenbach
'// Note: The strFileFilter is NOT case sensitive
'// Dir method does not detect hidden files
Dim avarFileArray() As Variant
Dim lngFileCount As Long
Dim strFileName As String
On Error GoTo ErrHandler
strStartFolder = fnstrGetSeparatoredPath(strStartFolder)
If Not fnblnExistsFileFolder(strStartFolder) Then
GoTo NoFilesFound
End If
'If no files in folder, exit the sub
strFileName = Dir(strStartFolder & strFileFilter)
If Len(strFileName) = 0 Then
GoTo NoFilesFound
End If
'Loop until no more matching files are found
If blnReturnFullName Then
Do While Len(strFileName)
lngFileCount = lngFileCount + 1
ReDim Preserve avarFileArray(1 To lngFileCount)
avarFileArray(lngFileCount) = strStartFolder & strFileName
strFileName = Dir()
Loop
Else
Do While Len(strFileName)
lngFileCount = lngFileCount + 1
ReDim Preserve avarFileArray(1 To lngFileCount)
avarFileArray(lngFileCount) = strFileName
strFileName = Dir()
Loop
End If
fnavarGet1dArrFiles_ByDir = avarFileArray
Exit Function
ErrHandler:
fnavarGet1dArrFiles_ByDir = Empty
Exit Function
NoFilesFound:
fnavarGet1dArrFiles_ByDir = Empty
End Function
Public Function fnblnExistsFileFolder(ByVal strFullName As String) As Boolean
'adapted from function written by Ken Puls (www.excelguru.ca)
If Len(strFullName) Then
On Error Resume Next
fnblnExistsFileFolder = Len(Dir(strFullName, 31))
On Error GoTo 0
End If
End Function
Public Function fnstrGetSeparatoredPath(ByRef strPath As String, Optional ByVal blnInvert As Boolean) As String
'/ ensures folder path ends in path separator (aka trailing backslash)
'/ doesn't detect garbage input, requires a Path arg
Dim blnChange As Boolean
Const strcPATH_SEPARATOR As String = "\"
If Len(strPath) Then
blnChange = ((Right$(strPath, 1) = strcPATH_SEPARATOR) = blnInvert)
If Not blnChange Then
fnstrGetSeparatoredPath = strPath
ElseIf Not blnInvert Then
'add trailing separator
fnstrGetSeparatoredPath = strPath & strcPATH_SEPARATOR
Else
'remove last character
fnstrGetSeparatoredPath = Left$(strPath, Len(strPath) - 1)
End If
End If
End Function
Bookmarks