Automatically input file names from a directory to Excel sheet?

[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,

 

[Barb Reinhardt]

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

 

[Lori]

Barb,

Thank you very much!

I will try it out.

Really appreciate your help!

Lori

 

[Bernd P]

Hello,

Here are some nice examples:
http://erlandsendata.no/?cat=21

Regards,
Bernd

 

[JP]

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

 

[Lori]

Thank you very much, both Barb and JP!!

The macro works perfectly now.

Really appreciate your help!

Sincerely,
Lori

 

[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

 

[JP]

You're welcome, good luck!

--JP