Reading Text file and splitting dynamic sized string into array help

B

BalloutMoe

Board Regular
Joined
Jun 4, 2021
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Hello all I have the following in a text file that is being read by vba and going line by line

60ANDREW COX 0.00A08:00 09:58 ANDREW COX
60DAVID BEAUMONT 0.00A08:08 13:26 14:09 18:00 D. BEAUMONT
60JOSEPG CAMACHO 0.00A08:04 11:10 11:41 18:01 JO CAMACHO
60JUAN CABRERA 0.00A08:00 18:02 JUAN CABRERA
60ROBERT ELMORE 0.00A08:04 18:02 ROBERT ELMOR
60RUBEN CABRERA 0.00A15:00 18:02 RUBEN CABRER
60TRISTON HILL 0.00A08:02 11:52 12:27 18:02 TRISTON HILL
60JOSE VARGAS 0.00A08:19 16:22 16:54 18:01 VARGAS
99 END OF DAY

I would like to split the strings for example
David Beamount08:0813:26
David Beamount14:0918:00
Josepg Camacho08:0411:10
Josepg Camacho11:4118:01

and So on for each employee. Sometime the row can have a bunch of different time clocks. Each two represent a clock in and clock out. How can I split it dynamically and enter it into a sheet? I have the following code below it works but only first the first clock and clock out. Thank you for your help

VBA Code: 
  For i = 0 To UBound(arrTxt)
  
      If InStr(arrTxt(i), todaysdate2) > 0 Then
         Do While InStr(arrTxt(i + j), "END OF DAY") = 0
            
            j = j + 1
            
            For t = 2 To 17
                                   If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
                                    'SI = InStr(arrTxt(i + j), "60ANDREW ")
                                    arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")
                                    
                                    arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)
                                    arrTime(2) = Split(arrTime(2), "A")(1)
                                    
                                            If arrTime(2) <= "08:10" Then
                                            arrTime(2) = "08:00"
                                            End If
                                     
                                            If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then
                                            arrTime(3) = "18:00"
                                            End If
                          
                
                                            lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
                                            On Error Resume Next
                                            ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(1, 1).Value = arrTime(0)
                                            ThisWorkbook.Worksheets("Times").Range("B" & lastR2 + 1).Resize(1, 1).Value = arrTime(2)
                                            ThisWorkbook.Worksheets("Times").Range("C" & lastR2 + 1).Resize(1, 1).Value = arrTime(3)
                                            ThisWorkbook.Worksheets("Times").Range("D" & lastR2 + 1).Resize(1, 1).Value = todaysdate
                                            
                                  
                                   End If
                 Next
                                
         Loop 'For Do

      End If 'If Instr
  Next i
 
Sort by date Sort by votes
johnnyL said:
Sorry, I got side tracked with grandchild and dinner.

I haven't tested any of this code, just wrote it out in my head. Try it out and let me know how it goes. If it does not go well, perhaps you could include some more code that shows how exactly you arrive at the point of code that you did post.

Anyways, try replacing the code you posted in post #1 with:

VBA Code: 
    Dim arrTimeSlot         As Long
    Dim ClockTimesProcessed As Long, TimeClocksFound    As Long
    Dim OutputArrayRow      As Long
    Dim OutputArray         As Variant
'
    ReDim OutputArray(1 To 4, 1 To 10000)
    arrTimeSlot = 0
    ClockTimesProcessed = 0                                                                                         ' Initiate ClockTimesProcessed
    OutputArrayRow = 0
'
    For i = 0 To UBound(arrTxt)
        If InStr(arrTxt(i), todaysdate2) > 0 Then
            Do While InStr(arrTxt(i + j), "END OF DAY") = 0
                j = j + 1
'
                For t = 2 To 17
                    If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & _
                            ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
'                       SI = InStr(arrTxt(i + j), "60ANDREW ")
                        OutputArrayRow = OutputArrayRow + 1                                                     ' Increment OutputArrayRow
                        arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")                             ' Get Data Line into arrTime
