List Files In Directory
A simple program for listing all files in a certain directory by calling a VBA Macro Code.
Explanation
The program is set up by giving input about which folder/directory that the program shall analyse. The VBA program then uses the Dir function to get the information about what files are stored in the folder/directory. Then the program simply writes the data to the worksheet. It is possible to use the data in an array if wanting to modify and use the code in an other program. This is a good function when you need to write something or perform an operation to all files stored in a certain folder but you do not know exactly what the files are called or how many they are.
The entire VBA Excel program is available for downloading at the bottom of this page, enjoy!
Code
Public Sub List_Files_In_Directory()
Range("A5:A2000").ClearContents
Dim List_Files_In_Directory(10000, 1)
Dim One_File_List As String
Dim Number_Of_Files_In_Directory As Long
One_File_List = Dir$("C:" + "\*.*")
Do While One_File_List <> ""
List_Files_In_Directory(Number_Of_Files_In_Directory, 0) = One_File_List
One_File_List = Dir$
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Loop
Number_Of_Files_In_Directory = 0
While List_Files_In_Directory(Number_Of_Files_In_Directory, 0) <> tom
Range("A5").Offset(Number_Of_Files_In_Directory, 0).Value = List_Files_In_Directory(Number_Of_Files_In_Directory, 0)
Number_Of_Files_In_Directory = Number_Of_Files_In_Directory + 1
Wend
End Sub
Download excel file! List_All_Files_In_Directory.xls
Comments
Tom and jerry animation at:
http://tomandjerryanimation.blogspot.com
Comment made by: tom and jerry , 2010-04-03 16:12:30
worst piece of code ever posted. you are retarded!
Comment made by: wtf , 2011-01-02 01:29:56
I don'tknow about stupic? Works pretty well to me with some modification. Thanks.
Comment made by: TQT , 2011-05-26 23:04:49
thanks - this helped me a lot , yes some tweaks that can be done to smarten code up, but I can do those
thanks for the post
Comment made by: mike , 2011-10-24 12:13:27
This was a great reminder. Thanks for posting.
Comment made by: Mary , 2011-12-13 16:52:53
Thanks
Comment made by: Joe , 2012-04-18 18:53:35
Congrats for this. I was looking for it but only found old commands that didn´t work.
Now it is working as I needed. Very nice.
Comment made by: Marcos , 2012-09-13 13:59:51
Thanks
Comment made by: Mr excel , 2012-12-06 14:43:19
Congratulation! Good Code.
See my:
Function Check()
Dim nFrase As String
Let nFrase = "c:\Bernardes\Dashboard.xlsb"
If DeepCheck (nFrase) Then
MsgBox "O caminho e/ou arquivo '" & nFrase & "' existe é válido." _
, vbInformation _
, "Informação"
Else
MsgBox "O caminho e/ou arquivo '" & nFrase & "' é inválido ou não existe." _
, vbCritical _
, "Erro"
End If
End Sub
Function DeepCheck (nPath As String) As Boolean
If Dir (nPath) = vbNullString Then
Let DeepCheck = False
Else
Let DeepCheck = True
End If
End Function
André Luiz Bernardes
bernardessAgmail.com
See more in: http://inanyplace.blogspot.com/
Comment made by: Andre Luiz Bernardes , 2013-01-07 17:45:56
Write Comment: