Need help modify my code to loop thru subfolder excel files.

[outlawdevil]

Hi,

I currently have a code that can extract cell value, however the folder that I have been assigned with has one level subfolder. Can any one help me to modify this code to pick up subfolder files. Thanks.

'Macro to extract cell value from all files in a folder

Sub Test()
' Adjust the path below as required
MyPath = "C:\Users\cooll\Desktop\Macro\"    ' Set the path.
myname = Dir(MyPath, vbNormal)    ' Retrieve the first entry.
Do While myname <> ""    ' Start the loop.
    ' Ignore the current directory and the encompassing directory.
    If myname <> "." And myname <> ".." Then
        If (GetAttr(MyPath & myname) And vbNormal) = vbNormal Then
           ActiveCell.FormulaR1C1 = "='" & MyPath & "[" & myname & "]Sheet1'!R1C2" ' change the part after the ] to your sheets name
           ' also change the R1C1 on the end to pick up the cell you want ie R2C3 for cell C2
           ' do NOT change the 1st one (.FormulaR1C1) this is part of the command.
           ActiveCell.Offset(0, 1).Value = myname
           ActiveCell.Offset(1, 0).Select
        End If
    End If
    myname = Dir
Loop
End Sub