Writing a VBA function to output the name of a file and folder in a specified network location that-CodePudding

I am writing a VBA function to output a list of folders and files in a specified file location with a name that contains a specified string. The following, which I found online (linked below), has been able to output the list of files but does not include folders:

Function GetFileNamesbyExt (ByVal FolderPath As String, FileExt As String) As Variant
DIM Result As Variant

Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object

Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files

ReDim Result(1 To MyFiles.Count)
i = 1

For Each MyFile In MyFiles
If InStr(1, MyFile.Name, FileExt) <> 0 Then
Result(i) = MyFile.Name
i = i   1
End If
Next MyFile
ReDim Preserve Result(1 To i - 1)

GetFileNamesbyExt = Result
End Function

Link: https://trumpexcel.com/list-of-file-names-from-a-folder-in-excel/

I have attempted to use Dir() and using 'MyFolder.Folders' but it hasn't worked.

Any help would be appreciated.

CodePudding user response：

MyFolder.SubFolders, not MyFolder.Folders

Option Explicit

' changing      Set MyFiles = MyFolder.Files
' to       Set MySubFolders = MyFolder.SubFolders

Function GetSubFolders(ByVal FolderPath As String) As Variant
  Dim Result As Variant

  Dim i As Integer
  Dim MyFSO As Object
  Dim MyFolder As Object
  Dim MySubFolder As Object, MySubFolders As Object

  Set MyFSO = CreateObject("Scripting.FileSystemObject")
  Set MyFolder = MyFSO.GetFolder(FolderPath)
  Set MySubFolders = MyFolder.SubFolders

  ReDim Result(1 To MySubFolders.Count)
  i = 1

  For Each MySubFolder In MySubFolders
    Result(i) = MySubFolder.Name
    i = i   1
  Next MySubFolder
  ReDim Preserve Result(1 To i - 1)

  GetSubFolders = Result
End Function