Count and sum the data in text file

[amit_gupta123]

Hii all

I am totally new to vba. I am figuring out a solution wherein I have list of text files in excel and all text files with same name are in a folder. There are around 40 files. I just want that int he adjacent two columns count of item in a particular file and the sum of amount which starts at col no 27th and ends at 39 in text file.
The macro check the file names mentioned in col A then reflect the count and amount and then it goes to next file in the list and it continuous till last file

A sample text file is attached here with

Regards

 

[Hui]

If the files are all in 1 directory you can use this:

Sub LoopThroughFolder()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim MyVal As Double

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Users\xxx\Desktop\xxxx") 'Change directory to suit

MyVal = 0

For Each objFile In objFolder.Files
  If Right(objFile, 3) = "txt" Then
  Open objFile For Input As #1

  While Not EOF(1)
  Line Input #1, Dataline ' read in data 1 line at a time
  MyVal = MyVal + CDbl(Mid(Dataline, 27, 13))

  Wend



  ' Close before reopening in another mode.
  Close #1
  End If
Next objFile
MsgBox "Myvalue = " + CStr(MyVal)

End Sub

I have assumed that the values in Columns 27-39 are as listed below


eg: the number is 9945000 = 9.945Million

 

[amit_gupta123]

Sir ..thks for the response...but the exact output is like as under


The macro should pick the first file""test"and display the count in the file and sum of values from 27 to 39 column then it should go to next file and display the count and sum and so on...

Thanks in advance for help

Regards

 

[Marc L]

Hi,

that needs almost a crystal clear explanation !
​

 

[amit_gupta123]

Hii...May i would be able to explain the same as under

I have few text files in a folder which are named as mentioned in above excel screenshot. All text files have same length of data. The number of items in each text files is different. I want that the count of items in a particular file get displayed in adjacent column B and the sum of entries in text file (col 27-39) get dispalyed in column C. I am attaching one sample text file for the same

I hope this time i am able to explain

Regards

 

[Marc L]

No answer yet ? So maybe stills unclear !

I just took a glance to Hui's code,
why don't you try to amend it to suit your needs ?

And don't forget, Macro Recorder is the VBA noob best friend !​