Display a list of Excel files at a specific location

[bruty]

Is there any way in Excel to create a list of all Excel files in a given network address?

ie If cell A1 has the network address 'C:\File List\' I am looking for some code or some other way to list in the cells underneath all the Excel files in that directory.

 

[shyrath]

Is there any way in Excel to create a list of all Excel files in a given network address?

ie If cell A1 has the network address 'C:\File List\' I am looking for some code or some other way to list in the cells underneath all the Excel files in that directory.

I think this should work for you, or get you started

http://www.vbaexpress.com/kb/getarticle.php?kb_id=232

 

[schielrn]

If you do a search, this has been mentioned many times before. The code at the bottom of this link seems to do a good job, but you pick the directory rather than using a cell, but it could be modified to work off of A!, but I would suggest searching the board as this has been asked numerous times:

http://ask.metafilter.com/54952/Help-me-output-a-list-of-files-in-a-directory-to-Excel-or-HTML

Hope that helps.

 

[wob]

This should do what you want.
B3 should have the path. C3 should have .xls
Creates a textfile on your C: drive



Dim SearchPath As String
Dim SearchSuffix As String

SearchPath = Range("B3").Value
SearchSuffix = Range("c3").Value

Set fs = Application.FileSearch
With fs
.LookIn = SearchPath
.SearchSubFolders = True
.FileName = SearchSuffix

If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."

Open "h:\excellist.txt" For Output As #1
For i = 1 To .FoundFiles.Count
Print #1, .FoundFiles(i)
Next i
Close #1
MsgBox "c:\excellist.txt updated"

Else
MsgBox "There were no files found."
End If
End With
'
End Sub