VBA example - Excel 2007: list all sub folders


VBA code to list all sub folders in a directory

This VBA code example helps you in writing VBA code in Excel to display all folders in a specific directory. For example, you want to show all folder names that are in the drive D:

Sub folders in C:\
$Recycle.Bin
Borland
Dev-Cpp
Documents and Settings
images
inetpub
jython2.5.0
MSOCache
PerfLogs
Program Files
VBAExcel_tutorial


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


VBA MS Excel: VBA loop to list all sub folders 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  
 
Cells(1, 1) = "Sub folders in " & ComboBox1.Text
  
 
Cells(1, 1).Interior.Color = RGB(10, 100, 100)
    'fitting the text  
 
Range("A1:A100").Columns("A:D").AutoFit
  
 
'Create file system object
  
 
Set fs = CreateObject("Scripting.FileSystemObject")
  
 
'Refere to parent folder
  
  
Set dir = fs.GetFolder(ComboBox1.Text)
  
 
'Show all sub foders
  
  
Set fl = dir.SubFolders
 
  
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

Comments

Name:
Comment:
Enter the text:
CAPTCHA image




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.