get total

[Footbal76]

I have this code it works great.
It opens a file and searchs for a value. once its done with that file it goes to the next file. I have 3 files total.
I need to give the total of the value ID. in each file
Does anyone know how to do this?

Thanks in advance

Dim File As String
Dim TextLine As String
Dim ssn As String
Dim name As String
Dim i As Integer
Dim stucntr As Integer
Dim filecntr As Integer
stucntr = 0
For filecntr = 1 To 3
File = "C:\VB_PROJECTS\File\JQ0" & CStr(filecntr) & "H301"
If (FileExists(File) = True) Then
Open File For Input As #1
If Not EOF(1) Then
Do While Not EOF(1)
Line Input #1, TextLine
If InStr(TextLine, "ID:") > 0 Then
MsgBox "hello"
End If
Loop
Close #1
End If
End If
Next

 

[gmmastros]

In order to get good advice about this, you'll need to show us an example of what the file looks like. When posting the data, please, no email addresses, names, etc...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Footbal76]

Here is an example. but it would have more than 50 or so
I want to get the total of IDs
ID:2067
BIOLOGICAL SCI
AUTUMN QUARTER 2003
ELEMNTRY 1:CLASSRM GERMAN 101.01 5
ALGEBRA&TRIG&APPL MATH 148 4
PREP FOR PHYSICS PHYSICS 109 4
GENERAL PSYCHOLOGY PSYCH 100 5
ARTS & SCI SURVEY USAS 100.11 1

ID:1212
SCI
AUTUMN QUARTER 2002
FLD EXP-LANG LT RD EDU T&L 884.56 3
SEM-LANG LIT RD EDU T&L 925.56 3
PREP FOR PHYSICS PHYSICS 109 4

 

[gmmastros]

Dim File As String
Dim TextLine As String
Dim ssn As String
Dim name As String
Dim i As Integer
Dim stucntr As Integer
Dim filecntr As Integer
    
[!]Dim lngTotal As Long[/!]

stucntr = 0
For filecntr = 1 To 3
    File = "C:\VB_PROJECTS\File\JQ0" & CStr(filecntr) & "H301"
    If (FileExists(File) = True) Then
        Open File For Input As #1
        If Not EOF(1) Then
            Do While Not EOF(1)
                Line Input #1, TextLine
                If InStr(TextLine, "ID:") > 0 Then
                    [!]lngTotal = lngTotal + Split(TextLine, ":")(1)[/!]
                End If
            Loop
            Close #1
        End If
    End If
Next

[!]MsgBox lngTotal[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Golom]

I need to give the total of the value ID. in each file

As I read your code it looks like "ID:" is (or may be) a string that occurs within that you have read called "TextLine". Do you mean that you want to count the number of times "ID:" occurs?

If so

Dim File                As String
Dim Counts(1 To 3)      As Long
Dim i                   As Integer
Dim a()                 As String

For i = 1 To 3
   File = "C:\VB_PROJECTS\File\JQ0" & CStr(i) & "H301"

    With New FileSystemObject
        a = Split(.OpenTextFile(File).ReadAll, "ID:")
    End With
    Counts(i) = UBound(a)
Next

 

[Footbal76]

I tried what you indicated I get an error

run-time error 13
type mismatch

any suggestions

 

[Golom]

You tried which one?

George's or mine?

 

[gmmastros]

Also, can you please explain what you mean by 'total'. I took it to mean 'the sum of the id values'. Golom understood it to mean 'the number of times ID: occurs in the file'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Footbal76]

Sum, total. the number of times ID is in the file

 

[Footbal76]

George's had the time error

 

[Footbal76]

Golom

I get a Object not defined on: With New FileSystemObject

suggetions
thanks

 

[Golom]

You need a reference to Microsoft Scripting Runtime

 

[gmmastros]

I would suggest that you take a closer look at your data files. If my 'solution' is causing errors, then you must have bad data. Let me explain.

With my solution, you are looping through your data file, line by line. If the line contains "ID:" then my solution would split the data on the colon and treat whatever follows as a long. Since you got a type mismatch error, there must be a line in the file with ID: but with some other alpha characters following it. If this is the case, then golom's solution may not give the correct results either. It could, but you should consider this as a wake up call to check your data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom