VBA to List all Folders, Subfolders and files in a directory

[Jindalga2]

Hello All,

I am trying to create a VBA in Excel that replicates a cmd script to list out contents of a folder, subfolders and files, and found this piece of code from an earlier post dated Dec 13, 2018:

VBA to List all Folders, Subfolders and files in a directory

Hi all, I found many examples that prints file directories into a spreadsheet. I am trying to create a VBA in Excel that replicates a cmd script to list out contents of a folder, subfolders and files tree "C:\list\" > C:\list\details.txt" /A /F The spreadsheet needs to cascade so column A...

www.mrexcel.com

The code in the link works perfectly fine, but has one minor short coming.
Each time we try to browse through the contents of a folder, we need to copy the folder path, go to the code module manually and and then paste the same in the code.

Was just wondering if there is a way if this process could be automated either by usung Folder Picker dialogue box [ Application.FileDialog(msoFileDialogFolderPicker) ] or copying the address of the folder and pasting it in a particular cell (example A2) and then automating the code to fetch the path from the designated cell (A2, as in the example), and display the contents.

I am a novice in the field of excel VBA and tried my hand to make some changes to the existing code, but that did not materialise.
Your assistance in this regard shall be of immense help!

Thanks for your valuable time & efforts in advance.

 

[Formula11]

You could try this.

Sub test()
    Dim BrowseWindow As FileDialog
    Dim my_path As String
    Dim dest_Cell As Range
    
    On Error Resume Next

    'dest_cell = something
    
    Set BrowseWindow = Application.FileDialog(msoFileDialogFolderPicker)
    
    If BrowseWindow.Show = -1 Then
        my_path = BrowseWindow.SelectedItems(1) 'Selection of parent folder
        'Proceed
        Call List_Folders_and_Files(my_path, dest_Cell)
    End If
End Sub

 

[Jindalga2]

You could try this.

Sub test()
    Dim BrowseWindow As FileDialog
    Dim my_path As String
    Dim dest_Cell As Range
   
    On Error Resume Next

    'dest_cell = something
   
    Set BrowseWindow = Application.FileDialog(msoFileDialogFolderPicker)
   
    If BrowseWindow.Show = -1 Then
        my_path = BrowseWindow.SelectedItems(1) 'Selection of parent folder
        'Proceed
        Call List_Folders_and_Files(my_path, dest_Cell)
    End If
End Sub

In the above code, i made the following substitution:

'dest_cell = something
dest_Cell = ActiveSheet.Range("B2")

The FileDialogFolderPicker box appears and i am able to select the folder, but the operation returns a blank sheet - meaning the data does not get populated. Pls help!

 

[Jindalga2]

You could try this.

Sub test()
    Dim BrowseWindow As FileDialog
    Dim my_path As String
    Dim dest_Cell As Range
   
    On Error Resume Next

    'dest_cell = something
   
    Set BrowseWindow = Application.FileDialog(msoFileDialogFolderPicker)
   
    If BrowseWindow.Show = -1 Then
        my_path = BrowseWindow.SelectedItems(1) 'Selection of parent folder
        'Proceed
        Call List_Folders_and_Files(my_path, dest_Cell)
    End If
End Sub

Hello @Formula11
Thank you so much for the efforts once again. The original author of the code @John_w has shared the newer version of the main routine, and that works perfect, like a charm.
Sincere gratitide to you as well as @John_w for sparing your valuabe time and efforts. God bless!

The newer version of the main routine is available here:

VBA to List all Folders, Subfolders and files in a directory

Hi all, I found many examples that prints file directories into a spreadsheet. I am trying to create a VBA in Excel that replicates a cmd script to list out contents of a folder, subfolders and files tree "C:\list\" > C:\list\details.txt" /A /F The spreadsheet needs to cascade so column A...

www.mrexcel.com

This post can be considered closed.

 

[Formula11]

OK no worries.