import a list of file names in a directory

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
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

Active Member
Joined
Mar 20, 2002
Messages
358
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

Well-known Member
Joined
Feb 17, 2002
Messages
542
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

Active Member
Joined
Mar 20, 2002
Messages
358
Thanks Sam, Works great really saves allot of work. Thanks again Dan!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top