VBA example - Excel 2007: list all files in a folder


VBA code to list all files in a folder

This VBA code example helps you in writing VBA code in Excel to display all files in a specific directory. For example, you want to show all files that are in the drive D:
All files in D:\
.htaccess
Course_syllabus English _Research_Method.doc
Excelobj.xlsx
fixshell.exe
free time.doc
gogo.txt
image817.gif
KnowledgeSharing.bmp
List of Khum Sithor Leader & members to SPM-26May2011.doc
Mybook.xlsx
VBA-for-Excel.xlsx
VBA_tutorial.txt
revenu1_table.xlsx
revenu_table.xlsx
rm_question.doc
RptStudent.pdf



  
To run this example VBA code, you will need a Form, a CommandButton, and a ComboBox.



VBA for Excel: VBA loop to list all files in a directory or dive


VBA code for this example:


Option Explicit
Private Sub CommandButton1_Click()
    Dim fs, dir, fl As Variant
    Dim row As Integer
    'Show heading text
    Cells(1, 1) = "All files in " & ComboBox1.Text
    Cells(1, 1).Interior.Color = RGB(10, 100, 100)
    'fitting the text
    Range("A1:A100").Columns("A:C").AutoFit
    'Create file system object
    Set fs = CreateObject("Scripting.FileSystemObject")
    'Refere to parent folder
    Set dir = fs.GetFolder(ComboBox1.Text)
    'Show all all files in the folder
    Set fl = dir.Files
    row = 2
    For Each f In fl
        Cells(row, 1) = f.Name
        row = row + 1
    Next
End Sub
 
Private Sub UserForm_Initialize()
    'sample directories
    ComboBox1.AddItem "C:\"
    ComboBox1.AddItem "D:\"
    ComboBox1.ListIndex = 0
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.