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

outlawdevil

Board Regular
Joined
Jun 30, 2009
Messages
238
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,368
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top