Generation of filename, count of records and Sum Value for multiple text files

[amit_gupta123]

Hello..

I am looking forward for your support. I have a folder in which there are multiple text files with same format. I would like to have out in excel sheet in the following format:

Column A = Text File Name
Column B = Count of records for each text file
Column C = Total Sum Value of data from places 9 to 11 after doing fixed width for each text file

The input text files and expected output samples are attached

Regards

 

[jindon]

Try

Sub test()
    Dim myPth As String, fn As String, x, e, n As Long, ttl As Double
    myPth = ThisWorkbook.Path & "\"
    fn = Dir(myPth & "*.txt"): n = 1
    Do While fn <> ""
        x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(myPth & fn).ReadAll, vbNewLine)
        For Each e In x
            ttl = ttl + Val(Mid$(e, 9, 3))
        Next
        n = n + 1
        Cells(n, 1).Resize(, 3).Value = Array(fn, UBound(x) + 1, ttl)
        ttl = 0: fn = Dir
    Loop
End Sub

 

[amit_gupta123]

Thanks a lot..it's working

A small modification is required...the length of text a single line is 11...so in case any line having length less then 11 or blank or any spl character should be omitted. Is it possible then it would be wonderful....

Kudos to u!!

 

[jindon]

Just change to

            If Len(e) > 10 Then ttl = ttl + Val(Mid$(e, 9, 3))

 

[amit_gupta123]

I did the same as under but the blank rows are getting counted

Sub test()
Dim myPth As String, fn As String, x, e, n As Long, ttl As Double
myPth = "C:\Users\AMIT\Desktop\test" & "\"
fn = Dir(myPth & "*.txt"): n = 1
Do While fn <> ""
x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(myPth & fn).ReadAll, vbNewLine)
For Each e In x
If Len(e) > 10 Then ttl = ttl + Val(Mid$(e, 9, 3))
Next
n = n + 1
Cells(n, 1).Resize(, 3).Value = Array(fn, UBound(x) + 1, ttl)
ttl = 0: fn = Dir
Loop
End Sub

 

[jindon]

OK, try change to

Sub test()
    Dim myPth As String, fn As String, x, e, n As Long, t As Long, ttl As Double
    myPth = ThisWorkbook.Path & "\"
    fn = Dir(myPth & "*.txt"): t=1
    Do While fn <> ""
        x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(myPth & fn).ReadAll, vbNewLine)
        For Each e In x
            If Len(e) > 10 Then ttl = ttl + Val(Mid$(e, 9, 3)): n = n + 1
        Next
        t = t + 1
        Cells(t, 1).Resize(, 3).Value = Array(fn, n, ttl)
        ttl = 0: n = 0: fn = Dir
    Loop
End Sub

 

[amit_gupta123]

Superb Superb Superb!!!

I have just started learning VBA and wish I could gain at least 10% of knowledge you have...

Amazing