find a line and return the 4th line from it

[Ragavhere]

Hi,

I need to access a text file using excel macro and match the line "Variable name : Fcst_Wk" and return the 4th line below it and assign it to a variable. i.e i need the line "Variable value : Wk2" and assign it to a variable. The content of the file is as below.


Variable name : CurrScenario
Server name : chnveltss04
Application name : Plan_UAT
Database name :
Variable value : LE_Wk1

Variable name : Fcst_Mth
Server name : chnveltss04
Application name : Plan_UAT
Database name :
Variable value : Jul

Variable name : Fcst_Wk
Server name : chnveltss04
Application name : Plan_UAT
Database name :
Variable value : Wk2

Variable name : NextScenario
Server name : chnveltss04
Application name : Plan_UAT
Database name :
Variable value : LE_Wk2

Appreciate your help.

Regards,
Ragav.

 

[cgcamal]

Hi Ragavhere,

Try with:

[COLOR=Navy]Sub [/COLOR]Search_Line_In_TextFile()
Dim CurrL As String

k = 1
Open "c:\YourPath\inputfile.txt" For Input As #1 [COLOR=Green]'Open in Ram inpufile.txt[/COLOR]

Do While Not EOF(1)[COLOR=Green] 'Loop until end of file.[/COLOR]

Input #1, CurrL [COLOR=Green]'Read each line and store it into CurrL variable[/COLOR]
If InStr(1, CurrL, "Variable name : Fcst_Wk") Then
    
    For i = 1 To 4 'Loop to read 4 lines after line matched
       Input #1, Strg 
    Next
    
[COLOR=Green]'When loop finishes, we'll store the current line in Strg variable[/COLOR]
     Cells(k, 1) = Strg
     k = k + 1
  End If

Loop
Close #1 [COLOR=Green]' Close file[/COLOR]
[COLOR=Navy]End Sub[/COLOR]

It will find all lines containing "Variable name : Fcst_Wk" and will show in first column of
current worksheet the content that is 4 lines below.

Hope this helps,

Regards

 

[Ragavhere]

thats works perfectly. Thanks a lot.