Dir Do Until Loop not working

[Jetx227]

Hi guys! I have this for loop iterating through each folder in a file path, then it goes into the folder and gets the names of the files. Only problem is that while it should be getting the name of every file in each folder, it only gets the name of the first file. So I though a Do Until loop would solve the issue, but it didn't do anything. Any help would be great, thanks!

For Each objFolder In objFolders
   FileName = Dir(FilePath & "\" & objFolder.Name & "\" & objFolder.Name & "*")
   Do Until FileName = ""
      'Some Commands that have to do with the filename
      FileName = Dir()
   Loop

Note, if this is normally supposed to work (because I took this off some website [codevba.com/office/loop_files_in_folder.htm] ), is it possible that commands that I use in the Do Until loop would interfere with it? Thanks

 

[jmacleary]

Hi there. Try amending you filename line like this (change in red):

FileName = Dir(FilePath & "" & objFolder.Name & "\*.*")

 

[Jetx227]

Hi there. Try amending you filename line like this (change in red):

FileName = Dir(FilePath & "" & objFolder.Name & "\*.*")

Hi there!
I tried this and updated the code to:

For Each objFolder In objFolders
   FileName = Dir(FilePath & "" & objFolder.Name & "\*.*")
   Do Until FileName = ""
      'Some Commands that have to do with the filename
      FileName = Dir()
   Loop

This still only pulled the first file from each folder

 

[gallen]

You don't need the "*.*" should just end in the final backslash

 

[jmacleary]

Hi there. I have plugged your code into another subroutine I use to see if it works - this code will msgbox each file in each subfolder starting from the folder you select. It shows that multiple files are selected.

Sub looper()
    Dim objFSO As Object
    Dim objFolders As Object
    Dim objFolder As Object
    Dim strDirectory As String


    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Select Folder"
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        End If
        strDirectory = .SelectedItems(1)
    End With
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolders = objFSO.GetFolder(strDirectory).SubFolders
    
For Each objFolder In objFolders
   Filename = Dir(strDirectory & "\" & objFolder.Name & "\*.*")
   Do Until Filename = ""
      'Some Commands that have to do with the filename
      Filename = Dir()
      MsgBox Filename
   Loop
   Next objFolder
End Sub

 

[Jetx227]

Hi there. I have plugged your code into another subroutine I use to see if it works - this code will msgbox each file in each subfolder starting from the folder you select. It shows that multiple files are selected.

Sub looper()
    Dim objFSO As Object
    Dim objFolders As Object
    Dim objFolder As Object
    Dim strDirectory As String


    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Select Folder"
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        End If
        strDirectory = .SelectedItems(1)
    End With
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolders = objFSO.GetFolder(strDirectory).SubFolders
    
For Each objFolder In objFolders
   Filename = Dir(strDirectory & "\" & objFolder.Name & "\*.*")
   Do Until Filename = ""
      'Some Commands that have to do with the filename
      Filename = Dir()
      MsgBox Filename
   Loop
   Next objFolder
End Sub

Hi jmacleary,
So I just adjusted your code slightly and it worked perfectly, it pulled all the names of the files. Although when I tried to use my commands it is still on pulling the first file for some reason. Is it possible that they could have an affect on it? The commands essentially open a pdf, turn it into an excel workbook (which saves the workbook into that folder that the file was pulled from), copies the sheet ion the workbook that was created into a master workbook, and then deletes the workbook created from the pdf. So by the time that the loop comes back around to looking at the file location, it should be the exact same as when the loop started, which is why im not sure its having an affect on it.

 

[Jetx227]

You don't need the "*.*" should just end in the final backslash

Hi Gallen,
This had the same issue, still only pulled the first file (see my reply to jma's post, could it be my commands that affect it?)

 

[Jetx227]

So I'm fairly certain that because I'm adding a new file and changing the dynamic of the folder (even though I delete the new file before the loop looks at the file location again), the dir function just kinda reads an error and ends the loop or something. So basically my fix is I'm just gonna try to loop through all of the files ahead of time and store the names in cells in a worksheet or something

 

[Fluff]

Pretty sure that when you first call dir it creates a "list" of files, but if the files in that folder change, then it destroys the "list" & has to rebuild it. Hence you always get the same file.

 

[Jetx227]

Pretty sure that when you first call dir it creates a "list" of files, but if the files in that folder change, then it destroys the "list" & has to rebuild it. Hence you always get the same file.

Yeah, I was just trying to read into it more and I think that your explanation of what it does makes sense. I got it now so that I just use the loop that jma gave me to save the file names in a worksheet (each folder getting a column, each file in that folder getting a row) then just calling that and running my original commands. A bit of a work around, but whatever works. Thanks to everyone for the help!