How to get a list of files, including subfolders, and a list of folders with VBA (Excel macro)

Publication Date:July 12, 2022

INFOMARTION > How to get a list of files, including subfolders, and a list of folders with VBA (Excel macro)


I would like to describe how to get a list of files and a list of folders including subfolders with VBA (Excel macro). This article is intended for those who wish to program the following tasks

  • I want to get a list of all files or folders under a folder with their full paths.
  • I want to process files or folders under a folder in a loop.

Table of Contents

  1. Programming Method
  2. Code Description
  3. summary

1. Programming Method

The following is an example of programming reference.

Since it is created in Function, I would like to have it copied as it is and have the method call from Sub.

'Parameter Examples
'## filePath      C:\Users\user\Desktop\test\
'## kind          false:folder, true:file
'## list
Function getFolderOrFile(filePath As String, kind As Boolean, list As Collection) As Collection
Dim filePathEnd As String
filePathEnd = Right(filePath, 1)
If filePathEnd = "\" Then
  filePath = Left(filePath, Len(filePath) - 1)
End If
'Folder existence check
Dim result As String
result = Dir(filePath, vbDirectory)
If result = "" Then
  MsgBox "File does not exist!"
  Exit Function
End If
If kind Then
  Dim buf As String
  buf = Dir(filePath & "\*.*")
  Do While buf <> ""
    list.Add filePath & "\" & buf
    buf = Dir()
  list.Add filePath
End If
Dim folder As Object
With CreateObject("Scripting.FileSystemObject")
  For Each folder In .GetFolder(filePath).SubFolders
    getFolderOrFile folder.Path, kind, list
  Next folder
End With
End Function

To use it, pass the following as arguments

  • filePath・・・directory. Example)C:\Users\user\Desktop\test
  • kind・・・Pass true,false. If True, returns a list of files. If False, returns a list of folders.
  • list・・・The collection will be used to store the results. An example implementation is described below.

An example implementation of an actual Function call is shown below.

Sub test()
Dim list As Collection
Set list = New Collection
getFolderOrFile "C:\Users\user\Desktop\test", False, list
Dim item As Variant
For Each item In list
  ThisWorkbook.Worksheets(1).Cells(1, 1).Value = item
End Sub

When getFolderOrFile is called, list contains a list of files or folders. The implementation example is False, so the list of folders is stored.

Describe the processing you want to do in the "TODO" section of the For statement. item can be treated as a String variable.

The reference source will be the process of setting the acquired list to A1 in Excel. (This process overwrites the same cell repeatedly.)

2. Code Description

This is the end of the explanation of how to use the system, followed by an explanation of the code.

The details can be seen in the code, but the key point is that getFolderOrFile is called further in the getFolderOrFile method.

In the process of searching for folders, the process is further repeated by calling the process of searching for folders, and the mechanism is to search all folders.

By calling its own method, it repeatedly calls its own method as long as the folder is found, searching all folders.

3. summary

If you are having trouble with how to get a list of files and a list of folders including subfolders with VBA (Excel macro), please refer to this page.

Thank you for taking the time to read this to the end.


Please click here to go to the top page of INFORMATION.


Please click here to view the profile.

■For inquiries, please contact

For inquiries about the article, please contact us here.