MrExcel Publishing
Your One Stop for Excel Tips & Solutions

listing files in a folder


Posted by Sam Samojenko on December 06, 2001 4:27 PM

I have a need to identify all *.xls files in a folder and tried running the following macro. Problem is when the last file is found the macro falls over. Can you help?

Sub findfile()
Dim Files
Dim LRow As Integer

Files = Dir("\\ntco04\nsexec\finreports\depts\*.xls")
LRow = 40
Range("A" & LRow) = MyFile

Do While Dir <> ""
LRow = LRow + 1
Files = Dir
Range("A" & LRow) = Files
Loop

End Sub


Posted by Colo on December 06, 2001 6:00 PM

Hi,Sam
Try This, and please check HELP of "Do Loop statement".

Sub findfile()
Dim Files As String, LRow As Integer
Files = Dir("\\ntco04\nsexec\finreports\depts\*.xls")
LRow = 40
Do While Files <> ""
Range("A" & LRow) = Files
LRow = LRow + 1
Files = Dir
Loop
End Sub