List of .xls files in directory

Tiavas · May 25th 2006

Hey is there any way to get a list of filenames into VBA from a specific directory?
for example all files in directory c:\my documents\excel?

eduboys · May 25th 2006

Re: List of .xls files in directory

I've used this snippet to open all files in a directory. The line I stated should give you the filenames of each file in a directory that you could store however you'd like. Definitely some extra code in there, but you should be able to salvage something from it.

Code

Dim Test, sPath As String
Dim Folder, oFSO As Object
On Error Resume Next


Set wbCodeBook = ThisWorkbook


sPath = "Your Path"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sPath)


With Application.FileSearch
    .NewSearch
     'Change path to suit
    .LookIn = Folder
    .FileType = msoFileTypeExcelWorkbooks
    .SearchSubFolders = False
    .Filename = "*.xls"
    If .Execute > 0 Then 'Workbooks in folder
        For lCount = 1 To .FoundFiles.Count 'Loop through all.
            'This next line should get you the filename of all excel workbooks int he directory
            Workbooks.Name(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
         Next lCount
    End If
End With

Display More

Tiavas · May 25th 2006

Re: List of .xls files in directory

Thanks i'll try it

Tiavas · May 25th 2006

Re: List of .xls files in directory

Somehow i get an error on this Workbooks.Name line. what's wrong with it?

Got it! sorry Stupid me :)))
THANKS A LOT!!!

birkholz · Mar 16th 2012

Re: List of .xls files in directory

Code doesn't work with Excel 2007, so I was going to use code from SMC on an earlier thread:http://www.ozgrid.com/forum/showthread.php?t=65530
How do I add code to search sub-folders, as in Application.File Search ability: .SearchSubFolders = True

Private Sub Workbook_Open() 'Upon opening the file, do this:
'First action: Create a worksheet with names of all files in \\Ssi08\sys1\DOCS

Code

Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add
     
     'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder("[URL="file://\\Ssi08\sys1\DOCSCAN"]\\Ssi08\sys1\DOCSCAN[/URL]")
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
     
     'Loop through the Files collection
    For Each objFile In objFolder.Files
  
        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
    Next
     
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
     


  
End Sub

Display More

List of .xls files in directory

Participate now!

Share