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:

Comment:
two + 3 minus 1=
Your name: