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

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


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 = vaArrayEnd 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?).


You can use the built-in Dir function or the FileSystemObject.

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    LoopEnd 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 = NothingEnd 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 = NothingEnd Sub


Dim iIndex as IntegerDim ws As Excel.WorksheetDim wb      As WorkbookDim strPath As StringDim strFile As StringstrPath = "D:\Personal\"strFile = Dir(strPath & "*.xlsx")Do While strFile <> ""    Set wb = Workbooks.Open(Filename:=strPath & 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