I am working in Excel, to look into a txt file for certain information and then paste it into the worksheet.
I want to copy information from different lines that contain different defined strings.
Example:
txt file is a like a logbook. This logbook has entrances like |UPDATED|, |CREATED| and |UPDATED|. After this is is logged, then a variable number of lines will follow, until the "topic" is closed with the symbol "->".
I want the script to, look for the string |UPDATED|, copy the whole line and paste into Excel. Then keep scrolling through the txt file lines, until "->" is found, copy that line and put it right under the correspondent "title" (UPDATED, CREATED, UPDATED).
If there was a way to also record the line it found the entrance into a variable, then I could do, for example const= const - 2, to go up in txt file for two lines and also copy that content!
Sub PS_automation()
Dim ReadData As String
Dim x As Integer, y As Integer, z As Integer, p As Integer
x = 1
y = 1
z = 1
p = 1
Open "C:\Users\racastr1\Desktop\PS_AUTOMATE.txt" For Input As #1
Do Until EOF(1)
Line Input #1, ReadData
If InStr(Left(ReadData, 9), "|CREATED|") Then
Cells(x, 1) = ReadData
x = x + 1
Else
If InStr(Left(ReadData, 8), "|CLOSED|") Then
Cells(y, 2) = ReadData
y = y + 1
Else
If InStr(Left(ReadData, 9), "|UPDATED|") Then
Cells(z, 3) = ReadData
z = z + 1
End If
End If
End If
Loop
Close #1
End Sub
This already copies the mentioned entrances, but if I had the same condition to find "->" it does not paste it into the worksheet in order (so it does not get into the associated "title").
You could try something like this (changed my approach from the initial answer)
Sub PS_automation()
Const TXT_PATH As String = "C:\Users\racastr1\Desktop\PS_AUTOMATE.txt"
Dim data As String, arr, arrCols, i As Long, s As String, col As Long
Dim ws As Worksheet, n As Long, v, sPrev As String
data = GetContent(TXT_PATH) 'read the whole file
data = Replace(data, vbcrlf, vbLf) 'normalize line separators
arr = Split(data, vbLf) 'split to array
'array of label+colNumber pairs
arrCols = Array("|CREATED|", 1, "|CLOSED|", 2, "|UPDATED|", 3)
Set ws = ActiveSheet 'or some specific sheet
For i = 0 To UBound(arr) 'loop over the lines from the file
s = arr(i)
'Loop over `arrCols` and check for pre-defined label....
For n = LBound(arrCols) To UBound(arrCols) Step 2
v = arrCols(n) 'label
If Left(s, Len(v)) = v Then 'match?
col = arrCols(n + 1) 'corresponding column number
If i > 0 Then 'go back up and check for date?
sPrev = arr(i - 1) 'the line above the match
If sPrev Like "####-##-## *" Then 'starts with date?
'write line with date to sheet if found
ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = sPrev
End If
End If
ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = s
GoTo skip 'done with this line
End If
Next n
If Left(s, 2) = "->" Then
If col > 0 Then
ws.Cells(Rows.Count, col).End(xlUp).Offset(1).Value = s
End If
End If
skip:
Next i
End Sub
Function GetContent(f As String) As String
GetContent = CreateObject("scripting.filesystemobject"). _
OpenTextFile(f, 1).ReadAll()
End Function