Excel VBA Trying to read all files in folder based on cell input and output to another cell

Question

Thus, the spreadsheet I'm using has a folder location in cell E22 and a blank cell in cell D22. I want to output all of the files to a blank space after reading the folder path (in E22) (in D22). However, it appears that all that I have is returning is the error message that I specified.

Side Note: Would anything change for network locations?

This is what I have:

Function GetAllFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim FileName As Variant
Dim i As Integer

FileName = Dir(FolderPath)

i = 1
Do While FileName <> ""
Result(i) = FileName
i = i + 1
FileName = Dir()
Loop

GetAllFileNames = Result

End Function


Sub GAFN()

End Sub

And then in the cell I want to insert the result into I have this:

=IFERROR(INDEX(GetAllFileNames($E$22),ROW()),"na")

Anyone can point me in the right direction?

Answer 1

Your array has no capacity for data because it was never initialized. Then, before adding each new element, you must Redim it. The array must be transposed in order for it to be in the "column" format; otherwise, it will be in the "row" format, which will prevent your INDEX function from functioning.

Function GetAllFileNames(ByVal FolderPath As String) As Variant
    Dim Result As Variant
    Dim FileName As Variant
    Dim i As Integer
    
    FileName = Dir(FolderPath)
    
    ReDim Result(1 To 1)  'Initialize array
    
    i = 1
    Do While FileName <> ""
        Result(i) = FileName
        i = i + 1
        FileName = Dir()
        If FileName <> "" Then ReDim Preserve Result(1 To UBound(Result) + 1)
    Loop
    
    GetAllFileNames = Application.Transpose(Result)  'Transpose array

End Function

As an alternative, you can use FileSystemObject

Function GetAllFileNames(FolderPath As String) As Variant
    Dim FSO As Object 'Or you can use New FileSystemObject   'Reference: Microsoft Scripting Runtime
    Dim myFolder As Object 'Folder
    Dim mySubFolder As Object 'Folder
    Dim MyFile As Object 'File
    Dim i As Long
    Dim Result As Variant

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FSO.GetFolder(FolderPath)
    
    ReDim Result(1 To myFolder.Files.Count)
    i = 1
    For Each MyFile In myFolder.Files
        Result(i) = MyFile.Name
        i = i + 1
    Next MyFile

    GetAllFileNames = Application.Transpose(Result)
End Function