workaround for Dir(*. ) ? 2

[johnnygeo]

I have an Excel file that I need to save copies of into several hundred folders within a tree structure. The master Excel file is at the top of the tree, and each copy has to be saved 5 folders down. Rather than explicitly state the folder names, I had hoped to use the VBA Dir statement to loop through all folders in the tree until I hit the bottom level. Sort of like this:

myPath = "c:\toplevel"
nextPath = dir(mypath & "*.")
do until nextPath = ""
    myPath = nextPath
    nextPath = dir(myPath & "*.")
Loop

I had based this on my knowledge of DOS, where DIR *. returns a list of all directories. VBA's DIR statement doesn't seem to recognize directories, though. Can anyone suggest a workaround?

Thanks,
Johnny Geo

 

[EBGreen]

Read the help file for DIR.

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (""). Once a zero-length string is returned, you must specify pathname in subsequent calls or an error occurs. You can change to a new pathname without retrieving all of the file names that match the current pathname.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[SkipVought]

Hi,

take a look at the FileSystem Object

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[rmikesmith]

Johnny,

You can specifiy an attribute parameter to Dir per the documentation:

Dir[(pathname[, attributes])]

Set attributes to vbDirectory to search only directories.


HTH
Mike

 

[EBGreen]

I agree that if I were doing this I would use the FileSystem Object.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[johnnygeo]

Thanks, guys. FileSystemObject proved to be a winner.