Process All CSV Files In SubFolders

[Kenneth Hobs]

I had to tweak 2 lines of code in Sam's code. The filter line needed the "cmd /c Dir" and a loop needed a -1.

I must be getting sleepy as I can't see what math needs to be fixed where you commented Sam.

Anywho, here is my slight modification. M, you will notice that Sam took it to the last level to poke some values into the active sheet. The math needs to be resolved properly though. Maybe I can see more clearly tomorrow. Allergies and late night last night and being an old fogy does not help.

Sub KenH_SamT_snb2()     
    Dim Filename As String
    Dim NameLength As Long
     
    Dim FileNameArray As Variant 'sn
    Dim FileLinesArray As Variant 'sp
     
    Dim Formula1LinesArray As Variant
    Dim Formula1Result As Double 'y
     'Repeat these two lies for each Formula. Edit the numbers to suit
    Dim Formula2LinesArray As Variant
    Dim Formula2Result As Double
     
    Dim Fn As Long 'Fn = Index number for FileNameArray                     'jj
    Dim Fl As Long 'Fl = FileLinesArray Index number                                    'j
     
    Const FolderPath As String = "X:\snb\maxxino\" 'include ending \
     
     
     '''' Put all the file names in the path in Array
    FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
      FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")


     '''' Open one file at a time
    With CreateObject("scripting.filesystemobject")
        For Fn = 0 To UBound(FileNameArray)
             
             
             ''''Put all lines from one file in Arrays
            FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbCrLf)
            Formula1LinesArray = FileLinesArray
            Formula2LinesArray = FileLinesArray
             'Initializ
            Formula1Result = 0
            Formula2Result = 0
             ''''Calcuate first result for one file
             
            For Fl = 0 To UBound(FileLinesArray) - 1
                 
                 '''' Calculate first formula
                 'Replace file line with Log of 6th value. Split(BlahBlah)(5)
                Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5))
                 'After the first line
                If Fl > 0 Then Formula1Result = Formula1Result + Formula1Result + _
                (Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) ^ 2) * 100
                 
                 '''' Calculate second Formula here
                 'Replace file line with first part of formula. Think carefully
                 'Formula2LinesArray (Fl) =  Your formula here
                 
            Next Fl
             
             '''' Put results in sheet
             
             'Get FileName
            NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\")
            Filename = Right(FileNameArray(Fn), NameLength)
             
             'Place result
            With ActiveSheet.Rows(Fn + 1)
                .Columns(2) = Formula1Result 'Column B
                .Columns(3) = Formula2Result
                .Columns(4) = Filename
            End With
             
             'Initialize Arrays
            FileLinesArray = ""
            Formula1LinesArray = FileLinesArray
            Formula2LinesArray = FileLinesArray
             
             ''''' For testing only
            If Fn >= 10 Then
                MsgBox "IT Works! Stopping Sub"
                Exit Sub
            End If
             ''''End Test
             
        Next Fn 'Work on next File
    End With
End Sub