'
                        TimeClocksFound = UBound(arrTime) - 3                                                   ' Calculate the # of TimeClocksFound
'
                        arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)                  ' Get the First & Last Name from beginning
                        OutputArray(1, OutputArrayRow) = arrTime(0)                                             ' Save the Employee Name into OutputArray
'
                        arrTime(2) = Split(arrTime(2), "A")(1)                                                  ' Get the first Time
                        If arrTime(2) <= "08:10" Then arrTime(2) = "08:00"                                      ' Adjust the first Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(2, OutputArrayRow) = arrTime(2)                                             ' Save the first clock in time into OutputArray
'
                        If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then arrTime(3) = "18:00"            ' Adjust the second Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(3, OutputArrayRow) = arrTime(3)                                             ' Save the first clock out time into OutputArray
'
                        OutputArray(4, OutputArrayRow) = todaysdate                                             ' Save todaysdate into OutputArray
'
'---------------------------------------------------------------------------------------------------------------
'
                        If ClockTimesProcessed = TimeClocksFound Then GoTo NextLineOfData                       ' If no more TimeClocks then goto next Data
'
                        arrTimeSlot = 3                                                                         ' Initialize arrTimeSlot
'

                        Do While ClockTimesProcessed < TimeClocksFound
                            arrTimeSlot = arrTimeSlot + 1                                                       ' Increment arrTimeSlot
                            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
'
                            OutputArray(1, OutputArrayRow) = arrTime(0)                                         ' Save the Employee Name into OutputArray
'
                            If TimeClocksFound - ClockTimesProcessed = 2 Then                                   ' If last set of TimeClocks found then ...
                                If arrTime(arrTimeSlot) <= "08:10" Then arrTime(arrTimeSlot) = "08:00"          ' Adjust the clock in Time if needed
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                If arrTime(arrTimeSlot) >= "17:53" And arrTime(arrTimeSlot) <= "18:25" Then
                                    arrTime(arrTimeSlot) = "18:00"                                              ' Adjust the clock out Time if needed
                                End If
'
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            Else
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            End If
                        Loop                                                                                    ' Loop back for next ClockTime
                    End If
NextLineOfData:
                Next
            Loop 'For Do
        End If 'If Instr
    Next i
'
    lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
    ReDim Preserve OutputArray(1 To 4, 1 To OutputArrayRow)
'
    ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(UBound(OutputArray, 2), UBound(OutputArray, 1)) = Application.Transpose(OutputArray)
End Sub
Click to expand...
Thank you for your code, I tried it however its still just putting the employees first clock in and out instead of each row
ANDREW COX
8:00​
9:58​
2/21/2022​
DAVID BEAUMONT
8:00​
13:26​
2/21/2022​
JOSEPG CAMACHO
8:00​
11:10​
2/21/2022​
JUAN CABRERA
8:00​
18:00​
2/21/2022​
ROBERT ELMORE
8:00​
18:00​
2/21/2022​
RUBEN CABRERA
15:00​
18:00​
2/21/2022​
TRISTON HILL
8:00​
11:52​
2/21/2022​
JOSE VARGAS
8:19​
16:22​
2/21/2022​

this was the result. I can do the changes after but I just need to sperate each two times with the employee name
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
i forgot the actual date
Sub splitten()
Dim Result(), t_IN, t_OUT

a = Sheets("blad3").Range("a1").CurrentRegion.Value 'read your data to an array (or another method to get those data into an array)
ReDim Result(1 To UBound(a) * 3, 1 To 4) 'prepare result array with (precaution) 3 timeslots per employee

