Notifications
Clear all
VBA & Macros
2
Posts
2
Users
0
Reactions
225
Views
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
Answered on Mr Excel
Posted : 18/07/2021 8:07 pm
