Excel File Directory

[wienker]

Hi,

I was wondering if there was a way to have excel look into a folder and identify all the excel files in that folder. i.e. a vaildation drop-down that shows every file in the folder so I can choose which one to add.

I have a hundred or so files in a folder and am constantly adding new ones.

Any thoughts?

Thank you

 

[Von Pookie]

This doesn't exactly answer your question, but I use this to search for .xls files in a folder:

Sub test()
Dim Bk As Workbook, i As Integer

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\" 'folder to be searched
    .SearchSubFolders = False
    .Filename = "*.xls"
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
End With

With Application.FileSearch
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
            'code to add file name to list box would go here
        Next i
    Else
        MsgBox "No files found."
    End If
End With

End Sub

 

[wienker]

Thank you Kristy! I am not exactley sure what to place in the part where you stated:
'code to add file name to list box would go here

If I just wanted to paste the results on my worksheet. Is there an easy way to do that. Please let me know if you have any thoughts. Thanks again.

Cheers

 

[Von Pookie]

Putting them on the sheet is easy.

This should make a list in column A of the active sheet:

Sub test()
Dim Bk As Workbook, i As Integer, x As Long

x = 1

With Application.FileSearch
   .NewSearch
   .LookIn = "C:\" 'folder to be searched
   .SearchSubFolders = False
   .Filename = "*.xls"
   .MatchTextExactly = True
   .FileType = msoFileTypeAllFiles
End With

With Application.FileSearch
   If .Execute() > 0 Then
      For i = 1 To .FoundFiles.Count
         Cells(x, 1).Value = .FoundFiles(i)
         x = x + 1
      Next i
   Else
      MsgBox "No files found."
   End If
End With

End Sub

 

[wienker]

Thanks

Thank you a ton. That's exactley what i needed. You just made my life a lot easier!!!

Have a great night.