For i = 1 To UBound(a) 'loop through the data
sp = Split(a(i, 1), "0.00A") 'split on this string
If UBound(sp) = 1 Then 'there are 2 parts
sp1 = Split(sp(1)) 'split the 2nd part on the spaces
For j = 0 To UBound(sp1) Step 2 'loop through the times (per 2, in and out)
If InStr(sp1(j), ":") = 0 Then Exit For 'if there is no ":" in that part, then it is no longer a time, so quit
ptr = ptr + 1 'increment pointer
Result(ptr, 1) = WorksheetFunction.Proper(Mid(sp(0), 3)) 'Name

t_IN = TimeValue(sp1(j)) 'actual in
If t_IN <= TimeValue("08:10") Then t_IN = TimeValue("08:00") 'manipulate in
Result(ptr, 2) = t_IN 'add to array

t_OUT = TimeValue(sp1(j + 1)) 'actual out
If WorksheetFunction.Median(TimeValue("17:53"), TimeValue("18:25"), t_OUT) = t_OUT Then t_OUT = TimeValue("18:00") 'manipulate out
Result(ptr, 3) = t_OUT 'add to array
Result(ptr, 4) = CDbl(Date)
Next
End If
Next

ThisWorkbook.Worksheets("Times").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(ptr, 4).Value = Result

End Sub
 
Upvote 0
oops, try replacing the original code in post #1 with:

VBA Code: 
    Dim arrTimeSlot         As Long
    Dim ClockTimesProcessed As Long, TimeClocksFound    As Long
    Dim OutputArrayRow      As Long
    Dim OutputArray         As Variant
'
    ReDim OutputArray(1 To 4, 1 To 10000)
    arrTimeSlot = 0
    ClockTimesProcessed = 0                                                                                         ' Initiate ClockTimesProcessed
    OutputArrayRow = 0
'
    For i = 0 To UBound(arrTxt)
        If InStr(arrTxt(i), todaysdate2) > 0 Then
            Do While InStr(arrTxt(i + j), "END OF DAY") = 0
                j = j + 1
'
                For t = 2 To 17
                    If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & _
                            ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
'                       SI = InStr(arrTxt(i + j), "60ANDREW ")
                        OutputArrayRow = OutputArrayRow + 1                                                     ' Increment OutputArrayRow
                        arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")                             ' Get Data Line into arrTime
'
                        TimeClocksFound = UBound(arrTime) - 3                                                   ' Calculate the # of TimeClocksFound
'
                        arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)                  ' Get the First & Last Name from beginning
                        OutputArray(1, OutputArrayRow) = arrTime(0)                                             ' Save the Employee Name into OutputArray
'
                        arrTime(2) = Split(arrTime(2), "A")(1)                                                  ' Get the first Time
                        If arrTime(2) <= "08:10" Then arrTime(2) = "08:00"                                      ' Adjust the first Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(2, OutputArrayRow) = arrTime(2)                                             ' Save the first clock in time into OutputArray
'
                        If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then arrTime(3) = "18:00"            ' Adjust the second Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(3, OutputArrayRow) = arrTime(3)                                             ' Save the first clock out time into OutputArray
'
                        OutputArray(4, OutputArrayRow) = todaysdate                                             ' Save todaysdate into OutputArray
'
'---------------------------------------------------------------------------------------------------------------
'
                        If ClockTimesProcessed = TimeClocksFound Then GoTo NextLineOfData                       ' If no more TimeClocks then goto next Data
'
'---------------------------------------------------------------------------------------------------------------
'
                        arrTimeSlot = 3                                                                         ' Initialize arrTimeSlot
'

                        Do While ClockTimesProcessed < TimeClocksFound
                            arrTimeSlot = arrTimeSlot + 1                                                       ' Increment arrTimeSlot
                            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
'
                            OutputArray(1, OutputArrayRow) = arrTime(0)                                         ' Save the Employee Name into OutputArray
'
                            If TimeClocksFound - ClockTimesProcessed = 2 Then                                   ' If last set of TimeClocks found then ...
                                If arrTime(arrTimeSlot) <= "08:10" Then arrTime(arrTimeSlot) = "08:00"          ' Adjust the clock in Time if needed
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                If arrTime(arrTimeSlot) >= "17:53" And arrTime(arrTimeSlot) <= "18:25" Then
                                    arrTime(arrTimeSlot) = "18:00"                                              ' Adjust the clock out Time if needed
                                End If
