I have main folder `Cost` and inside it there are two folders `A` & `B`. Inside A & B folders, there are subfolders and each subfolder has only one xlsb file. I need to list all the xlsb files in column B and the subfolder that it has the xlsb in column A.
The following code works but not perfect
Code: Select all
Sub ListXLSBFiles()
Dim mainFolder As String
Dim ws As Worksheet
Dim Row As Long
mainFolder = "D:\Cost"
Set ws = ThisWorkbook.Sheets("Sheet3")
ws.Cells.Clear
ws.Cells(1, 1).Value = "Subfolder Name"
ws.Cells(1, 2).Value = "XLSB File Name"
Row = 2
ListFilesAndFoldersInFolder mainFolder, ws, Row
End Sub
Sub ListFilesAndFoldersInFolder(folderPath As String, ws As Worksheet, ByRef Row As Long)
Dim xlsbFile As String
Dim subFolder As Object
Dim subSubFolder As Object
xlsbFile = Dir(folderPath & "*.xlsb")
Do While xlsbFile <> ""
ws.Cells(Row, 1).Value = folderPath
ws.Cells(Row, 2).Value = xlsbFile
Row = Row + 1
xlsbFile = Dir
Loop
Set subFolder = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath)
For Each subSubFolder In subFolder.SubFolders
ListFilesAndFoldersInFolder subSubFolder.Path & "\", ws, Row
Next subSubFolder
End Sub