Open Excel files in subfolders on desktop

[Lifeseeker]

Hi there,

I have some pieces of code that I am hoping to open a some excel.xlsm files in subfolders within a main folder to do some simple manipulations.

file path for the 1st excel file:

C:\Users\GJ\Desktop\TEST1\aaa.xlsx

file path for the 2nd excel file:
C:\Users\GJ\Desktop\TEST2\bbb.xlsx

The main directory stays the same, and the file structure is the same in TEST1 and TEST2 sub folders, but how do I loop through these 2 subfolders under Desktop?

Sub Command0_Click()

 Dim folderPath As String
    Dim filename As String
    Dim WB As Object
    Dim xlApp As Object
    Set xlApp = CreateObject(Excel.Application)
    
    folderPath = "C:\Users\GJ\Desktop\"
   
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
   
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
        Set WB = xlApp.Workbooks.Open(folderPath & filename)
       
      
      
        WB.Close False
        filename = Dir
    Loop


End Sub

Am I on the right track?

 

[arnelgp]

Sub Command0_Click()
    Dim folderPath As String
    Dim filename As String
    Dim WB As Object
    Dim colFolder As Collection
    Dim vFolder As Variant
    Dim xlApp As Object
    
    Set xlApp = CreateObject(Excel.Application)
    Set colFolder = New VBA.Collection
    
    folderPath = "C:\Users\GJ\Desktop\"
   
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
   
    ' fill our collection with sub-directory first
    filename = Dir(folderPath, vbDirectory)
    While filename <> ""
        If (filename <> ".") And (filename <> "..") Then
            If (VBA.GetAttr(folderPath & filename) And vbDirectory) <> 0 Then
                colFolder.Add filename
            End If
        End If
        temp = Dir
    Wend
    
    ' recursively check excel file in subdirectory
    ' open if found
    For Each vFolder In colFolder
        filename = Dir(folderPath & vFolder & "\*.xlsx")
        Do While filename <> ""
        
            Set WB = xlApp.Workbooks.Open(folderPath & vFolder & "\" & filename)
          
            WB.Close False
            filename = Dir
        Loop
    Next
    Set colFolder = Nothing
    
End Sub

 

[Lifeseeker]

Sub Command0_Click()
    Dim folderPath As String
    Dim filename As String
    Dim WB As Object
    Dim colFolder As Collection
    Dim vFolder As Variant
    Dim xlApp As Object
    
    Set xlApp = CreateObject(Excel.Application)
    Set colFolder = New VBA.Collection
    
    folderPath = "C:\Users\GJ\Desktop\"
   
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
   
    ' fill our collection with sub-directory first
    filename = Dir(folderPath, vbDirectory)
    While filename <> ""
        If (filename <> ".") And (filename <> "..") Then
            If (VBA.GetAttr(folderPath & filename) And vbDirectory) <> 0 Then
                colFolder.Add filename
            End If
        End If
        temp = Dir
    Wend
    
    ' recursively check excel file in subdirectory
    ' open if found
    For Each vFolder In colFolder
        filename = Dir(folderPath & vFolder & "\*.xlsx")
        Do While filename <> ""
        
            Set WB = xlApp.Workbooks.Open(folderPath & vFolder & "\" & filename)
          
            WB.Close False
            filename = Dir
        Loop
    Next
    Set colFolder = Nothing
    
End Sub

Hi,

temp is not recognized as a variable. It should be declared as String type?

Thanks

 

[arnelgp]

yes you should declare it:

Dim temp As String