'
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            Else
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            End If
                        Loop                                                                                    ' Loop back for next ClockTime
                    End If
                Next
            Loop 'For Do
        End If 'If Instr
NextLineOfData:
    Next
'
    lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
    ReDim Preserve OutputArray(1 To 4, 1 To OutputArrayRow)
'
    ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(UBound(OutputArray, 2), UBound(OutputArray, 1)) = Application.Transpose(OutputArray)
 
Upvote 0
BSALV said:
VBA Code: 
Sub splitten()
     Dim Result(), t_IN, t_OUT
   
     a = Sheets("blad3").Range("a1").CurrentRegion.Value        'read your data to an array (or another method to get those data into an array)
     ReDim Result(1 To UBound(a) * 3, 1 To 4)                   'prepare result array with (precaution) 3 timeslots per employee

     For i = 1 To UBound(a)                                     'loop through the data
          sp = Split(a(i, 1), "0.00A")                          'split on this string
          If UBound(sp) = 1 Then                                'there are 2 parts
               sp1 = Split(sp(1))                               'split the 2nd part on the spaces
               For j = 0 To UBound(sp1) Step 2                  'loop through the times (per 2, in and out)
                    If InStr(sp1(j), ":") = 0 Then Exit For     'if there is no ":" in that part, then it is no longer a time, so quit
                    ptr = ptr + 1                               'increment pointer
                    Result(ptr, 1) = WorksheetFunction.Proper(Mid(sp(0), 3))     'Name

                    t_IN = TimeValue(sp1(j))                    'actual in
                    If t_IN <= TimeValue("08:10") Then t_IN = TimeValue("08:00")     'manipulate in
                    Result(ptr, 2) = t_IN                       'add to array

                    t_OUT = TimeValue(sp1(j + 1))               'actual out
                    If WorksheetFunction.Median(TimeValue("17:53"), TimeValue("18:25"), t_OUT) = t_OUT Then t_OUT = TimeValue("18:00")     'manipulate out
                    Result(ptr, 3) = t_OUT                      'add to array
               Next
          End If
     Next

     ThisWorkbook.Worksheets("Times").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(ptr, 3).Value = Result

End Sub
Click to expand...

BSALV said:
i forgot the actual date
Sub splitten()
Dim Result(), t_IN, t_OUT

a = Sheets("blad3").Range("a1").CurrentRegion.Value 'read your data to an array (or another method to get those data into an array)
ReDim Result(1 To UBound(a) * 3, 1 To 4) 'prepare result array with (precaution) 3 timeslots per employee

For i = 1 To UBound(a) 'loop through the data
sp = Split(a(i, 1), "0.00A") 'split on this string
If UBound(sp) = 1 Then 'there are 2 parts
sp1 = Split(sp(1)) 'split the 2nd part on the spaces
For j = 0 To UBound(sp1) Step 2 'loop through the times (per 2, in and out)
If InStr(sp1(j), ":") = 0 Then Exit For 'if there is no ":" in that part, then it is no longer a time, so quit
ptr = ptr + 1 'increment pointer
Result(ptr, 1) = WorksheetFunction.Proper(Mid(sp(0), 3)) 'Name

t_IN = TimeValue(sp1(j)) 'actual in
If t_IN <= TimeValue("08:10") Then t_IN = TimeValue("08:00") 'manipulate in
Result(ptr, 2) = t_IN 'add to array

t_OUT = TimeValue(sp1(j + 1)) 'actual out
If WorksheetFunction.Median(TimeValue("17:53"), TimeValue("18:25"), t_OUT) = t_OUT Then t_OUT = TimeValue("18:00") 'manipulate out
Result(ptr, 3) = t_OUT 'add to array
Result(ptr, 4) = CDbl(Date)
Next
End If
Next

