Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
I am trying to use the Dir function to loop through folders in specified
directories, and again to loop through the files in those folders, but my second Dir is messing with the first one (€œRecFile = Dir€ in inner loop makes €œFolder=Dir€ in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" €˜insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
You could use your current procedure to go 1 level of folders deep. Is that
what you want or do you want to examine all folders below the specified folder, no matter the depth? -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
See this article for multiple methods. 2nd method is using DIR
http://support.microsoft.com/kb/185476/EN-US/ How To Search Directories to Find or List Files http://support.microsoft.com/kb/185601/EN-US/ HOW TO: Recursively Search Directories by Using FileSystemObject http://support.microsoft.com/kb/186118/EN-US/ How To Use FileSystemObject with Visual Basic -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
Maybe I'm missing something, but wouldn't I have to specify each of the
folder names? I have sixty of them (12 each in 5 directories), and don't want to list them all in the code, if possible. "Don Guillett" wrote: try to modify this idea to suit Sub anotherfindfiles() Application.ScreenUpdating = False Dim FN As String ' For File Name Dim ThisRow As Long Dim MediaFileLocation As String MediaFileLocation = "c:\yourfolder\*.mp3" FN = Dir(MediaFileLocation) Do Until FN = "" ThisRow = ThisRow + 1 Cells(ThisRow, 1) = FN FN = Dir Loop Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
Yes, I only need to go one level deep within each of the directories - each
of the 5 directories has 12 folders within it (for months of the year), and I would only need the files within there. There should not be any folders within the monthly folders, just files. "Tom Ogilvy" wrote: You could use your current procedure to go 1 level of folders deep. Is that what you want or do you want to examine all folders below the specified folder, no matter the depth? -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
Dim FolderPath As String
Dim Folder As String Dim RecFile As String Public Sub Create_File_List() dim v(1 to 12) as String dim i as Long i = 1 Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then v(i) = Folder i = i + 1 End if Folder = Dir() Loop for i = 1 to 12 Folder = v(i) if len(trim(folder)) < then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If Next End Sub -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... Yes, I only need to go one level deep within each of the directories - each of the 5 directories has 12 folders within it (for months of the year), and I would only need the files within there. There should not be any folders within the monthly folders, just files. "Tom Ogilvy" wrote: You could use your current procedure to go 1 level of folders deep. Is that what you want or do you want to examine all folders below the specified folder, no matter the depth? -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir function resetting
Awesome! This is exactly what I needed!
"Tom Ogilvy" wrote: Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() dim v(1 to 12) as String dim i as Long i = 1 Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then v(i) = Folder i = i + 1 End if Folder = Dir() Loop for i = 1 to 12 Folder = v(i) if len(trim(folder)) < then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If Next End Sub -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... Yes, I only need to go one level deep within each of the directories - each of the 5 directories has 12 folders within it (for months of the year), and I would only need the files within there. There should not be any folders within the monthly folders, just files. "Tom Ogilvy" wrote: You could use your current procedure to go 1 level of folders deep. Is that what you want or do you want to examine all folders below the specified folder, no matter the depth? -- Regards, Tom Ogilvy "Emily Edgington" wrote in message ... I am trying to use the Dir function to loop through folders in specified directories, and again to loop through the files in those folders, but my second Dir is messing with the first one ("RecFile = Dir" in inner loop makes "Folder=Dir" in outer loop no longer work). I have tried putting the inner loop in it's own procedure and calling it within the outer loop, to no avail. Any suggestions? Dim FolderPath As String Dim Folder As String Dim RecFile As String Public Sub Create_File_List() Folder = Dir(FolderPath, vbDirectory) Do While Folder < "" If Folder < "." And Folder < ".." Then If (GetAttr(FolderPath & Folder) And vbDirectory) = vbDirectory Then RecFile = Dir(FolderPath & Folder & "\*-pt.xls") Do Until RecFile = "" 'insert some formulas, yadda yadda yadda RecFile = Dir Loop End If End If Folder = Dir Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are my options resetting themselves? | Excel Discussion (Misc queries) | |||
Resetting the Filter Function .. | Excel Discussion (Misc queries) | |||
Resetting the end of a worksheet | New Users to Excel | |||
Used Range is not resetting | Excel Programming | |||
Resetting variables | Excel Programming |