Enhancing the macro

Question

I want to improve my Excel VBA macro that creates the file list and the macro that renames the file name on the file list.

I made two Excel VBA macros. The macro named "Sub File_list" creates a file list in a folder where the xls file is stored and, The macro named "Sub Re_name" renames files using the file list. However, these macros cannot handle files in subfolders.These macros are show below, you can download the macro from this link.

【My Questions】

• I want the "Sub File_list" to have the ability to list files in subfolders.
• I want these "Sub Re_name" to have the ability to rename files in subfolders.（The renamed file shall stored in the same file as the original file.)

Assume that the files and folders shown in FIG. 1 are stored in the folders. The "File_mng.xls" is the excel file that consists these macros.

Fig.1

At this time, when the "Sub File_list" is executed, all files stored in the same level (except "File_mng.xls" itself) are displayed on the spreadsheet (See Fig.2). However, sub folders and the files stored in that sub folders are not listed.

Fig.2

Note that, the backslash is garbled into the Yen sign because My Windows10 is Japanese version.

【The macros】 You can also download the macro from this link.

'Create a list of files in a specific folder
Sub File_list()
    Dim myFileName As String
    Dim FSO As Object
    Dim cnt

    myDir = ThisWorkbook.Path
    myDir = myDir & "\"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    cnt = FSO.GetFolder(myDir).Files.Count

    Range("A1").Value = "File name (Number of files " & cnt & ")"

    'Show hidden and system files
    myFileName = Dir(myDir & "*", vbHidden + vbSystem)

    While myFileName <> vbNullString
        If myFileName <> ThisWorkbook.Name Then
            Cells(Rows.Count, 1).End(xlUp).Offset(1).Value _
                = myDir
            Cells(Rows.Count, 2).End(xlUp).Offset(1).Value _
                = myFileName
        End If
        myFileName = Dir()
    Wend

    Columns(1).AutoFit
    Application.ScreenUpdating = True
End Sub

'Renames  files using the file list
Sub Re_name()
    myDir = ThisWorkbook.Path
    Nmax = (ActiveSheet.Range("A1").End(xlDown).Row)

    For n = 2 To Nmax
        yenn = ""

        If (Right(Cells(n, 1), 1) <> "\") Then
            yenn = "\"
        End If

        N1 = Cells(n, 1) & yenn & Cells(n, 2)
        N2 = Cells(n, 3) & Cells(n, 4) & Cells(n, 5) & Cells(n, 6)

        If N2 = "" Then
            N2 = N1
        Else
            N2 = myDir & "\" & N2
        End If

        Name N1 As N2
    Next n
End Sub

P.S. I'm not very good at English, so I'm sorry if I have some impolite or unclear expressions. I welcome any corrections and English review. (You can edit my question and description to improve them)

You can download all related files from here.

Post hoc Note: (Added on 2019/12/15(JST))
【Comment on the trust settings for PASUMPON V N's macro 】
Thanks to the contributions of PASUMPON V N, I get a complete solution.

You can download a modified version so that it lists files based on the folder where the macro is. (I modified HostFolder = "C:\User\" to HostFolder = ThisWorkbook.Path )

Running this macro, I came across one error, 'Error 1004: Programmatic access to Visual Basic Project is not trusted' at the line of ".VBProject.References". But It is solved by security settings of excel.

The setting method may depend on version and language

• For the Japanese version, if you come across the following error, this site(but written in Japanese) might be helpful. What I actually tried was the procedure written in this site.
  "プログラミングによる visual basic プロジェクトへのアクセスは信頼性に欠けます 1004"
  (that means "'Error 1004: Programmatic access to Visual Basic Project is not trusted")

• For the English version,here or here might be helpful if you come across the Error 1004.

Answer 1

Hi I have modified the code for your requirement, could you please let me know if it is fine

i have used below code , for recursive type programming

Loop Through All Subfolders Using VBA

Sub sample()
    Dim FileSystem As Object
    Dim HostFolder As String
    Dim Ref As Object, CheckRefEnabled%
    CheckRefEnabled = 0
    With ThisWorkbook
        For Each Ref In .VBProject.References
            If Ref.Name = "Scripting" Then
                CheckRefEnabled = 1
                Exit For
            End If
        Next Ref
        If CheckRefEnabled = 0 Then
            .VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
        End If
        End With


    HostFolder = "C:\User\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)

End Sub

Sub DoFolder(Folder)

Dim wb As Workbook

Dim ws As Worksheet

Set wb = ThisWorkbook

Set ws = wb.Worksheets("Sheet1")


Dim LastRow As Long

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each myFile In Folder.Files
        Debug.Print myFile
        Debug.Print Folder.Name
        Debug.Print myFile.Name



    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    i = LastRow + 1


        ws.Cells(i, 1) = myFile.Path
        ws.Cells(i, 2) = Folder.Name
        ws.Cells(i, 3) = myFile.Name


    Next
End Sub