Process All CSV Files In SubFolders

[SamT]

After some investigation, I found that at least some CSV files have extra LineFeeds at the end. Trying to get the Log of the 6th element of "" caused the problem.

Check these results manually and see if this Sub is working now.

table_aapl.csv	2.6762027449490400
table_abbv.csv	16.3658370318833000

Also, Read and understand the formula in this code. It is still giving the same wrong result. But this is the formula you gave us.

BTW, In Table_appl_Test.csv, the value separator is a semicolon. This code only works with a comma separator. Don't be changing things on us without telling us.

Option Explicit
 
Sub SamT_3()
    Dim Filename As String
    Dim NameLength As Long
     
    Dim FileNames As Variant
    Dim FileLines As Variant
    Const F As Long = 5 'CSV field number counting from zero
     
    Dim K_Array As Variant
    Dim L_Array As Variant
    Dim Sum_L As Double
     
    Dim Fn As Long 'Fn = Index number for FileNames
    Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number                                'j
     
    Const FolderPath As String = "C:\TestFolder\" 'include ending \ '<<<<<<<<<<<<<<<<<<<<<<<<<
     
     
     '''' Put all the file names in the path in Array
    FileNames = 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(FileNames)
             
             ''''Put all lines from one file in Arrays
            FileLines = Split(.opentextfile(FileNames(Fn)).readall, vbLf)
            
            'Compensate for extra vbLf's in FileLines '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            Do While FileLines(UBound(FileLines)) = ""
              ReDim Preserve FileLines(UBound(FileLines) - 1)
            Loop
            
            ReDim K_Array(UBound(FileLines))
            ReDim L_Array(UBound(FileLines) + 1)
             
             ''''Log(F) into Column K
            For CR = LBound(FileLines) To UBound(FileLines) - 1
                K_Array(CR) = Log(Split(FileLines(CR), ",")(F))
            Next CR
             
             ''''Calculate Formula on Column K, put in Column L
            For CR = 0 To UBound(K_Array) - 1 '-1 to compensate for column formula offsets
                L_Array(CR + 1) = (100 * (K_Array(CR + 1) - K_Array(CR))) ^ 2
            Next CR
             
             ''''Sum of Column L
            For CR = 1 To UBound(K_Array)
                Sum_L = Sum_L + L_Array(CR)
            Next CR
             
             '''' Put results in sheet
             
             'Get FileName
            NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
            Filename = Right(FileNames(Fn), NameLength)
             
             'Place result
            With ActiveSheet.Rows(Fn + 1)
                .Columns(1) = Filename
                .Columns(2) = Sum_L
            End With
             
        Next Fn 'Work on next File
    End With
End Sub