CODEWITHSUNDEEP
excelvba
BCLtd
BCLtd

Reputation: 1461

VBA Write All Sub Folders To Sheet

I am trying to write out all folders, sub folders and sub folders etc of the main folder. I am not interested in files.

I have the code working if I just use messagebox... but when I try to write to sheet it just overwrites the previous line.

Sub sample()
   Dim FileSystem As Object
   Dim HostFolder As String
    
   HostFolder = "G:\"

   Set FileSystem = CreateObject("Scripting.filesystemobject")

   DoFolder FileSystem.getfolder(HostFolder)    
End Sub

Sub DoFolder(folder)
   Dim ln As Integer
   Dim subfolder       

   ln = 2    
   For Each subfolder In folder.subfolders    
      'MsgBox subfolder           
       DoFolder subfolder   
       ln = ln + 1
       ActiveWorkbook.Sheets("Sheet1").Range("A" & ln).Value = subfolder   
   Next

   'Dim file

   'For Each file In folder.files

   'MsgBox file

   'Next  
End Sub

Private Sub CommandButton1_Click()    
   Call sample 
   MsgBox ("complete")  
End Sub

Upvotes: 0

Views: 124

Answers (1)

Ming Shou
Ming Shou

Reputation: 129

The reason is simple. That's because you defined the variable "ln" as a local variable inside the Sub "DoFolder". You need to define the variable "ln" as a global variable. Then, it will work well. Most of your code is correct.

Public ln As Integer ' Define as a global

Sub sample()
    Dim FileSystem As Object
    Dim HostFolder As String
    
    HostFolder = "G:\"
    
    Set FileSystem = CreateObject("Scripting.filesystemobject")
    ln = 2 ' Initialize here.

    DoFolder FileSystem.getfolder(HostFolder)
End Sub

Sub DoFolder(folder)
    Dim subfolder
    'Dim ln As Integer
    'ln = 2

    For Each subfolder In folder.subfolders
        DoFolder subfolder
        ln = ln + 1
        ActiveWorkbook.Sheets("Sheet1").Range("A" & ln).Value = subfolder
    Next
End Sub

Private Sub CommandButton1_Click()
    Call sample
    MsgBox ("complete")
End Sub

Upvotes: 2

Related Questions