Excel parsing of Directories

[chuka]

Hi

I'm trying to write a Macro in excel that will parse files in directories and subdirectories.
I started by trying to get a list of directories and subdirectories but the following code ends with error.

MyPath = "c:\stat\"
Application.DisplayAlerts = True
MToProcess = Dir(MyPath, vbDirectory)' Retrieve the first entry.
Do While MToProcess <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MToProcess <> "." And MToProcess <> ".." Then
' Use bitwise comparison to make it is a directory.
If (GetAttr(MyPath & MToProcess) And vbDirectory) = vbDirectory Then
PNamePath = MyPath + MToProcess + "\"
PToProcess = Dir(PNamePath, vbDirectory)


End If ' it represents a directory.
End If
MToProcess = Dir ' Get next entry.
Loop


It looks like the line "PToProcess = Dir(PNamePath, vbDirectory)" messes up all the structure and I end with an error.
Any ideas how to fix this, I will need to nest about 5 subdirectories and files inside.

 

[PHV]

A starting point with the MS Sripting Runtime referenced:
Create a procedure like this:

Sub WalkDir(oFolder)
Debug.Print "Dir  ", oFolder.Path
For Each f In oFolder.Files
  Debug.Print "File ", f.Path
Next
For Each f In oFolder.SubFolders
  WalkDir(f)
Next
End Sub

And then you may use it like this:

MyPath = "c:\stat\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fd = fso.GetFolder(MyPath)
WalkDir(fd)
Set fd = Nothing: Set fso = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi chuka,

I'm not altogether sure what you're trying to do here, but PH is right - you should probably be using fso. The reason for your error, however, is that you don't reset [purple]MyPath[/purple] when you go down a level (you use, instead, [purple]PNamePath[/purple]) and so at the level below your c:/stat directory you are looking in the wrong place for your GetAttr function and so get file not found. You need to change to something like ..

If (GetAttr(MyPath & MToProcess) And vbDirectory) = vbDirectory Then
    [red]MyPath[/red] = MyPath + MToProcess + "\"
    PToProcess = Dir([red]MyPath[/red], vbDirectory)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[chuka]

Guys

Thanks for your help, I found a piece of code on the internet that does exactly what I need.