Macro pulls incorrect data from txt files. I have a code that loops through several hundred files pulling timestamps for start time and Diagnose time and pastes them into column A and B. The time stamp for start time pulls correctly but the time stamp for Diagnose does not. Instead the first line of text in the txt file is pulled and pasted into column B. A sample of the input txt Log file looks like this, there are hundreds of other time stamps in the txt log file but the two time stamps I care about are start and [irp] Diagnose
+version=LogbookPlus 1.7.23
+site=
+lastedit=2019-08-31 17:19:31.289
+description=SRC - LSA-0251 error
+number=1282
+so=51657136
+toolowner=
+init=2019-08-30 08:40:38.360
+start=2019-08-30 08:25
+end=2019-08-30 09:45
+down=Unscheduled
+account=Source
+rooterror=LSA-0251
+subsystem=ILP-DC-PQ
+assy=Dose & power performance
+work=2019-08-30|08:39| [IRP] Diagnose
+work=2019-08-30|08:41| Start streaming
+work=2019-08-30|09:03| Conditioning
+work=2019-08-30|09:04| Standby
Here is my code
Sub FindTimeStamps()
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim MyFolder As String, MyFile As String
'Open Diaglouge box prompting user to choose folder path
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
MyFolder = .SelectedItems(1)
Err.Clear
End With
'Create a new object for files in that folder and apply for/loop
Dim objFSO As Object
Dim objFolder As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getFolder(MyFolder)
Dim fls As Object
Dim i As Integer
i = 1
For Each fls In objFolder.Files
'File Path of Text File
MyFile = MyFolder & "\" & fls.Name
'Determine the next file number available for use by the Fileopen function
TextFile = FreeFile
'open the text file
Debug.Print CurDir
Open MyFile For Input As #1
'Store file content inside a variable
Do Until EOF(1)
Line Input #1, textline
Text = Text & textline
Loop
Close #1
'Find Time Stamp Data from txt file
Dtime = Diagnose
Diagnose = InStr(1, Text, Dtime)
dt = Mid(Text, Diagnose + 1, 17)
Sttime = InStr(Text, "+start=")
'Paste obtained Time Stamp into excel Cells
Range("A" & i + 1).Value = Mid(Text, Sttime + 7, 16)
Range("B" & i + 1).Value = dt
i = i + 1
Text = ""
Next
End Sub
if I do not hard code the Diagnose variable and instead do a user input such as
Find = InputBox("which word")
Open Text For Input As #1
Do While Not EOF(1)
Input #1, Text
If InStr(1, Text, Find) > 0 Then
idx = InStr(1, Text, "=")
dt = Mid(Text, idx + 1, 17)
Exit Do
End If
Loop
This was suggested by another user and the code works and pulls the correct time stamp. The Drawback to this is that I have to keep typing in Diagnose for every single file in the folder which is not ideal. Im still learning VBA so I'm not just looking for a solution but also a reason why the hard coded variable does not pull data correctly. Thanks any help is much appreciated
Here is the output of the macro I get in excel when I use the Hard coded variable for Diagnose, there are no time stamps in column b for diagnose it just pulls the first line of the input txt file for reason I do not understand
Column A Column B
8/28/2019 14:29 version=LogbookPl
8/29/2019 5:38 version=LogbookPl
8/30/2019 8:25 version=LogbookPl
