Loop through files in a folder, exclude the last file

Question

i have a folder that has the structure of subfolders filled with powerpoints and one powerpoint outside of those subfolders, so kind of like this:

[main folder]
 |-[subfolder 1]
   |-ppt A
 |-[subfolder 2]
   |-ppt B
 |-ppt file to exclude

my goal is to loop through these subfolders, perform actions on the powerpoints inside the subfolders, and ignore the ppt at the end. i can't hardcode the name of the ppt because the name gets updated every week with a new date in the title. is this possible to do without hardcoding the file name?

while simply testing, i've tried using a do-while loop and hardcoded the name, but that didn't seem to work. in fact, i think it ended up running an endless loop and crashed my PPT:

  Dim File
    For Each File In Folder.Files
        Debug.Print File.path
        
        Do While File <> "hardcoded file name"
            If Right(File, 5) Like ".ppt*" Then
                Set objPresentation = Presentations.Open(File, msoFalse, msoFalse, msoFalse)
                For i = 1 To 1
                    objPresentation.Slides.Item(1).Copy
                    Presentations.Item(1).Slides.Paste
                    Presentations.Item(1).Slides.Item(Presentations.Item(1).Slides.Count).Design = _
                        objPresentation.Slides.Item(1).Design
                    ActivePresentation.Slides(ActiveWindow.View.Slide.SlideIndex).Shapes("Rectangle 2").Delete
                 Next i
                 objPresentation.Close
            End If
        Loop
    Next
    
End Sub

i thought about using a case statement, but wasn't exactly sure where to place it. even if i were to get it to work, i'm not sure if it would still work without the file name being hardcoded.

Does it also crash if you remove parts of those inner loops? What is the goal of `for i = 1 to 1`? — leosch, Mar 16 '23 at 21:16

score 1 · Accepted Answer · answered Mar 16 '23 at 21:38

1

If you're only interested in files in subfolders, then something like this will only give you those paths:

Sub Tester()
    Dim f As Object, sf As Object, fl As Object
    
    Set f = CreateObject("Scripting.Filesystemobject").getfolder("C:\Temp\VBA")
    'only check subfolders
    For Each sf In f.subfolders
        Debug.Print sf.Name
        'loop files in subfolder
        For Each f In sf.Files
            If LCase(f.Name) Like "*.ppt*" Then
                Debug.Print , f.Path
            End If
        Next f
    Next sf
End Sub

answered Mar 16 '23 at 21:38

Tim Williams

154,628
8
97
125

this is great, but it seems to have created an endless loop for me and it just goes back to the first subfolder. how can i end it once it gets to the last subfolder? thank you! – xiaomao Mar 17 '23 at 14:49
It's not an endless loop as posted, so did you make some changes? – Tim Williams Mar 17 '23 at 15:12
i did, but actually just came back to say that i fixed it and to disregard. thank you for all your help! – xiaomao Mar 17 '23 at 15:23

score 0 · Answer 2 · answered Mar 16 '23 at 21:13

To identify the last file in your start folder, you can use File's property count:

Dim counter As Long
counter = folder.Files.Count
   
For Each file In folder.Files
  counter = counter - 1
  If counter = 0 Then Debug.Print "Last one"
  Debug.Print file.Path
Next file

To search the subfolders see other answers, like here

Loop through files in a folder, exclude the last file

2 Answers2