Hi guys! I'm having trouble looping a certain macro that I just coded. My main goal is to search some information in a text file and then having the output result in excel columns. The bit of code I've came up with so far works great but I can't get it to loop in order to search the whole text file. The informations in the text file are organized in section so I have to search each section for the desired results but now my code can only search 1 section and then stops.
To help your understand my problem, here's more details:
My code so far :
Example of my Txt File :
The output I get with my code :
I only get one section of information so far and I really need my code to be able to search the entire sections of the entire text file.
I just started working with VBA and I'm cannot find a way to loop this bit of code in order to get the desired results.
Can you guys help me sorting that out? It would be more than appreciated!
Have a good one!
To help your understand my problem, here's more details:
My code so far :
Code:
[COLOR=#000000][FONT=Calibri]Sub test()[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim myFile As String, text As String, textline As String, DDC As Integer, DDR As Integer, DDP As Integer, ADC As Integer, i As Integer, sArray(4) As String, SE As Integer, SP As Integer, SG As Integer, j As Integer, v As Integer[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]myFile = "C:\Users\Seb\Desktop\C0010DET.txt"[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Open myFile For Input As #1[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Do Until EOF(1)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Line Input #1, textline[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] text = text & textline[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Loop[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Close #1
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] i = 1[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Dim vItm As Variant[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Dim aStrings(1 To 2) As String[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] For Each vItm In aStrings[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] DDC = InStr(text, "Date")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] DDR = InStr(text, "Name")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] ADC = InStr(text, "Age")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] SE = InStr(text, "Email")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] SP = InStr(text, "Function")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] SG = InStr(text, "Time")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 1, 1).Value = Mid(text, DDC, 14)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 1, 2).Value = Mid(text, DDC + 36, 10)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 2, 1).Value = Mid(text, DDR, 16)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 2, 2).Value = Mid(text, DDR + 36, 10)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 3, 1).Value = Mid(text, ADC, 23)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 3, 2).Value = Mid(text, ADC + 36, 6)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 4, 1).Value = Mid(text, SE, 16)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 4, 2).Value = Mid(text, SE + 36, 6)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 5, 1).Value = Mid(text, SP, 24)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(i + 5, 2).Value = Mid(text, SP + 36, 6)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] For v = 0 To 10[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] j = v * 228[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(v + 7, 1).Value = Mid(text, SG + j, 24) + Mid(text, SG + 64 + j, 10) + "/ " + Mid(text, SG + 77 + j, 10)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Cells(v + 7, 2).Value = Mid(text, SG + 103 + j, 10)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Next v[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] Next vItm[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri] [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR]
Example of my Txt File :
Code:
INFORMATION 1
[COLOR=#000000][FONT=Calibri]DATE : 23/12/2014
[/FONT][/COLOR]NAME : Jerry Garcia
AGE : 43
EMAIL : jgarcia@hotmail.com
FUNCTION : Boss
TIME : 3H12PM
INFORMATION 2
[COLOR=#000000][FONT=Calibri]DATE : 09/02/2013
[/FONT][/COLOR]NAME : John Doe
AGE : 22
EMAIL : j_shot@gmail.com
FUNCTION : Director
TIME : 9H54PM
etc...
The output I get with my code :
Code:
INFORMATION 1
[COLOR=#000000][FONT=Calibri]DATE : 23/12/2014
[/FONT][/COLOR]NAME : Jerry Garcia
AGE : 43
EMAIL : jgarcia@hotmail.com
FUNCTION : Boss
TIME : 3H12PM
I only get one section of information so far and I really need my code to be able to search the entire sections of the entire text file.
I just started working with VBA and I'm cannot find a way to loop this bit of code in order to get the desired results.
Can you guys help me sorting that out? It would be more than appreciated!
Have a good one!