Extracting Specific Line Of Data From Text File Using VBA

[galang_ofel]

Hi,
Please assist in creating vba code that will automate current task as follows,
- manually open each file to check if complete with volume numbering 200+ accounts.
- It was made by checking value of the ending balance - normal - ist file as againts ending
balance of the next, if values are equal it means files complete,if not incomplete.
Sample content of file
file 1
,,,,,"BEGINNING BALANCE-NORMAL","C","","11602603.58"
,,,,,"ENDING BALANCE-NORMAL","C","","9301134.07"
File 2
,,,,,"BEGINNING BALANCE-NORMAL","C","","9301134.07"
,,,,,"ENDING BALANCE-NORMAL","C","","4907521.54"

File format is text and comma deliminated
Data to look for is located on top- 1st and 2nd row

I got several codes from other forums and tried to edit them to fit my requirements but to no avail.
Because at this point I, cannot create original (made by me) code successfully as my knowlede in vba is limited, My macro programs are just copied from forums here and somewhere else.

Thank you in advance

Ofel

[Excel Fox]

And the name of the text files are all in sequence?

[galang_ofel]

Hi,
Yes name of the text files are all in sequence.

Thanks a lot
ofel

[Excel Fox]

And how many lines of text will these text files have? And are you looking for the very first line and the last line? Or can those be anywhere? Can you post two sample text files?

[galang_ofel]

Hi,
My apology for the late reply, as requested please find sample file attached

Thanks

Ofel

[bakerman]

This should get you started.
Gives Name, BeginBalance and EndingBalance for each file in directory.

Sub ReadTextFiles()
'Set Reference to Microsoft Scripting Runtime (Tools > References)
Const TextDir = "G:\My documents\TextFiles\" 'adjust accordingly
Dim FSO As FileSystemObject, FSOStream As TextStream
NumFiles = Format(CreateObject("scripting.filesystemobject") _
.GetFolder(TextDir).Files.Count, "0000")
ReDim sn(1 To NumFiles, 1 To 3)
Set FSO = New FileSystemObject
fn = Dir(TextDir & "*.txt")
i = 1
Do While fn <> ""
Set FSOStream = FSO.GetFile(TextDir & fn) _
.OpenAsTextStream(ForReading, TristateUseDefault)
sn(i, 1) = fn
sn(i, 2) = Split(FSOStream.ReadLine, ",")(8)
sn(i, 3) = Split(FSOStream.ReadLine, ",")(8)
fn = Dir()
i = i + 1
Loop
With Sheets(1).Cells(1) 'puts data in 1st sheet, change to need
.CurrentRegion.ClearContents
.Resize(UBound(sn, 1), UBound(sn, 2)) = sn
End With
End Sub




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[galang_ofel]

Hi,
Problem solved, Thanks a lot