ThisWorkbook.Worksheets("Times").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(ptr, 4).Value = Result

End Sub
Click to expand...
Here is a sample of the text file the problem I am having to get the array you mentioned from the text file if you see in the first line it contains the stores id (100001) and the date(021922)
I would like to use the array from that line until the line (99 END OF DAY )

100001021922 200.0011123358 007:01TERRELLL WHO
12 4548.39123418 761.89 0.00 968.71 2220.10 513.71 0.00 0.0014:10 83.98 0.00 0.00 0.00 0.00 0.00X1 0.00 0.00
13A 0.00 24.00 383.72 0.00 2 56 24.00 4398.01 4486.71 0.00 0.00 25.50 185.88 0.00 0 21 37 41 3504.07 0.00
30 85 65 486.20Extra Oil A
30101 17 696.15FULL SERVICE A
30101 12 863.40FULL SYNTHETIC A
30101 2 89.90FULL SERVICE A
30101 5 384.75T6 FULL SYNTHETIC A
30101 2 99.90SEMI SYNTHETIC A
30101 2 105.90HIGH MILEAGE A
30101 1 76.95MOBIL 1 SYNTHETIC A
30101 1 52.95MOTORCRAFT 5W20 A
30102 5 124.75AIR FILTER A
30110 5 434.91FUEL FILTER A
30121 2 14.00SAFETY INSPECTION A
30122 15 382.50OBD INSPECTIONS A
30125 1 44.95CABIN FILTER A
30157 1 25.00PARTS A
30158 3 405.00LABOR A
30084 42 115.50DISPOSAL FEE A
30086 15 84.00EXTRA CHARGE -OIL FILTER A
99 END OF DAY
100001022022 200.0011123419 009:07TERRELLL WHO
12 0.00123420 0.00 0.00 0.00 0.00 0.00 0.00 0.0011:13 0.00 0.00 0.00 0.00 0.00 0.00X1 0.00 0.00
13A 0.00 0.00 0.00 0.00 0 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0.00 0.00
99 END OF DAY
100001022122 300.0011123421 007:11TERRELLL WHO
11GB8204190F 1677RAEXX1 0.00 85.42 4.22 0.00 0.00 0.0012342408:50 63D MEC
14GB8204190MF3D MECHANICAL SERVICE PO BOX 501 75142 08CT ROT15W40 10.00 1GBHC24698E2041901441216 972-- N 203391 28776 7 0
11TEXA5 A7 529RAEXX1 0.00 20.00 0.00 0.00 0.00 0.0012343310:21 3BEST P
14TEXA5 MFTEXAS BEST PRE OWNED 15672 HWY 205 75160 13BK 0.00 0 972-- Y 408756 119719 0 0
12 4973.98123505 951.78 0.00 302.58 3588.11 131.51 0.00 0.0018:24 0.00 0.00 0.00 0.00 0.00 0.00X1 0.00 0.00
13A 0.00 2.00 388.21 0.00 2 67 0.00 4849.83 4858.83 105.42 0.00 0.00 222.57 0.00 2 24 45 41 3211.07 0.00
30 85 58 348.50Extra Oil A
30101 16 655.20FULL SERVICE A
30101 141007.30FULL SYNTHETIC A
30101 4 179.80FULL SERVICE A
30101 3 149.85SEMI SYNTHETIC A
30101 3 158.85HIGH MILEAGE A
30101 1 52.95MOTORCRAFT 5W20 A
30102 5 134.75AIR FILTER A
30103 21 535.50OBD INSPECTIONS A
30104 1 7.00SAFTEY INSPECTION A
30105 3 124.93CABIN FILTER A
30106 6 114.94WIPER BLADE A
30117 2 60.00CUSTOMER OIL A
30157 10 892.84PARTS A
30158 3 245.00LABOR A
30160 7 3.67DISCOUNT A
30084 41 112.75DISPOSAL FEE A
30086 11 75.00EXTRA CHARGE -OIL FILTER A
60ANDREW COX 0.00A08:00 09:58 ANDREW COX
60DAVID BEAUMONT 0.00A08:08 13:26 14:09 18:00 D. BEAUMONT
60JOSEPG CAMACHO 0.00A08:04 11:10 11:41 18:01 JO CAMACHO
60JUAN CABRERA 0.00A08:00 18:02 JUAN CABRERA
60ROBERT ELMORE 0.00A08:04 18:02 ROBERT ELMOR
60RUBEN CABRERA 0.00A15:00 18:02 RUBEN CABRER
60TRISTON HILL 0.00A08:02 11:52 12:27 18:02 TRISTON HILL
60JOSE VARGAS 0.00A08:19 16:22 16:54 18:01 VARGAS
99 END OF DAY


