Find specific data from a text file in VBA

[alokranjan]

I want to fetch the data from a text file which starts with arrUserActivity in VBA. I'm able to fetch if there is no break line i.e Sample 1 but not for Sample 2.

Sample 1 : var arrUserActivity = [['9/13/2022 8:49:00 AM','MOA_S10EST','Firoj Aga','Remote Desktop using VNC 2.0 ','CBM'],['9/13/2022 1:54:00 PM','MOA_S10EST','Aux Tech2','Remote Desktop using VNC 2.0','Routine']];

Sample 2 : var arrUserActivity = [

['9/13/2022 8:49:00 AM','MOA_S10EST','Firoj Aga','Remote Desktop using VNC 2.0 ','CBM'], ['9/13/2022 1:54:00 PM','MOA_S10EST','Aux Tech2','Remote Desktop using VNC 2.0 ','Routine']];

My code is :

WhatToFind = "var arrUserActivity"
FromFile1 = Path + "\02-Text_Files\" + Site_Name + ".txt"
FileNum = FreeFile()
Open FromFile1 For Input As #FileNum
Do While Not EOF(FileNum)
lngCount = lngCount + 1 'increment a counter'
Line Input #FileNum, DataLine ' read in data 1 line at a time'
If InStr(1, DataLine, WhatToFind) > 0 Then 'the string is found'
bFound = True 'set a boolean value to true'
Exit Do 'and stop the loop'
End If
Loop

I'm able to fetch data if there is no line break.

 

[Domenic]

Your code seems to work fine. It reads the first line of the text file, the If statement evaluates to True, bFound is set to True, and then it exits the loop. Try stepping through your code by first placing your cursor within your code, and then pressing the F8 key. Does it step through your code as desired?

 

[alokranjan]

Your code seems to work fine. It reads the first line of the text file, the If statement evaluates to True, bFound is set to True, and then it exits the loop. Try stepping through your code by first placing your cursor within your code, and then pressing the F8 key. Does it step through your code as desired?

It works fine for sample1(i.e. if the data is in continuation ) else it only captures var arrUserActivity = [ and skips the data which is after the break line.(Screenshot attached).

 

[Domenic]

Oh I see, you'd like to retrieve the rest of the text associated with "var arrUserActivity".

In that case, try the following code instead. Note that I have assumed you've declared your variables. As such, add the following declaration to your code...

Dim code as String

Also, I would suggest that you use the ampersand ( & ) operator instead of the plus ( + ) operator to concatenate strings. Here's the code...

    WhatToFind = "var arrUserActivity"
    
    FromFile1 = Path & "\02-Text_Files\" & Site_Name & ".txt"
    
    FileNum = FreeFile()
    Open FromFile1 For Input As #FileNum
    
        Do While Not EOF(FileNum)
            lngCount = lngCount + 1 'increment a counter'
            Line Input #FileNum, DataLine ' read in data 1 line at a time'
            If InStr(1, DataLine, WhatToFind) > 0 Then 'the string is found'
                code = DataLine
                Do While Not EOF(FileNum)
                    lngCount = lngCount + 1
                    Line Input #FileNum, DataLine
                    code = code & DataLine
                    If Right(DataLine, 2) = "];" Then Exit Do
                Loop
                bFound = True 'set a boolean value to true'
            End If
            If bFound Then Exit Do
        Loop
    
    Close #FileNum

Hope this helps!

 

[alokranjan]

Thanks for your help. However, now it fetches only the first line after the line break and skips the rest(attached screenshot).

Kindly advice.

Data to be fetched :
var arrUserActivity = [

['9/13/2022 8:49:00 AM','MOA_STA10EST','Firoj Aga','Remote Desktop using VNC 2.0 ','CBM'],
['9/13/2022 1:54:00 PM','MOA_STA10EST','Aux Tech2','Remote Desktop using VNC 2.0 ','Routine'],
['9/15/2022 9:31:00 AM','MOA_STA10EST','Aux Tech2','Remote Desktop using VNC 2.0 ','routine'],
['9/21/2022 11:26:00 AM','MOA_STA09EST','Aux Tech2','Remote Desktop using VNC 2.0 ','Routine site access'],
['9/21/2022 11:28:00 AM','MOA_STA10EST','Aux Tech2','Remote Desktop using VNC 2.0 ','Routine site access'],
['9/22/2022 12:32:00 PM','MOA_STA10EST','Aux Tech1','Remote Desktop using VNC 2.0 ','routine'],
['9/22/2022 12:37:00 PM','MOA_STA10EST','Aux Tech1','Remote Desktop using VNC 2.0 ','Routine log in'],
['9/22/2022 12:47:00 PM','MOA_STA01EST','Aux OpsIT','Remote Desktop using RDP ','Test RDP to servers'];

Data getting fetched : ['9/13/2022 8:49:00 AM','MOA_STA10EST','Firoj Aga','Remote Desktop using VNC 2.0 ','CBM'];

 

[Domenic]

From your image, it doesn't look that you've assigned your variable WhatToFind the string "var arrUserActivity". It looks that you've actually assigned it the string "var arrActivities_60850", and so it retrieves that particular line.

Is this in fact the case?

 

[alokranjan]

From your image, it doesn't look that you've assigned your variable WhatToFind the string "var arrUserActivity". It looks that you've actually assigned it the string "var arrActivities_60850", and so it retrieves that particular line.

Is this in fact the case?

Sorry its typo mistake, kindly ignore. However, now I'm able to fetch the data through your code, minor modification was required.

 

[Domenic]

It looks like you've sorted it out, cheers!