Get list of Excel files in a folder using VBA [duplicate]

Question

I need to get the names of all the Excel files in a folder and then make changes to each file. I've gotten the "make changes" part sorted out. Is there a way to get a list of the .xlsx files in one folder, say D:\Personal and store it in a String Array.

I then need to iterate through the list of files and run a macro on each of the files which I figured I can do using:

Filepath = "D:\Personal\" For Each i in FileArray     Workbooks.Open(Filepath+i) Next 

I had a look at this, however, I wasn't able to open the files cause it stored the names in Variant format.

In short, how can I use VBA to get a list of Excel filenames in a specific folder?

Answer 1

Ok well this might work for you, a function that takes a path and returns an array of file names in the folder. You could use an if statement to get just the excel files when looping through the array.

Function listfiles(ByVal sPath As String)      Dim vaArray     As Variant     Dim i           As Integer     Dim oFile       As Object     Dim oFSO        As Object     Dim oFolder     As Object     Dim oFiles      As Object      Set oFSO = CreateObject("Scripting.FileSystemObject")     Set oFolder = oFSO.GetFolder(sPath)     Set oFiles = oFolder.Files      If oFiles.Count = 0 Then Exit Function      ReDim vaArray(1 To oFiles.Count)     i = 1     For Each oFile In oFiles         vaArray(i) = oFile.Name         i = i + 1     Next      listfiles = vaArray  End Function 

It would be nice if we could just access the files in the files object by index number but that seems to be broken in VBA for whatever reason (bug?).