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

by

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

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Getting ScriptControl to work with Excel 2010 x64
  3. Pop up the Excel Statusbar?
  4. When one should use Set [e.g for SpecialCells return value]?
  5. Should I turn .CutCopyMode back on before exiting my sub procedure?
  6. Determine if cell contains data validation
  7. Sheet protection: UserInterFaceOnly gone
  8. VBScript – How to make program wait until process has finished?
  9. How to highlight a cell using the hex color value within the cell?
  10. Removing special characters VBA Excel
  11. Open an Excel file from SharePoint site
  12. VBA recognizing workbook by partial name
  13. Excel VBA wildcard search
  14. How do I loop an excel 2010 table by using his name & column reference?
  15. Excel VBA Project has generated multiple Workbook objects
  16. Copy an entire worksheet to a new worksheet in Excel 2010
  17. to make this code simpler
  18. VBA code takes long time to execute in excel
  19. Excel VBA deleting rows in a for loop misses rows
  20. What is the lifetime of a global variable in excel vba?
  21. Copy data from another Workbook through VBA
  22. Excel VBA Load worksheet in Function
  23. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  24. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  25. Insert line break in wrapped cell via code
  26. Change color of certain characters in a cell
  27. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  28. How to create dynamic variable names VBA
  29. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  30. simple vba code gives me run time error 91 object variable or with block not set

Leave a Comment