• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel vba read text file and extract numbers based on date

mshome

New Member
hi,
excel 2010 windows 7
i want to read from text files in one directory the total of the numbers to the right of "From Data" from all cycles dated 01-Sep-2015

if user input "01-Sep-2015" then the function return total of all numbers to the right of "from data" from all cycles with date 01-Sep-2015 in this data sample answer 17+18=35

the text files can contain multiple cycles like 1-sep-2015 2-sep-2015 and on ....
sample data attached.

this my code
Code:
Function FSCount(CyDA As String) As Integer
    Dim myfile As String
    Dim FileName As String
    Dim FrDA As Integer
   
   
    folderName = Cells(2, 11).Value


    FileName = Format(DateSerial(2000 + Left(CyDA, 2), Mid(CyDA, 3, 2), Right(CyDA, 2)), "yyyy_mmm")

    i = 1
    myfile = Dir(folderName & "\FS_QC_Count_Log_" & "*.txt")
    Do While myfile <> ""
        Open folderName & "\" & myfile For Input Access Read As #1

        Do While Not EOF(1)
             
                Line Input #1, EntireLine
                    SearchKey = "Cycle Date"
                    SearchKey2 = "From Data"
                    If CyDA = InStr(1, EntireLine, Right(SearchKey, 11)) Then
                    FrDA = Trim(InStr(1, EntireLine, Right(SearchKey2, 10), 1))
                   
            End If
          Close #1
    Loop
    Loop
End Function
the function return #invalid!
 

Attachments

  • FS_QC_Count_Log_2015_Sep_02.txt
    1.4 KB · Views: 19

Hi !

The function returns nothing ! You forget to return value !
Just see VBA help of Function statement and its sample …

You close the file before the end of it just after first line read !
Close the file after the loop of reading lines …

The search keys are on the same line ? No !
So why they are searched on the same line ? Bad conception !
You must first check date line. Once found, put a flag on.
When flag is on then for next lines, check the second key …
Once second key is found, set flag off …

FileName variable and code line i = 1 : what's for ?‼
 
Thanks marc L
Code:
Function FSCount(CyDA As String) As Integer
    Dim myfile As String
    Dim FrDA As Integer
   
   
    folderName = Cells(2, 11).Value

    myfile = Dir(folderName & "\FS_QC_Count_Log_" & "*.txt")
    Do While myfile <> ""
        Open folderName & "\" & myfile For Input Access Read As #1
       
            Do While Not EOF(1)
                         
            Line Input #1, EntireLine
            SearchKey = "Cycle Date"
                               
            Line Input #2, EntireLine
            SearchKey2 = "From Data"
                               
                If CyDA = InStr(1, EntireLine, Right(SearchKey, 11)) Then
                FrDA = Trim(InStr(1, EntireLine, Right(SearchKey2, 10), 1))
                                   
                End If
                     
            Loop
        Close #1
    Loop
FSCount = FrDA
End Function

1- removed the unnecessary i=1 & filename
2- closed after the loop
3- added return value

how to flag date once found ?
 
