Looking for Macro: How to display folder, files and path in excel

[Mylifedesire]

Hi All,


This might be a common question and i am looking for:


1) Give the network/shareddrive folder path

2) First column display folder name

3) Second column display files inside folder

4) Third column display path of the file


We need to consider the root folder and all the sub-folders and display the details might be in tree structure


Thanks & Regards

John

 

[Hui]

John

Give the following a go

Copy and paste it into a Code Module in VBA

Run the macro List_Files

Navigate to where you want and OK

[pre]

Sub List_Files()
Dim f As Object, fso As Object
Dim folder As String
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancelled"
End
End If
folder = .SelectedItems(1)
End With

ws.Cells(2, 1).Value = Left(folder, 3)
ws.Cells(2, 2).Value = Right(folder, Len(folder) - 3)

For Each f In fso.GetFolder(folder).Files
ws.Range("C" & ws.Rows.Count).End(xlUp).Offset(1, 0) = f.Name
Next
Columns("A:C").Columns.AutoFit
End Sub

[/pre]