Does anyone know how to extract the name of the subfolder and automatically paste to the excel sheet? eg i have a root folder called roottest and 150 subfolders and like to have the name of the 150 subfolder and display in each of the cell in excel. many thanks

managed to get the folder name...But any idea how to get the FILE NAME thanks

Here is some code that I think will help you. I found pieces on the net after seeing your question. I have modified the pieces to work with Excel and to populate 2 columns of the Active worksheet. I hope it is helpful. [code] ----- ' Procedure : ShowFolderList ' Author : orange ' Date : 1/22/2009 ' Purpose : To list subfolders and file names. You must enter the StartFolder 'explicitly. For each Folder/File combo, the Folder name is placed in Column 1 'of Worksheet. The file name is placed in column 2. ' ' NOTE: This routine is run from within Excel AND ' MUST have a reference to the Microsoft Scripting Runtime. ' ' Calls: ShowSubFolderList ' CalledBy: N/A - ' Sub ShowFolderList() Dim fso As Object, file As Object, folder As Object, subfolder As Object, s As String Dim startfolder As String Dim i As Long Dim j As Long Dim MyTempList() As String Dim lfldrnm As Integer Dim FldrName As String Dim FilName As String Dim sTmp As String 'On Error GoTo ShowFolderList_Error On Error Resume Next ' to avoib an Invalid procedure call????? startfolder = "H:\My Downloads" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(startfolder) Debug.Print "startfolder " & startfolder & vbCrLf Cells(1, 1).Value = "Folder Name" Cells(1, 2).Value = "File name" i = 3 For Each file In folder.Files s = s & folder.Name & "|" & file.Name & "^" Next file For Each subfolder In folder.SubFolders Call ShowSubFolderList(subfolder, s) Next subfolder ' '*********************************************************************************** ' This routine takes the long string of Folder and file names and marker characters ' and places each folder - file record in an array. ' Each element of the array is parsed to get the folder name and the file name. ' The folder and file name are placed into the same row of the active worksheet. ' The folder name goes in column 1, the filename in column 2. '******************************************************************************* ' MyTempList() = Split(s, "^") For j = 0 To UBound(MyTempList) Step 1 lfldrnm = InStr(MyTempList(j), "|") FldrName = Mid(MyTempList(j), 1, lfldrnm - 1) FilName = Mid(MyTempList(j), lfldrnm + 1, Len(MyTempList(j)) - (lfldrnm + 1)) 'Debug.Print MyTempList(j) 'Debug.Print FldrName & "***" & FilName Cells(i, 1).Value = FldrName Cells(i, 2).Value = FilName i = i + 1 Next j MsgBox "Procedure has finished " & Now() & vbCrLf & i - 3 & " Folder/File combos processed" Set file = Nothing Set subfolder = Nothing Set fso = Nothing On Error GoTo 0 Exit Sub ShowFolderList_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowFolderList of VBA Document ThisWorkbook" End Sub --- ' Procedure : ShowSubFolderList ' Author : Orange ' Date : 1/22/2009 ' Purpose : Subroutine that works with ShowFolderList. This gets the folder ' and file name. ' Calls: ShowSubFolderList (recursive) ' CalledBy: ShowFolderList - ' Sub ShowSubFolderList(fld As Object, ByRef str As String) Dim fil As Object, subfld As Object, arr() As Variant For Each fil In fld.Files str = str & fld.Name & "|" & fil.Name & "^" Next fil For Each subfld In fld.SubFolders Call ShowSubFolderList(subfld, str) Next subfld Set fil = Nothing Set subfld = Nothing End Sub [/code]