Get list of Excel files in a folder using VBA

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

User · Answer

If all you want is the file name without file extension

Dim fileNamesCol As New Collection
Dim MyFile As Variant  'Strings and primitive data types aren't allowed with collection

filePath = "c:\file directory" + "\"
MyFile = Dir$(filePath & "*.xlsx")
Do While MyFile <> ""
    fileNamesCol.Add (Replace(MyFile, ".xlsx", ""))
    MyFile = Dir$
Loop

To output to excel worksheet

Dim myWs As Worksheet: Set myWs = Sheets("SheetNameToDisplayTo")
Dim ic As Integer: ic = 1

For Each MyFile In fileNamesCol
    myWs.Range("A" & ic).Value = fileNamesCol(ic)
    ic = ic + 1
Next MyFile

Primarily based on the technique detailed here: https://wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm

User · Answer

You can use the built-in Dir function or the FileSystemObject    Dir Function  VBA  Dir Function FileSystemObject  VBA  FileSystemObject - Files Collection   They each have their own strengths and weaknesses   Dir Function  The Dir Function is a built-in  lightweight method to get a list of files  The benefits for using it are    Easy to Use Good performance  it s fast  Wildcard support   The trick is to understand the difference between calling it with or without a parameter  Here is a very simple example to demonstrate   Public Sub ListFilesDir ByVal sPath As String  Optional ByVal sFilter As String       Dim sFile As String      If Right sPath  1   lt  gt      Then         sPath   sPath  amp          End If      If sFilter      Then         sFilter             End If       call with path  initializes  the dir function and returns the first file name     sFile   Dir sPath  amp  sFilter       call it again until there are no more files     Do Until sFile               Debug Print sFile           subsequent calls without param return next file name         sFile   Dir      Loop  End Sub   If you alter any of the files inside the loop  you will get unpredictable results  It is better to read all the names into an array of strings before doing any operations on the files  Here is an example which builds on the previous one  This is a Function that returns a String Array   Public Function GetFilesDir ByVal sPath As String        Optional ByVal sFilter As String  As String         dynamic array for names     Dim aFileNames   As String     ReDim aFileNames 0       Dim sFile As String     Dim nCounter As Long      If Right sPath  1   lt  gt      Then         sPath   sPath  amp          End If      If sFilter      Then         sFilter             End If       call with path  initializes  the dir function and returns the first file     sFile   Dir sPath  amp  sFilter        call it until there is no filename returned     Do While sFile  lt  gt               store the file name in the array         aFileNames nCounter    sFile           subsequent calls without param return next file         sFile   Dir           make sure your array is large enough for another         nCounter   nCounter   1         If nCounter  gt  UBound aFileNames  Then              preserve the values and grow by reasonable amount for performance             ReDim Preserve aFileNames UBound aFileNames    255          End If      Loop       truncate the array to correct size     If nCounter  lt  UBound aFileNames  Then         ReDim Preserve aFileNames 0 To nCounter - 1      End If       return the array of file names     GetFilesDir   aFileNames    End Function   File System Object   The File System Object is a library for IO operations which supports an object-model for manipulating files  Pros for this approach    Intellisense Robust object-model   You can add a reference to to  Windows Script Host Object Model   or  Windows Scripting Runtime   and declare your objects like so   Public Sub ListFilesFSO ByVal sPath As String       Dim oFSO As FileSystemObject     Dim oFolder As Folder     Dim oFile As File      Set oFSO   New FileSystemObject     Set oFolder   oFSO GetFolder sPath      For Each oFile In oFolder Files         Debug Print oFile Name     Next  oFile      Set oFile   Nothing     Set oFolder   Nothing     Set oFSO   Nothing  End Sub   If you don t want intellisense you can do like so without setting a reference   Public Sub ListFilesFSO ByVal sPath As String       Dim oFSO As Object     Dim oFolder As Object     Dim oFile As Object      Set oFSO   CreateObject  Scripting FileSystemObject       Set oFolder   oFSO GetFolder sPath      For Each oFile In oFolder Files         Debug Print oFile Name     Next  oFile      Set oFile   Nothing     Set oFolder   Nothing     Set oFSO   Nothing  End Sub

User · Answer

Regarding the upvoted answer  I liked it except that if the resulting  listfiles  array is used in an array formula  CSE   the list values come out all in a horizontal row   To make them come out in a vertical column  I simply made the array two dimensional as follows   ReDim vaArray 1 To oFiles Count  0  i   1 For Each oFile In oFiles     vaArray i  0    oFile Name     i   i   1 Next

User · Answer

Dim iIndex as Integer Dim ws As Excel Worksheet Dim wb      As Workbook Dim strPath As String Dim strFile As String  strPath    D  Personal   strFile   Dir strPath  amp     xlsx    Do While strFile  lt  gt         Set wb   Workbooks Open Filename  strPath  amp  strFile       For iIndex   1 To wb Worksheets count         Set ws   wb Worksheets iIndex            Do something here       Next iIndex   strFile   Dir  This moves the value of strFile to the next file  Loop

User · Answer

Sub test       Dim FSO As Object     Set FSO   CreateObject  Scripting FileSystemObject       Set folder1   FSO GetFolder FromPath  Files     FolderPath 1    D  Arun Macro Files UK Marco External Sales Tool for Au Example Files       Workbooks Add     Set Movenamelist   ActiveWorkbook     For Each fil In folder1         Movenamelist Activate         Range  A100000   End xlUp  Offset 1  0  Value   fil         ActiveCell Offset 1  0  Select     Next End Sub

User · Answer

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

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

Examples related to vba

Examples related to excel

Examples related to excel-2010