This is my code that gets me to the line but its running line by line how can I incorporate your code?
VBA Code: 
 todaysdate = ThisWorkbook.Worksheets("Sheet1").Range("C1").Value 'Date
        sdayname = Format(todaysdate, "dddd") 'Reformat Date

'Clear content and Set WorksheetName
  Set Sh = ThisWorkbook.Worksheets("Sheet1")

'FileName

  txtFileName = Sh.Range("M3").Value
'StoreID and Date Combined
  todaysdate2 = Sh.Range("L3").Value & Format(todaysdate, "mmddyy")

  'Array and open Textfile
   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fileopening = fso.OpenTextFile(txtFileName, 1)

   arrTxt = Split(fileopening.ReadAll, vbCrLf)

  For i = 0 To UBound(arrTxt)
  
      If InStr(arrTxt(i), todaysdate2) > 0 Then
         Do While InStr(arrTxt(i + j), "END OF DAY") = 0
            
            j = j + 1
 
Upvote 0
VBA Code: 
Sub TimeSheets()
Dim Sh As Worksheet, txtFileName As String, lastR As Long, i As Long, j As Long
Dim arrTxt as Variant
Dim fileopening As Object

todaysdate = ThisWorkbook.Worksheets("Sheet1").Range("C1").Value 'Date
sdayname = Format(todaysdate, "dddd") 'Reformat Date

'Checks if its a sunday
If sdayname = "Sunday" Then
MsgBox "The Date entered is a Sunday, the shop is closed! Please enter another Date and Click the button again!"
Exit Sub
End If

Set Sh = ThisWorkbook.Worksheets("Sheet1") 'Set WorksheetName
txtFileName = Sh.Range("M3").Value 'FileName
todaysdate2 = Sh.Range("L3").Value & Format(todaysdate, "mmddyy") 'StoreID and Date Combined

'Array and open Textfile
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fileopening = fso.OpenTextFile(txtFileName, 1)

arrTxt = Split(fileopening.ReadAll, vbCrLf)

Dim arrTimeSlot         As Long
    Dim ClockTimesProcessed As Long, TimeClocksFound    As Long
    Dim OutputArrayRow      As Long
    Dim OutputArray         As Variant
'
    ReDim OutputArray(1 To 4, 1 To 10000)
    arrTimeSlot = 0
    ClockTimesProcessed = 0                                                                                         ' Initiate ClockTimesProcessed
    OutputArrayRow = 0
'
    For i = 0 To UBound(arrTxt)
        If InStr(arrTxt(i), todaysdate2) > 0 Then
            Do While InStr(arrTxt(i + j), "END OF DAY") = 0
                j = j + 1
'
                For t = 2 To 17
                    If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & _
                            ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
'                       SI = InStr(arrTxt(i + j), "60ANDREW ")
                        OutputArrayRow = OutputArrayRow + 1                                                     ' Increment OutputArrayRow
                        arrtime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")                             ' Get Data Line into arrTime
'
                        TimeClocksFound = UBound(arrtime) - 3                                                   ' Calculate the # of TimeClocksFound
