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