Get List of Files and Sub-Folders in a Folder

While working with VBA, one of the many common things to do will be, getting a list of files and sub-folder in a folder . The following code will be able to help you, in case that's your need. You can copy it for your use

'Declare a variable to hold File System object
Dim objFSO As Object

Decalare a variable to hold File Object
Dim objFile As Object 

'Decalare a couple of variables to hold Folder Objects
Dim objFolder As Object

Dim objSubFolder As Object
Dim sFolderPath as String

'Edit this statement to point it to the folder of your choice
sFolderPath= "C:\MyFolder"
  
'Create an Object of Scripting.FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
    
'Get Folder Information

Set objFolder = objFSO.GetFolder(sFolderPath)
    
'Loop through each sub-folder in the Folder specified
For Each objSubFolder In objFolder.SubFolders
    
      'Do your stuff here
 

Next
    
'Loop through each file in the Folder specified

For Each objFile In objFolder.Files  
    

      'Do your stuff here
 
Next

If you are new to VBA please follow the below link to start with

Excel Macros - Hello World 

You may also be interested in 

Create Directory Tree