'
                        arrtime(0) = Right(arrtime(0), Len(arrtime(0)) - 2) & " " & arrtime(1)                  ' Get the First & Last Name from beginning
                        OutputArray(1, OutputArrayRow) = arrtime(0)                                             ' Save the Employee Name into OutputArray
'
                        arrtime(2) = Split(arrtime(2), "A")(1)                                                  ' Get the first Time
                        If arrtime(2) <= "08:10" Then arrtime(2) = "08:00"                                      ' Adjust the first Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(2, OutputArrayRow) = arrtime(2)                                             ' Save the first clock in time into OutputArray
'
                        If arrtime(3) >= "17:53" And arrtime(3) <= "18:25" Then arrtime(3) = "18:00"            ' Adjust the second Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(3, OutputArrayRow) = arrtime(3)                                             ' Save the first clock out time into OutputArray
'
                        OutputArray(4, OutputArrayRow) = todaysdate                                             ' Save todaysdate into OutputArray
'
'---------------------------------------------------------------------------------------------------------------
'
                        If ClockTimesProcessed = TimeClocksFound Then GoTo NextLineOfData                       ' If no more TimeClocks then goto next Data
'
'---------------------------------------------------------------------------------------------------------------
'
                        arrTimeSlot = 3                                                                         ' Initialize arrTimeSlot
'

                        Do While ClockTimesProcessed < TimeClocksFound
                            arrTimeSlot = arrTimeSlot + 1                                                       ' Increment arrTimeSlot
                            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
'
                            OutputArray(1, OutputArrayRow) = arrtime(0)                                         ' Save the Employee Name into OutputArray
'
                            If TimeClocksFound - ClockTimesProcessed = 2 Then                                   ' If last set of TimeClocks found then ...
                                If arrtime(arrTimeSlot) <= "08:10" Then arrtime(arrTimeSlot) = "08:00"          ' Adjust the clock in Time if needed
                                OutputArray(2, OutputArrayRow) = arrtime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                If arrtime(arrTimeSlot) >= "17:53" And arrtime(arrTimeSlot) <= "18:25" Then
                                    arrtime(arrTimeSlot) = "18:00"                                              ' Adjust the clock out Time if needed
                                End If
'
                                OutputArray(3, OutputArrayRow) = arrtime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            Else
                                OutputArray(2, OutputArrayRow) = arrtime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                OutputArray(3, OutputArrayRow) = arrtime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            End If
                        Loop                                                                                    ' Loop back for next ClockTime
                    End If
                Next
            Loop 'For Do
        End If 'If Instr
NextLineOfData:
    Next
'
    lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
    ReDim Preserve OutputArray(1 To 4, 1 To OutputArrayRow)
'
    ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(UBound(OutputArray, 2), UBound(OutputArray, 1)) = Application.Transpose(OutputArray)
   
End Sub

This is my code at the moment and its only returning one employee
 
Upvote 0
So i moved "NextLineOfData: to the higher Next it got all the employees but only one line per employee and one clock in and out
 
Upvote 0
Well now that you have provided more of your code, I can look into it more and hopefully create a better solution for you.
 
Upvote 0
johnnyL said:
Well now that you have provided more of your code, I can look into it more and hopefully create a better solution for you.
Click to expand...
Sorry about that I thought it would be simple to just incorporate it. But yea reading from a large text file
 
Upvote 0
Does that data you posted recently pertain to the post #1 data? I am skeptical because it doesn't appear to be. Unless you are referring to the end of the data.

Perhaps you might want to include a data file that represents what you are dealing with, unless you are saying that represents a full text file.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Populate Rows & Columns by Comma Split (DYNAMIC VERSION ver.2)
Replies
4
Views
201
airforceone
airforceone
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
586
Alex Blakenburg
A
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
K
  • Solved
Formula to match two columns and get a result from third one
Replies
5
Views
342
Fluff
Fluff
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
356
dilshod_k
D

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top