Automatically input file names from a directory to Excel sheet?

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

How can I write a macro to automatically grab the file names of the files in
a specified directory and input them into the Excel spreadsheet, with one
file name in one spreadsheet cell?

Thank you very much in advance for any help or hint.

Sincerely,
 
You could do something like this

Option Explicit

Sub Test()

Dim myFolder As String
Dim myFile As String
Dim lRow As Long
Dim aWS As Excel.Worksheet

Set aWS = Workbooks.Add.ActiveSheet
aWS.Name = "List"

lRow = 1
aWS.Cells(1, 1) = "FileName"

myFolder = "C:\Documents and Settings\barbara.reinhardt\Desktop\" '<~~
change this to whatever you want it to be

If Right(myFolder, 1) <> "\" Then
myFolder = myFolder & "\"
End If

myFile = Dir(myFolder & "*.xls*")

Do
If myFile <> "" Then
lRow = lRow + 1
aWS.Cells(lRow, 1) = myFile
End If
myFile = Dir

Loop While myFile <> ""
End Sub
 
Shouldn't you check if myFile is empty *before* starting the loop, in
case the folder is empty? Then you wouldn't need to check myFile
inside the loop, which could potentially be looped dozens or hundreds
of times.

myFile = Dir(myFolder & "*.xls*")

Do While myFile <> ""
lRow = lRow + 1
aWS.Cells(lRow, 1) = myFile
myFile = Dir
Loop

--JP
 
Thank you very much, both Barb and JP!!

The macro works perfectly now.

Really appreciate your help!

Sincerely,
Lori
 
Hi Bernd,

Thank you very much for the information!

I did find an example of creating a file list from a folder but I found the
logic from Barb is easier for me to digest.

I have saved the website in favorite and will check it out later when I need
further help for other issues.

Really appreciate your help!

Lori
 
Back
Top