You scan only first text file, you forgot to scan others,
read Dir VBA help as its sample and check this code :​
Code:
Function FSCount(CyDA As String) As Long
      Dim myfile As String, FrDA As Long
  
    FolderName$ = Cells(2, 11).Value & "\"
         myfile = Dir(FolderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open FolderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
             Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "From Data *" Then
                    FLAG = 0
                    FrDA = FrDA + Split(Application.Trim(EntireLine))(1)
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
         FSCount = FrDA
End Function
 
You scan only first text file, you forgot to scan others,
read Dir VBA help as its sample and check this code :​
Code:
Function FSCount(CyDA As String) As Long
      Dim myfile As String, FrDA As Long
 
    FolderName$ = Cells(2, 11).Value & "\"
         myfile = Dir(FolderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open FolderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
             Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "From Data *" Then
                    FLAG = 0
                    FrDA = FrDA + Split(Application.Trim(EntireLine))(1)
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
         FSCount = FrDA
End Function
appreciate your help Mr.Marc L
this code return 0 it should return 35
 

So check CyDA variable …

Follow code in step by step mode via F8 key and
control variables in Locals window to understand your issue …

This demonstration works on my side (Split index corrected) :​
Code:
Function FSCount(CyDA As String) As Long
      Dim myfile As String, FrDA As Long
    FolderName$ = Cells(2, 11).Value & "\"
         myfile = Dir(FolderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open FolderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
             Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "From Data *" Then
                    FLAG = 0
                    FrDA = FrDA + Split(Application.Trim(EntireLine))(2)
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
         FSCount = FrDA
End Function

Sub Demo()
    MsgBox FSCount("01-Sep-2015")
End Sub
 
Last edited:
So check CyDA variable …

Follow code in step by step mode via F8 key and
control variables in Locals window to understand your issue …

This demonstration works on my side (Split index corrected) :​
Code:
Function FSCount(CyDA As String) As Long
      Dim myfile As String, FrDA As Long
    FolderName$ = Cells(2, 11).Value & "\"
         myfile = Dir(FolderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open FolderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
             Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "From Data *" Then
                    FLAG = 0
                    FrDA = FrDA + Split(Application.Trim(EntireLine))(2)
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
         FSCount = FrDA
End Function

Sub Demo()
    MsgBox FSCount("01-Sep-2015")
End Sub

This Code Works if i Follow the code in step by step mode via F8 key and
control variables in Locals window
i hold f8 until it loops all text file it return the correct amount 35
but if i do FsCount(1-Sep-2015) it return 0
i think because it get FrDA Value before it loop the text files which at that moment FrDA =0
any idea how to get FrDA Value after it Loops all text files ?
And CyDA Date Take From the sub FSCount("01-Sep-2015") ?
CyDA Is an Input by user
like if i want 2-sep-2015
i do FSCount(2-sep-2015), FSCount(3-sep-2015) and on
 
Last edited:

So like you forgot to specify worksheet for the FolderName cell,
maybe the active worksheet is not the right worksheet …

Text for date must be enclosed within doublequotes !

No issue on my side :

Output1.jpg
 
So like you forgot to specify worksheet for the FolderName cell,
maybe the active worksheet is not the right worksheet …

Text for date must be enclosed within doublequotes !

No issue on my side :

Thank you very much Mr.Marc L it work perfectly if i do FSCount("1-Sep-2015")
but is it possible to take the date from B* cell like =FSCount("B6")
in B6 i have 9/1/2015
when i do =FSCount("B6") it return 0
 
You must respect TBTO rule !

"B6" is just a string, not the cell text like Range("B6").Text

If cell is not text but a date, you must format cell like dd/mmm/yyyy.

Think, But Think Object !
 
Last edited:

B6 cell is text or date ?!

If date (best), cell must be formatted like text file : dd/mmm/yyyy
Or use the Format VBA function …

If text, you must think to place a zero before string for day < 10
and to set date with four digits
'cause "2-Sep-15" can't be equal to "02-Sep-2015" ! …
 
Last edited:
B6 cell is text or date ?!

If date (best), cell must be formatted like text file : dd/mmm/yyyy
Or use the Format VBA function …

If text, you must think to place a zero before string for day < 10
and to set date with four digits
'cause "2-Sep-15" can't be equal to "02-Sep-2015" ! …
B6 cell is Date
no matter if formatted dd-mmm-yy or dd-mmm-yyyy still get 0
even =FSCount(CHAR(34)&B6&CHAR(34)) return 0
 

tete-yoda-3d.gif

As I yet wrote in post #10, the text cell reference is Range("B6").Text

So instead of FSCount("01-Sep-2015") : FSCount(Range("B6").Text)
 
tete-yoda-3d.gif

As I yet wrote in post #10, the text cell reference is Range("B6").Text

So instead of FSCount("01-Sep-2015") : FSCount(Range("B6").Text) …​
i get there is problem with the formula and highlight .Text
but i got it
=FSCount(TEXT(B6,"dd-mmm-yyyy"))

Thank You Mr.Marc For you help.
 

Oh, you use it as a personal function in an Excel formula !

My answer was to use it in VBA like my Demo procedure …
As a personal function, easier is to set CyDA as Range
and use in code CYDA.Text or via Format VBA function …
Then easy formula is =FSCount(B6)
 
Oh, you use it as a personal function in an Excel formula !

My answer was to use it in VBA like my Demo procedure …
As a personal function, easier is to set CyDA as Range
and use in code CYDA.Text or via Format VBA function …
Then easy formula is =FSCount(B6)
hmmmmmm
seems that if the text inside text file separated by tabs the return is 0
any idea how to fix that ?
 

So it seems you did'nt attach the good file !

Anyway, you can also use Split VBA function …

 
So it seems you did'nt attach the good file !

Anyway, you can also use Split VBA function …

i rushed it thinking it was spaces

hmmm
i tried with vbTab and Chr(9)
It Docent work what am i doing worng here ?

Code:
Function FSCount(CyDA As String) As Long
      Dim myfile As String, FrDA As Long, TempNum As String
    FolderName$ = "C:\LOGS\LOG 1\"
        myfile = Dir(FolderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open FolderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
            Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "From Data *" Then
                    FLAG = 0
                  FrDA = FrDA + Split(Application.Trim(EntireLine), Chr(9))(2)
               
                 
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date "), Chr(9), (1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
        FSCount = FrDA
End Function

Sub demo()
FSCount ("01-Sep-2015")
End Sub

i used F8 to check vba code and it dose't even flag
 

variable = Split(string, delimiter)

In step by step mode via F8 key, check variable value in Locals window …

 
variable = Split(string, delimiter)

In step by step mode via F8 key, check variable value in Locals window …

vbtab delimiter
should it be in first or second split
"Split(Split(EntireLine, " Cycle Date ")(1))(0)"
 

In first (outer) obviously !
As written in Split VBA help, space character is the default delimiter.
And yes to separate tabulations, use vbTab constant as delimiter …

To understand how it works : a Split, a variable.

See samples in this thread, posts #8, 12 & 22 …​
 
Last edited:
Thanks mr.Marc it works now
added ,Vbtab after (1)
one more thing i notice some text files have the same product for the same cycle
whats the best approach to prevent the code from summing the same product for the same cycle twice ?"due to operator mistake"
like
folder A text file contain cycle 01-sep-15 product Z1
from data 13
folder B text file contain cycle 01-sep-15 product Z1
from data 13
 
Back
Top