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

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) <> "\" Then
        sPath = sPath & "\"
    End If

    If sFilter = "" Then
        sFilter = "*.*"
    End If

    'call with path "initializes" the dir function and returns the first file name
    sFile = Dir(sPath & 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) <> "\" Then
        sPath = sPath & "\"
    End If

    If sFilter = "" Then
        sFilter = "*.*"
    End If

    'call with path "initializes" the dir function and returns the first file
    sFile = Dir(sPath & sFilter)

    'call it until there is no filename returned
    Do While sFile <> ""

        '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 > 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 < 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

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  amp     xlsx   Do While MyFile  lt  gt         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   amp  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

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