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


"Lori" wrote:

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,

Thank you very much!

I will try it out.

Really appreciate your help!

Lori

"Barb Reinhardt" wrote:

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" wrote:

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,

Hello,

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

Regards,
Bernd

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

On Sep 28, 4:32*pm, Barb Reinhardt
wrote:
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

Thank you very much, both Barb and JP!!

The macro works perfectly now.

Really appreciate your help!

Sincerely,
Lori

"JP" wrote:

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

On Sep 28, 4:32 pm, Barb Reinhardt
wrote:
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

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

"Bernd P" wrote:

Hello,

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

Regards,
Bernd

You're welcome, good luck!

--JP

On Oct 1, 10:54*am, Lori wrote:
Thank you very much, both Barb and JP!!

The macro works perfectly now.

Really appreciate your help!

Sincerely,
Lori