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

Publication Date：July 12, 2022

summary

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.

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

'delete\

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()

  Loop

Else

  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

  '------TODO------

  ThisWorkbook.Worksheets(1).Cells(1, 1).Value = item

Next

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.