Calculation based on specific word in a text file using Visual Basic

[brat33]

I am in the process of modifying some code that reads a .txt file after it has been created from .pdf (this works great) and find a specific line in the text file. Once I have found this line (it will be in every text file but on a different line) I then want to use the line directly below it to complete a calculation. I can also go about it like this: I can go to the end of the text file (no clue as to how to program this) and subtract 6 from the line count to do the calculation on the correct line. The line I am trying to get at is always the 6th line from the bottom, but varies from the top of the file.

Currently I have something like this:
If InStr(lineNo, "Credits Debits Difference") Then
lineNo = lineNo + 1
For Each i In Split(line)
If (IsNumeric(i)) Then
totalItems = totalItems + i
End If
Next
End If

Credit Debit Difference is the text I am looking for in the txt file. lineNo is just a variable I set up to advance and read each line in the text file.

Thank you for any suggestions.

[vb5prgrmr]

Dim FileContents As String, LineArray() As String
Dim FNumb As Integer, FName As String
 
FName = "PathFileName"
FNumb = FreeFile
 
Open FName For Input As #FNumb
FileContents = Input(FileLen(FName, #FNumb)
Close #FNumb
 
LineArray = Split(FileContents, vbNewLine)
 
'line to work with = LineArray(UBound(LineArray) - 6)
 

Good Luck

[brat33]

*****if replied twice - sorry!******
Makes perfect sense to me...BUT I am still having a hard time implementing it. This is what I have now:

dim lineArray() as string, lineContent as string
dim tempLine as string, fNumb as integer
dim itemsFile

itemsFile = txtFilePath
fNumb = FreeFile

'open same file in another instance for a second reading
open itemsFile for input as #fNumb
lineContent = input(FileLen(itemsFile, #fNumb)
close #fNumb

'read each line of file into new line in the array.
lineArray = Split(linecontent, vbNewLine)

'goto end of array and back up 6 lines to get to correct line with totals
tempLine = lineArray(UBound(lineArray) - 6)

'split the line totals into seperate values
For Each i In Split(tempLine)
if (IsNumeric(i)) then
'add values into total field
totalItems = totalItems + i
End if
Next

'close out 2nd occurance of file
itemsFile.close


This is all placed inside of a while loop that is reading the file for the initial time. My if statement above opens the same file into a completely different named object. I am assuming that the 2nd time the file is opened it can be read in it's entirety.
When I run this, nothing happens. I have put in place message boxes to see if it will even pop them up, and nothing happens. No error messages pop up either. Something is wrong and I just cannot get my mind around it yet. If I remove/comment out the above code, then the program will run and create the message boxes and even a blank e-mail that I asked it to do at the end. This is the one piece of code that is not functioning as I would like it to. This code also has all separate variables EXCEPT for the totalItems, which was declared at the top of program.
Thank you for any additional suggestions you may have! I know what it should do, I just can not get it written correctly. I am sure I will feel stupid when I am told "oh your missing a comma", but I am at my wits end with this one!

[Guido Geurs]

dear,
there is a ")" missing in:

'open same file in another instance for a second reading
open itemsFile for input as #fNumb
lineContent = input(FileLen(itemsFile, #fNumb)close #fNumb


must be:

lineContent = Input(FileLen(itemsFile), #fNumb)



br,

[brat33]

Thank you for that! unfortunatlely that did not resolve my issue. It still will not run.

Is it even possible to take 1 text file - read it into a location1 and perform a while loop based on each line in location1 and not being at end of file; at the same time, inside the loop reopen the same text file into location2 and perform some steps, then close out of file location2 while returning to location1. I know this sounds confusing, but I can not think of another way to perform these steps.

[Guido Geurs]

dear,

attached is a working example.

br,

[Guido Geurs]

dear,

If you want the 6e last line in a txt file you must use the code=

  tempLine = lineArray(UBound(lineArray) - 5)

Ubound is an index and not the linenumber !
The indexes starts at 0 and ends at (linenumber -1)

A file with 100 lines gives:

line 93 => index 92
line 94 => index 93
line 95 => index 94 (-6)
line 96 => index 95 (-5)
line 97 => index 96 (-4)
line 98 => index 97 (-3)
line 99 => index 98 (-2)
line 100 => index 99 (-1)

so ubound= 99
the 6e last line (line 95) = index 94 = ubound-5 = 99-5 =94

see attachment

br,

[brat33]

I am starting to feel like I felt my first semester in College. Really I do have a degree in programming...it may not seem like it, but I do :)

Code - AGAIN:
Set totalsFile = fso.OpenTextFile(txtFilePath, 1)
While Not totalsFile.AtEndOfStream
line = totalsFile.Readline
If (usingSCO = True) Then
If (lineNo = 13) Then
If (InStr(line, " ") > 0) Then
totalAmount = totalAmount + Replace(Split (line) (0), "$", "")
Else
totalAmount = totalAmount + Replace(line, "$", "")
End If
End If
If (InStr(line, "Item Count (CR/DB)") > 0) Then
For Each i In Split(line)
If (IsNumeric(i)) Then
totalItems = totalItems + i
End If
Next
End If
Else
If (lineNo = 13) Then
Dim tempArray
tempArray = Split(line)
totalAmount = totalAmount + tempArray(UBound(tempArray) - 1)
End If

if (inStr(,line,"Credits Debits Difference") > 0 ) then
dim lineArray()
dim itemLine
dim fileBuff
Set fileBuff = fso.OpenTextFile(txtFilePath, 1)
lineArray = Split(fileBuff, vbCrLF)
itemLine = lineArray(uBound(lineArray))
For Each i In Split(itemLine)
if (IsNumeric(i)) Then
totalItems = totalItems + i
End If
Next
End If
End If
lineNo = lineNo + 1
Wend
totalsFile.Close

Code in bold is where I am having problems. Everything else works great! If I take out the bold code, and just set my totalItems to a random number it works great as well. ERROR is "Subscript Out Of Range".
I read a pdf file, convert it to a text file , and read that text file while performing some tasks. Once inside the while loop, I am trying to open the SAME text file into a different location to be read and get to the 6th line from the bottom of the file, add the two numbers on that line together and be done. This is the last step in the logic before the file gets closed and the totals are written to a generated e-mail. I have tried different open methods, different replace, instr, and if statements, all to no avail. I can not get it coded to work. I am using Microsoft Development Environment to code this. I will attach a copy of the text file as well this time.

[Guido Geurs]

dear,

there is a "," to much in the line:

if (inStr(,line,"Credits Debits Difference") > 0 ) then

must be:

if (inStr(line,"Credits Debits Difference") > 0 ) then

Are You using VB6 ?
Normally You can detect such errors in VB because they are RED (see Attachment GIF with a screencapture of VB editor).
The editor colors are set in Tools - Options (see attached GIF)

The brackets and ">O" is also not needed in the if..endif.
It can be writen as:

If InStr(Line, "Credits Debits Difference") Then

Please use the "Wrap CODE tags..." = > the # button in the menu of the BYTES replay editor.
How to: select ALL the lines with code and click on the # in the menu.

This is much easier to read for us and we don't have to unravel Your code again to see the structure.

br,

[Guido Geurs]

dear,

In Your code is the line=

               itemLine = lineArray(UBound(lineArray))

But this is the last line of the file !!!.
must this not be the 6e last line with data ?? or=

               itemLine = lineArray(UBound(lineArray)-5)

br,

[Guido Geurs]

dear,

When I run Your program, Line 13 = line with NOTHING between :

Count 141 150 Diff .00 .00 190,677.70 190,677.70 .00

User Name

Your counter "lineNo" is at the END of the code so these are the steps:

You reed line 1 in the "line" and LineNo =0
You add 1 to lineNo => is now 1
You reed the second line but the counter is still at 1 !!!

You check for line 13 (LineNo=13) but the "Line" has the data of line 14 = BLANCO

The linecounter must be at the beginning of the code: direct before of after the reeding of the var "Line" like this:

   Set totalsFile = fso.OpenTextFile(txtFilePath, 1)
      lineNo = 0
      While Not totalsFile.AtEndOfStream
         Line = totalsFile.Readline
         lineNo = lineNo + 1
         If usingSCO Then

br,

[Guido Geurs]

dear,

the line :

               lineArray = Split(fileBuff, vbCrLf)

gives the errror = Run-Time error '438' Object doesn't support this property or method !!

Because you can't split a link to a file (= fileBuff).
There is no data in it.

Why reading the file twice ???
I see now what you are trying to do with the TXT file.
I will see if I can write the code in a different way and using an array with the lines in it.

br,

[Guido Geurs]

dear,

I have rewritten the code in which I read the file once in an array.(see attachment)
Are the results what you want?

br,

[brat33]

Thank you very much for your assistance on this issue! It is now working like a champ! Took a few days off to work on other items, and came back to it, and what do ya know - works like I intended it to in the first place! Thank you again!