import a list of file names in a directory

[dantb]

Is there any formula or vba that excel has that would look in a directory, and return the name of the files within? Thanks Dan

 

[SamS]

Dan, I'm not sure who posted this but it is not mine. This will find all Excel Workbooks in a folder(and subfolders)and then create a hyperlink to them as well.

Sub ListFiles()
Dim i As Integer

With Application.FileSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "\ntco02asrifinreportsdepts"
.SearchSubFolders = False 'or True, depends on you
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 1).Hyperlinks.Add Cells(i, 1), Cells(i, 1).Value
Next i
End If
End With
End Sub

 

[dantb]

Thanks Sam,but all I am looking for is the names. Trying to save from typing about 300 file names. And all in the directory are not excel files. Thanks Dan

 

[SamS]

Dan, this is one I have used, just substitute your own path - I use the server name instead of a drive letter due to the drive being dynamic depending on when I map to the servers:

Sub findfiles()
myfile = Dir("\ntco02asri*.*")

Range("A1").Select

NRow = 1


Do While myfile <> ""

Range("A" & NRow) = myfile
NRow = NRow + 1
myfile = Dir

Loop
End Sub

 

[dantb]

Thanks Sam, Works great really saves allot of work. Thanks again Dan!