Sub blah()
Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
FileNumber = FreeFile
DestRow = 2
Open "data.txt" For Input As #FileNumber ' Open file.
Do 'loop in case there are text headers
Line Input #FileNumber, TextLine ' Read line into variable.
x = Split(Application.Trim(TextLine), vbTab)
If Not IsDate(x(0)) Then
Cells(DestRow, 1).Resize(, 2).Value = x
DestRow = DestRow + 1
End If
Loop Until IsDate(x(0))
mytime = TimeValue(x(0))
Cells(DestRow, 1).Value = mytime
'Cells(DestRow, 2).Value = x(1)
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:00")
mytime = mytime - Int(mytime)
Do While Not EOF(FileNumber) ' Loop until end of file.
Line Input #1, TextLine
x = Split(Application.Trim(TextLine), vbTab)
NextTime = TimeValue(x(0))
Do Until Round(NextTime, 5) = Round(mytime, 5)
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = 0
Cells(DestRow, 1).Resize(, 2).Font.Bold = True 'include to make inserted values bold
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
Loop
Cells(DestRow, 1).Value = mytime
'Cells(DestRow, 2).Value = x(1)
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
mytime = mytime + TimeValue("00:01:00")
mytime = mytime - Int(mytime)
DestRow = DestRow + 1
Loop
Close #FileNumber ' Close file.
End Sub
Sub blah2()
Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
FileNumber = FreeFile
DestRow = 2
'Open "data.txt" For Input As #FileNumber ' Open file.
Open "C:\Users\Pascal\Documents\excelguru3265\datatab.txt" For Input As #FileNumber ' Open file.
Do 'loop in case there are text headers
Line Input #FileNumber, TextLine ' Read line into variable.
x = Split(Application.Trim(TextLine), vbTab)
If Not IsDate(x(0)) Then
Cells(DestRow, 1).Resize(, 2).Value = x
DestRow = DestRow + 1
End If
Loop Until IsDate(x(0))
'fill in from 9pm until first time in file:
mytime = TimeValue("21:00:00")
Do Until Round(TimeValue(x(0)), 5) = Round(mytime, 5)
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = 0
Cells(DestRow, 1).Resize(, 2).Font.Bold = True 'include to make inserted values bold
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
Loop
'print the first file time into the sheet:
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
'process the rest of the file times:
Do While Not EOF(FileNumber) ' Loop until end of file…
Line Input #1, TextLine
x = Split(Application.Trim(TextLine), vbTab)
NextTime = TimeValue(x(0))
Do Until Round(NextTime, 5) = Round(mytime, 5) '…filling in missing times:
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = 0
Cells(DestRow, 1).Resize(, 2).Font.Bold = True 'include to make inserted values bold
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
Loop
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
mytime = mytime + TimeValue("00:01:00")
mytime = mytime - Int(mytime)
DestRow = DestRow + 1
Loop
Close #FileNumber ' Close file.
'fill in after last time to 5am
Do Until Round(TimeValue("05:01:00"), 5) = Round(mytime, 5)
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = 0
Cells(DestRow, 1).Resize(, 2).Font.Bold = True 'include to make inserted values bold
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
Loop
End Sub