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

0 votes

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?

Jan 24 in Others by Kithuzzz
• 27,740 points
37 views

1 answer to this question.

0 votes

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
answered Jan 24 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 51,240 points
67 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
32 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19 in Others by narikkadan
• 51,240 points
32 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
276 views
0 votes
1 answer
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 51,240 points
177 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP