Option Explicit
Sub TestFSGetDates()
Dim DateStr As String, ReturnType As String
'Dim ExeDtTime As Variant, CompDtTime As Variant
'
'If Len(ExeDtTime & "") = 0 Then
' Debug.Print "ExeDtTime has no value"
'End If
'
'If IsNull(ExeDtTime) Then
' Debug.Print "ExeDtTime is null"
'End If
'
'If IsEmpty(ExeDtTime) Then
' Debug.Print "ExeDtTime is empty or not initialized"
'End If
Debug.Print
DateStr = ThisWorkbook.Worksheets("Sheet1").Range("A2") '"01-Sep-2015"
ReturnType = ThisWorkbook.Worksheets("Sheet1").Range("B1")
FSGetDates DateStr, ReturnType
Debug.Print
DateStr = "01-Sep-2015"
ReturnType = "end"
FSGetDates DateStr, ReturnType
'Debug.Print Len("======================================== ") '41
'Debug.Print Len("Execution Date Time:") '20
'Debug.Print Len("02-Sep-2015 06:01:10 AM") '23
'Debug.Print Len("------------------------- Cycle Date ") '37
'Debug.Print Len("01-Sep-2015") '11
End Sub
Function FSGetDates(DateStr As String, ReturnType As String) As Variant
On Error GoTo Err_Handler
If IsDate(DateStr) Then
DateStr = Format(DateStr, "dd-mmm-yyyy")
Else
FSGetDates = "DateStr '" & DateStr & "' is invalid date"
Exit Function
End If
Debug.Print "DateStr:" & DateStr
Dim myfile As String, LineNum As Integer, ExeLineNum As Integer, CycleDateLineNum As Integer
Dim blExeDtTimeSet As Boolean
Dim FileName As String
Dim TempDate As String
Dim FolderName As String, i As Integer, EntireLine As String
Dim ExeDtTime As Variant, CompDtTime As Variant
Dim ExeDtStartNum As Integer, CompDtStartNum As Integer
Dim blCycleDateMatchUserDate As Boolean
FolderName = "C:\LOGS\LOG 1"
i = 0
myfile = Dir(FolderName & "\" & "*.txt")
Do While myfile <> ""
Open FolderName & "\" & myfile For Input Access Read As #1
While Not EOF(1)
Line Input #1, EntireLine
LineNum = LineNum + 1
' Debug.Print LineNum & " - " & EntireLine
If InStr(1, EntireLine, "Execution Date Time:") > 0 Then
' Debug.Print i & " - " & EntireLine
' Debug.Print InStr(1, EntireLine, "Execution Date Time:")
' Debug.Print Mid(EntireLine, 62, 23)
ExeDtStartNum = InStr(1, EntireLine, "Execution Date Time:") + Len("Execution Date Time:")
If Len(ExeDtTime & "") = 0 And Not blExeDtTimeSet Then
ExeLineNum = LineNum
ExeDtTime = Mid(EntireLine, ExeDtStartNum, 23)
End If
End If
If InStr(1, EntireLine, "Cycle Date") > 0 Then
If InStr(1, EntireLine, DateStr, vbTextCompare) Then
CycleDateLineNum = LineNum
blCycleDateMatchUserDate = True
blExeDtTimeSet = True
End If
End If
If LCase(ReturnType) = LCase("start") Then
If blCycleDateMatchUserDate And blExeDtTimeSet Then
'If start exit do loop
Close #1
Exit Do
End If
End If
If InStr(1, EntireLine, "Completed at") > 0 Then
If blCycleDateMatchUserDate = True Then
CompDtStartNum = InStr(1, EntireLine, "Completed at:") + Len("Completed at:") + 1
CompDtTime = Mid(EntireLine, CompDtStartNum, 23)
End If
End If
Wend
Close #1
myfile = Dir
Loop
If blCycleDateMatchUserDate = True Then
If LCase(ReturnType) = LCase("start") Then
If Len(Trim(ExeDtTime & "")) > 0 And IsDate(Trim(ExeDtTime & "")) Then
FSGetDates = ExeDtTime
Else
FSGetDates = "Cycle Date match User Date but Execution Date Time is missing / not valid"
End If
ElseIf LCase(ReturnType) = LCase("end") Then
If Len(Trim(CompDtTime & "")) > 0 And IsDate(Trim(CompDtTime & "")) Then
FSGetDates = CompDtTime
Else
FSGetDates = "Cycle Date match User Date but 'Completed at' Date Time is missing / not valid"
End If
End If
Else
FSGetDates = "Cycle Date does not match User Date in any of the text files in " & FolderName
End If
Exit_Here:
On Error Resume Next
Close #1
Debug.Print ReturnType & " - " & FSGetDates
Exit Function
Err_Handler:
FSGetDates = "Error - " & Err.Number & " " & Err.Description
Resume Exit_Here
End Function
