VBA Problem. Looping Through Files in Folder.

[hellfire45]

Hi guys, Ya'll are usually really good at the puzzly trouble shooting and I'm looking for some assistance.

My goal is to get this to loop through the folder at the path ultimately stored in STRFOLDER but it keeps opening and closing the same excel file in the folder instead of looping through the 70 or so files in the folder. Any idea of how to get it to move onto the next file in the folder until it has hit every file? Thanks guys, I'm sure its something really small but I just can't quite figure it out.

upath = Application.ActiveWorkbook.Path

i = 0
Do
srcpath = upath & "\" & Format(Date - i, "yyyy mm dd")
                If Dir(srcpath & "\", vbDirectory) <> "" Then
                                Exit Do
                Else
                                i = i + 1
                End If
Loop Until i = 35


FolderNames = Array("AM\", "VP\")
For Each Folder In FolderNames
            STRFOLDER = srcpath & "\" & Folder
            strFileSpec = STRFOLDER & "*.*"
            Set fso = CreateObject("Scripting.FileSystemObject")
            
            File_iteration = 0
            err_count = 0
            file_counter.CountFiles
            
            For Each FileInFolder In fso.getfolder(STRFOLDER).Files
                        On Error GoTo nextfile:
                        File_iteration = File_iteration + 1 'keeps track of the iteration of files being processed
                        Application.StatusBar = File_iteration - 1 & " of " & j & " Files Processed. Please be Patient."
            
                        strFileName = Dir(strFileSpec)
                        sFile = Dir(STRFOLDER & strFileName)
                        If Folder = "AM\" Then
                                    Workbooks.Open STRFOLDER & sFile, ReadOnly:=True
                                    sheet_num = ActiveWorkbook.Sheets.Count
                                    For x_sheet = 2 To sheet_num
                                                Set wscs = ActiveWorkbook.Sheets(x_sheet)
'a whole bunch of worksheet processes stuff

             next
                                    Next
                                    ActiveWorkbook.Close False
                        End If
loopsetter:
            Next FileInFolder  'next iteration in loop
Next Folder

 

[rlv01]

I don't really want to try to untangle your code, but here is an example of how to use the FileSystemObject to loop though all files in a folder.

Sub FileLoop()
    Dim FSO As Object
    Dim FileObj As Object
    Dim FolderPath As String

    FolderPath = "C:\Windows"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(FolderPath) Then
        For Each FileObj In FSO.GetFolder(FolderPath).Files
            Debug.Print FileObj.Name                     'list files
            '
            'Do stuff here
            '
        Next FileObj
    Else
        MsgBox FolderPath & " not found"
    End If
End Sub

 

[hellfire45]

Hi Thanks for the reply. I'm definitely capable of missing lots of things in this coding universe but unless I am, I think you suggested what I was already doing. Which, if so, has me extra perplexed.

 

[hellfire45]

I solved it. Thanks.