Subfolders

[bromy2004]

Hi,

I'm trying to get a macro to do several step to Each file within subfolders.
i.e. i select a folder path (C:\Test\) the macro will then open each file in there and save to a different location (C:\Final\)

but i can seem to get it to loop through more than one subfolder.

Excel Workbook
	G
25	C:\Test\Folder 1\Subfolder 1
26	C:\Test\Folder 1\Subfolder 2
27	C:\Test\Folder 1\Subfolder 3
28	C:\Test\Folder 1\Subfolder 4
29	C:\Test\Folder 1\Subfolder 5
30	C:\Test\Folder 2\Subfolder 1
31	C:\Test\Folder 2\Subfolder 2
32	C:\Test\Folder 2\Subfolder 3
33	C:\Test\Folder 2\Subfolder 4
34	C:\Test\Folder 2\Subfolder 5
35	C:\Test\Folder 3\Subfolder 1
36	C:\Test\Folder 3\Subfolder 2
37	C:\Test\Folder 3\Subfolder 3
38	C:\Test\Folder 3\Subfolder 4
39	C:\Test\Folder 3\Subfolder 5
40	C:\Test\Folder 4\Subfolder 1
41	C:\Test\Folder 4\Subfolder 2
42	C:\Test\Folder 4\Subfolder 3
43	C:\Test\Folder 4\Subfolder 4
44	C:\Test\Folder 4\Subfolder 5
45	C:\Test\Folder 5\Subfolder 1
46	C:\Test\Folder 5\Subfolder 2
47	C:\Test\Folder 5\Subfolder 3
48	C:\Test\Folder 5\Subfolder 4
49	C:\Test\Folder 5\Subfolder 5
Option

So i can loop it through "Test" and the "Folder*" folders
but i cant loop it through each of the subfolders.

there could be anywhere from 1 folder to 10 sub-levels

please help me.

 

[Tom Urtis]

Depending on the version of Excel you are using, this macro will identify all Excel workbooks in the primary folder you specify, and all its subfolders.

Modify the example primary folder path of "C:\Your\File\Path" with whatever your primary folder really is

and

substitute this code block which is only for demonstration purposes...

MsgBox "There were " & .FoundFiles.Count & " file(s) found in primary path" & vbCrLf & strPath, , "FYI"
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i

...with whatever code you had in mind to save those files or do whatever you were going to do to them.

Sub FilePathSearch()
With Application.FileSearch
Dim strPath$, i%
strPath = "C:\Your\File\Path"
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
.Filename = "*.xls*"
.MatchTextExactly = True
If .Execute() > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found in primary path" & vbCrLf & strPath, , "FYI"
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i

Else
MsgBox "Nothing found in primary path" & vbCrLf & strPath
End If
End With
End Sub

 

[bromy2004]

I'm getting an error on

With Application.FileSearch

"Run Time error 445"
"Object Doesn't Support this action"

I'm running Excel 2007

 

[Tom Urtis]

That possibility was why I said the code depended on the version of Excel you are using because 2007 does not support FileSearch. I'm in front of the television watching Missouri vs Nebraska tonight, so I'll modify the code for 2007 and post it tonight.

 

[Tom Urtis]

Halftime in rainy Columbia but a nice dry October night here in California...take a look at this post by Nate Oliver that I believe does what you are asking for:

http://www.mrexcel.com/forum/showthread.php?p=1228168

 

[bromy2004]

I currently have something similar to that.

using the

For Each SubFolder In Folder.SubFolders

however this only goes into 1 sub-folder.

there may be up to 10.

i have thought that maybe it could be a separate Macro
to search through a designated folder and run a second macro if there is a sub-folder.

but i haven't met with any success.

 

[NateO]

Hello,

You want recursion - a procedure that recalls itself. Look at the code Tom linked to, closely.