Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Dir in multiple nested loops
I set up three dummy folders in my c: drive
with path "Test\2008\April". Unfortunately when I run this CODE in a module I always get an error message. i tried putting the strSub2 code in a seperate sub routine but same error. I'm wondering if the problem has something to do with what path stored in the Dir after the inner loop. Any help on this problem is appreciated. Thanks. CODE: ========================================= Sub getFile() strSub1 = Dir$("C:\Test\", vbDirectory) Do While Len(strSub1) < 0 If Not ((strSub1 = ".") Or (strSub1 = "..")) Then If InStr(1, strSub1, "2008") Then strSub2 = Dir$("C:\Test\2008\", vbDirectory) Do While Len(strSub2) < 0 If Not ((strSub2 = ".") Or (strSub2 = "..")) Then If InStr(1, strSub2, "April") Then MsgBox strSub2 End If End If strSub2 = Dir$() Loop End If End If strSub1 = Dir$() Loop - ================================== INFO NOT RELATED TO ISSUE, BUT MAYBE SOLUTION In the production version "2008" and "April" are incremented variables (its pretty easy to just have "C:\Test\" & Month(i) type of thing) - ================================== End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Dir in multiple nested loops
On Apr 7, 12:08*pm, Matt wrote:
I set up three dummy folders in my c: drive with path "Test\2008\April". Unfortunately when I run this CODE in a module I always get an error message. i tried putting the strSub2 code in a seperate sub routine but same error. I'm wondering if the problem has something to do with what path stored in the Dir after the inner loop. Any help on this problem is appreciated. Thanks. CODE: ========================================= Sub getFile() strSub1 = Dir$("C:\Test\", vbDirectory) Do While Len(strSub1) < 0 If Not ((strSub1 = ".") Or (strSub1 = "..")) Then If InStr(1, strSub1, "2008") Then strSub2 = Dir$("C:\Test\2008\", vbDirectory) Do While Len(strSub2) < 0 If Not ((strSub2 = ".") Or (strSub2 = "..")) Then If InStr(1, strSub2, "April") Then MsgBox strSub2 End If End If strSub2 = Dir$() Loop End If End If strSub1 = Dir$() Loop - ================================== INFO NOT RELATED TO ISSUE, BUT MAYBE SOLUTION In the production version "2008" and "April" are incremented variables (its pretty easy to just have "C:\Test\" & Month(i) type of thing) - ================================== End Sub Just wanted to add that in most cases month name (example: April) will be just be some of the text in the folder name, which why i'm using instr() function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Dir in multiple nested loops
The problem is with nesting the DIR function. DIR doesn't remember that it
is nested. In the inner loop you reset the path of the dir to a new path and the old path is forgotten. You pull all the data out the dir function in the inner loop then you get to the outer loop and call dir after it ran out of data. here is some interesting code to look at Dim RowNumber Sub GetFolderSize() strFolder = "C:" RowNumber = 1 Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Sheets(1).Cells(RowNumber, 1) = strFolder + "\" Sheets(1).Cells(RowNumber, 2) = folder.Size RowNumber = RowNumber + RowNumber Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetSubFolderSize(strFolder + sf.Name + "\") 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Not folder.isrootfolder Then FolderSize = folder.Size Sheets(1).Cells(RowNumber, 2) = FolderSize Sheets(1).Cells(RowNumber, 1) = strFolder RowNumber = RowNumber + 1 End If 200 On Error GoTo 0 End Sub "Matt" wrote: I set up three dummy folders in my c: drive with path "Test\2008\April". Unfortunately when I run this CODE in a module I always get an error message. i tried putting the strSub2 code in a seperate sub routine but same error. I'm wondering if the problem has something to do with what path stored in the Dir after the inner loop. Any help on this problem is appreciated. Thanks. CODE: ========================================= Sub getFile() strSub1 = Dir$("C:\Test\", vbDirectory) Do While Len(strSub1) < 0 If Not ((strSub1 = ".") Or (strSub1 = "..")) Then If InStr(1, strSub1, "2008") Then strSub2 = Dir$("C:\Test\2008\", vbDirectory) Do While Len(strSub2) < 0 If Not ((strSub2 = ".") Or (strSub2 = "..")) Then If InStr(1, strSub2, "April") Then MsgBox strSub2 End If End If strSub2 = Dir$() Loop End If End If strSub1 = Dir$() Loop - ================================== INFO NOT RELATED TO ISSUE, BUT MAYBE SOLUTION In the production version "2008" and "April" are incremented variables (its pretty easy to just have "C:\Test\" & Month(i) type of thing) - ================================== End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Dir in multiple nested loops
On Apr 7, 1:02*pm, Joel wrote:
The problem is with nesting the DIR function. *DIR doesn't remember that it is nested. *In the inner loop you reset the path of the dir to a new path and the old path is forgotten. *You pull all the data out the dir function in the inner loop then you get to the outer loop and call dir after it ran out of data. here is some interesting code to look at Dim RowNumber Sub GetFolderSize() * *strFolder = "C:" * *RowNumber = 1 * *Set fso = CreateObject _ * * * ("Scripting.FileSystemObject") * *Set folder = _ * * * fso.GetFolder(strFolder) * *Sheets(1).Cells(RowNumber, 1) = strFolder + "\" * *Sheets(1).Cells(RowNumber, 2) = folder.Size * *RowNumber = RowNumber + RowNumber * *Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) * *Set fso = CreateObject _ * * * ("Scripting.FileSystemObject") * *Set folder = _ * * * fso.GetFolder(strFolder) * * * If folder.subfolders.Count 0 Then * * * * *For Each sf In folder.subfolders * * * * * * On Error GoTo 100 * * * * * * Call GetSubFolderSize(strFolder + sf.Name + "\") 100 * * *Next sf * * * End If * *'folder size in bytes * *On Error GoTo 200 * * * If Not folder.isrootfolder Then * * * * *FolderSize = folder.Size * * * * *Sheets(1).Cells(RowNumber, 2) = FolderSize * * * * *Sheets(1).Cells(RowNumber, 1) = strFolder * * * * *RowNumber = RowNumber + 1 * * * End If 200 * On Error GoTo 0 End Sub "Matt" wrote: I set up three dummy folders in my c: drive with path "Test\2008\April". Unfortunately when I run this CODE in a module I always get an error message. i tried putting the strSub2 code in a seperate sub routine but same error. I'm wondering if the problem has something to do with what path stored in the Dir after the inner loop. Any help on this problem is appreciated. Thanks. CODE: ========================================= Sub getFile() strSub1 = Dir$("C:\Test\", vbDirectory) Do While Len(strSub1) < 0 If Not ((strSub1 = ".") Or (strSub1 = "..")) Then If InStr(1, strSub1, "2008") Then strSub2 = Dir$("C:\Test\2008\", vbDirectory) Do While Len(strSub2) < 0 If Not ((strSub2 = ".") Or (strSub2 = "..")) Then If InStr(1, strSub2, "April") Then MsgBox strSub2 End If End If strSub2 = Dir$() Loop End If End If strSub1 = Dir$() Loop - ================================== INFO NOT RELATED TO ISSUE, BUT MAYBE SOLUTION In the production version "2008" and "April" are incremented variables (its pretty easy to just have "C:\Test\" & Month(i) type of thing) - ================================== End Sub- Hide quoted text - - Show quoted text - Thanks alot...I was thinking that..but hoping to defy logic..your solution makes alot of sense...I guess I was a little timid using the filesystemobject. I've played around with it a bit since posting and come to see how usefull it is...not scary at all. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with nested for-next loops | Excel Programming | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Many Nested loops | Excel Programming | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming |