Macro to extract list of sub-folders within a folder

VBA & Macros

Last Post by Philip Treacy 4 years ago

2 Posts

2 Users

0 Reactions

148 Views

RSS

Howard Cohen

(@howardc)

Posts: 54

Trusted Member

Topic starter

I have many sub-folders within c:accnts for eg TBBR1, TBKWNT,TBBORG

I would like my macro , which list alls the sub-folders within C:accnts, but lists these on a sepertate workbook and not within the workbook containing the macro

I would also like to the folder to be set to C:Accnts so no need to select the folder

The Sub-folders are for eg the ones listed below

C:accntsTBBR1
C:accntsTBKWNT
C:accntsTBBORG Etc

 Sub Extract_subFolder_Names() Sheets("").Select Application.ScreenUpdating = False Dim xPath As String Dim xWs As Worksheet Dim fso As Object, j As Long, folder1 As Object With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Choose the folder" .Show End With On Error Resume Next xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "" Application.Workbooks.Add Set xWs = Application.ActiveSheet xWs.Cells(1, 1).Value = xPath xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified") Set fso = CreateObject("Scripting.FileSystemObject") Set folder1 = fso.getFolder(xPath) getSubFolder folder1 xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535 xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit Application.ScreenUpdating = True End Sub Sub getSubFolder(ByRef prntfld As Object) ChDir "C:accnts" Dim SubFolder As Object Dim subfld As Object Dim xRow As Long For Each SubFolder In prntfld.SubFolders xRow = Range("A1").End(xlDown).Row + 1 Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified) Next SubFolder For Each subfld In prntfld.SubFolders getSubFolder subfld Next subfld End Sub

Your assistance in this regard is most appreciated

I have also posted on Macro to extract list of sub-folders within a folder | MrExcel Message Board

Posted : 14/07/2021 1:05 pm