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

So I have a spreadsheet with a folder location in E22 and a blank space in D22. I want to read the folder location (in E22) and then output all the files to the blank space (in D22). Though it doesn't seem like what I have is returning anything but the error message I laid out.

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?

CodePudding user response：

Your array was never initialized, so it can´t receive data. Then you need to Redim it before inserting every new element. Finally, you need to transpose the array for it to be in the "column" format or else it will be in a "row" format and your INDEX function won´t work.

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