VBA Code To Switch Path Dynamically For Subfolders In A Parent Folder

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
I have this code, which I copied from this same platform at:

written by @AlphaFrog .
Which means that I am able to determine if there is/are subfolder(s) in my mainfolder or not.

If there is/are subfolder(s), then I would like to switch the path from:
Code:
strFolderPath = ThisWorkbook.Path & "/MAINFOLDER/"

to
Code:
strFolderPath = ThisWorkbook.Path & "/MAINFOLDER/SUB1"
Which is to say that if there are say 3 subfolders, then I will switch the path 3 times for SUB1, SUB2, SUB3

I understand the loop will be something like this:

Code:
For Each SubFolder In Folder.SubFolders
        '''''msgbox to display path
Next
But I dont know how to code it.


Code:
Sub sub_folders_check()
    Dim fs, strFolderPath, oFolder
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    strFolderPath = ThisWorkbook.Path & "/MAINFOLDER/"
    
    Set oFolder = fs.GetFolder(strFolderPath)
    If (oFolder.SubFolders.Count = 0) Then
        'folder is Empty
        
    Else
        'folder isn't empty
        strFolderPath = ThisWorkbook.Path & "/MAINFOLDER/SUB1"
    End If
    Set fs = Nothing
End Sub

I will be very glad to get solution for this problem.

Thanks in advance.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
See if this helps you:

VBA Code:
Sub KellyMort()

    Dim fs As Object, oFolder As Object, oSubFolder As Object, FldrPath As String
 
    Set fs = CreateObject("Scripting.FileSystemObject")
    FldrPath = ThisWorkbook.Path & "\MAINFOLDER\"
    Set oFolder = fs.GetFolder(FldrPath)
    For Each oSubFolder In oFolder.SubFolders
    
        MsgBox oSubFolder.Path
    
    Next oSubFolder
    Set fs = Nothing
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top