Private fileCounter As Integer
Private activeSht As Worksheet
'Display all the files in a folder. Searches all the sub folders.

'Prints Folder Names in Column A and and the file Names in Column B

Sub SearchFiles()
    Dim pth As String
    Dim fso As FileSystemObject
    Dim baseFolder As Folder

'    pth = "C:\hadoop\data\RefUSADownloads" 'the base path which has to be searched for Files
    pth = "C:\hadoop\data\prospectsTest" 'the base path which has to be searched for Files

    Set fso = New FileSystemObject

    ''check if the folder actually exists or not

    If (Not (fso.FolderExists(pth))) Then
        'the folder path is invalid. Exiting.
        MsgBox "Invalid Path"
        Exit Sub
    End If

    Set baseFolder = fso.GetFolder(pth)

    fileCounter = 1
    Set activeSht = ActiveSheet

    activeSht.Range("A1").Value = "Folder Name"
    activeSht.Range("B1").Value = "File Name"

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    PrintFileNames baseFolder

ErrHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub


Sub PrintFileNames(baseFolder As Folder)
    Dim folder_ As Folder
    Dim file_ As File
    Dim xlWB As Excel.Workbook

    For Each folder_ In baseFolder.SubFolders
        'call recursive function.
        PrintFileNames folder_
    Next folder_

    For Each file_ In baseFolder.Files
        'print files here
        activeSht.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
        activeSht.Range("B1").Offset(fileCounter, 0).Value = file_.Name
        activeSht.Range("C1").Offset(fileCounter, 0).Value = file_.Type
        Set xlWB = xlApp.Workbooks.Open(file_.Path & file_.Name)
'        xlWB.SaveAs
        fileCounter = fileCounter + 1
    Next file_
End Sub