Hi Folks,
I have an issue to complete my VBA project for reading certain data from a txt file and fill up an excel table for further data validation.
I have a large data log divided to 15-lined-blocks, all blocks has a similar formatting and contains data from my logged system. I need to pick up a log-time and several other values from the following rows. The block has many useless lines and parameters for my project so they need to be skipped.
The issue is when I read the txt file I only can pick up data from the first block or the first occurrence not looping until EOF. Or
How can I have all required data from each scan-time through the logfile?
Thanks
I have an issue to complete my VBA project for reading certain data from a txt file and fill up an excel table for further data validation.
I have a large data log divided to 15-lined-blocks, all blocks has a similar formatting and contains data from my logged system. I need to pick up a log-time and several other values from the following rows. The block has many useless lines and parameters for my project so they need to be skipped.
The issue is when I read the txt file I only can pick up data from the first block or the first occurrence not looping until EOF. Or
How can I have all required data from each scan-time through the logfile?
Thanks
Code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim myFile, text, textline As String
Dim dat1, dat2, dat3, scantime As Integer
myFile = Application.GetOpenFilename()
Open myFile For Input As #1
i = 6
FileNum = FreeFile()
Open "Filename" For Input As #FileNum
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
scantime = InStr(text, "Scan Statistics")
dat1 = InStr(text, "DH")
dat2 = InStr(text, "DH")
dat3 = InStr(text, "NGIFF") '
ActiveSheet.Cells(i, 1).Value = Mid(text, scantime + 30, 8)
ActiveSheet.Cells(i, 2).Value = Mid(text, dat1 + 25, 3)
ActiveSheet.Cells(i, 3).Value = Mid(text, dat2 + 38, 3)
ActiveSheet.Cells(i, 4).Value = Mid(text, dat3 + 31, 3)
i = i + 1
Close #1
ActiveSheet.Cells(i, 1).Select