• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

VBA Word/String search in file

When I tried the code i got in related question, with different months it is not just finding the word searched but other words aswell and storing them. how do i just search for the words im looking for.

It finds
30168      \\FarrSQL\S0801160.012\S0801161.012
as supposed to but it also takes the lines below

7 - Inside ReceiveFax thread      00:00:16.67 - Inside ReceiveFax thread

I appreciate your help on this.
Private Sub getFiles()
 
    On Error Resume Next
    Dim aFiles() As String
    aFiles = filesInFolder(2008, 5)
    Dim j As Long, i As Long, n As Long
    Dim elementos As Long
    elementos = UBound(aFiles)
    
    If (Err.Number = 9) Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    Dim FileCont As String
    Dim eventNumber() As String, uncPath() As String
    Dim eventNumber1() As String, uncPath1() As String
    Dim searchI As String, searchF As String
    
    n = 1
 
    For j = LBound(aFiles) To UBound(aFiles)
        'MsgBox aFiles(j)
        FileCont = FileContent(aFiles(j))
        
        searchI = "for receive fax event: "
        searchF = vbCrLf
        eventNumber = CadenasEntreCaracteres(FileCont, searchI, searchF)
        
        searchI = "is stored as "
        searchF = vbCrLf
        uncPath = CadenasEntreCaracteres(FileCont, searchI, searchF)
        
        For i = LBound(eventNumber) To UBound(eventNumber)
            'MsgBox "file: " & aFiles(j) & vbCrLf & _
                "event number: " & eventNumber(i) & vbCrLf & _
                "unc path: " & uncPath(i)
            Cells(n, 1) = eventNumber(i)
            Cells(n, 2) = uncPath(i)
            n = n + 1
        Next
    Next
 
End Sub
 
Private Function FileContent(filePath As String) As String
    Dim fileNro As Integer
    fileNro = FreeFile
    Open filePath For Input As #fileNro
    FileContent = Input(LOF(fileNro), fileNro)
    Close #fileNro
End Function
 
Public Function CadenasEntreCaracteres(strSearchText As String, _
strSearchFor As String, strSeperator As String) As String()
    
    Dim strSearchTextFound() As String
    Dim foundText As Long
    Dim pos1 As Long, pos2 As Long
    
    foundText = 0
    pos1 = 0
    pos1 = InStr(pos1 + 1, strSearchText, strSearchFor) + Len(strSearchFor)
    Do While pos1 > 0
        ReDim Preserve strSearchTextFound(foundText)
        strSearchTextFound(foundText) = Mid(strSearchText, pos1, InStr(pos1, strSearchText, strSeperator) - pos1)
        
        pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)
        If pos1 > 0 Then pos1 = pos1 + Len(strSearchFor)
        foundText = foundText + 1
    Loop
    CadenasEntreCaracteres = strSearchTextFound
    
End Function
 
Private Function filesInFolder(year As Integer, month As Integer) As String()
    Dim strFolder As String
    Dim strPatern As String
    Dim sFile As String
    Dim strSubFolder As String
    Dim i As Long, j As Long, n As Long
    Dim aFiles() As String
    
    strPatern = year & Format(month, "00") & "*"
    strFolder = "F:\facsys Reporting\files\"
    strSubFolder = Dir(strFolder & strPatern, vbDirectory)
    
    Do While strSubFolder <> ""
        n = n + 1
        sFile = Dir(strFolder & strSubFolder & "\Receive.log", vbArchive)
        
        Do While sFile <> ""
            ReDim Preserve aFiles(i)
            aFiles(i) = strFolder & strSubFolder & "\" & sFile
            i = UBound(aFiles) + 1
            
            sFile = Dir   ' Obtiene siguiente entrada.
        Loop
        
        strSubFolder = Dir(strFolder & strPatern, vbDirectory)
 
        For j = 1 To n
            strSubFolder = Dir
            
        Next
    Loop
    
    filesInFolder = aFiles
 
End Function

Open in new window

receive.log
0
j8547
Asked:
j8547
  • 8
  • 5
  • 4
2 Solutions
 
FER_GCommented:
hello j8547; i have proved the code with the "receive.log" file, and i don't get the behavior that you describe above.
I just get :
eventNumber(0): 30169
uncPath(0): \\FarrSQL\S0801160.011\d0804163.011
eventNumber(1): 30168
uncPath(1): \\FarrSQL\S0801160.012\S0801161.012

and nothing else...so...

Regards.
FerG
--
Saludos.
--
Ing. Fernando D. Giletta
San Francisco. Córdoba. Argentina
0
 
aikimarkCommented:
Is it possible that your receive.log file might have some lines that are delimited with just a vbCr or vbLf?  If so, your algorithm will fail to pick up the correct line data.

Have you checked to see whether the UNCPath array contains the same number of items as the EventNumber array when you experience this error?

What are the EventNumber1 and UNCPath1 arrays used for?
0
 
aikimarkCommented:
Does the Cells reference imply that this is an Excel VBA application?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
j8547Author Commented:
Yea this is a VBA application. I think that i am getting the different words
eg: 7 - Inside ReceiveFax thread      00:00:16.67 - Inside ReceiveFax thread
when there is nothing in the logfile, as in no faxes received that day. If you test the logfile below you should get the line once.

when i do a full month i get the id and path and on every other row a line like above.

Thanks for the help
Receive.log
0
 
FER_GCommented:
ok; so, you must add the file path to the routine so you can then view the file and analize what could happend...

...
Cells(n, 1) = eventNumber(i)
Cells(n, 2) = uncPath(i)
Cells(n, 3) = aFiles(j)
n = n + 1
...

and take  a look of which file is the one who gave you that behavior
Regards.
FerG
--
Saludos.
--
Ing. Fernando D. Giletta
San Francisco. Córdoba. Argentina
0
 
j8547Author Commented:
I have added the above line and confirmed it only happens once for every log file that has no event id in it. I have attached the log file. I have no idea why its choosing these words. If i change the search criteria it returns something else. Its acting as if it is finding the proper text. Cannot figure it out.
Receive.log
0
 
aikimarkCommented:
your code never checks for the existence of the strSearchFor string in the CadenasEntreCaracteres routine.

This should correct the errant behavior.
Public Function CadenasEntreCaracteres(strSearchText As String, _
strSearchFor As String, strSeperator As String) As String()
    
    Dim strSearchTextFound() As String
    Dim foundText As Long
    Dim pos1 As Long, pos2 As Long
    
    foundText = 0
    pos1 = 0
    pos1 = InStr(pos1 + 1, strSearchText, strSearchFor) + Len(strSearchFor)
    Do While pos1 > 0
 
        pos1 = pos1 + Len(strSearchFor)
        ReDim Preserve strSearchTextFound(foundText)
 
        strSearchTextFound(foundText) = Mid(strSearchText, pos1, InStr(pos1, strSearchText, strSeperator) - pos1)
        
        pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)
        foundText = foundText + 1
    Loop
 
    CadenasEntreCaracteres = strSearchTextFound
    
End Function

Open in new window

0
 
j8547Author Commented:
I added the above code and it just changed the words that come out. It also added a line for every logfile that had an id with the whole line fromt he logfile.

Still getting more than what it is searching for. Thanks for your help
0
 
aikimarkCommented:
oops.  I thought I'd changed this line.  My appologies.
Public Function CadenasEntreCaracteres(strSearchText As String, _
strSearchFor As String, strSeperator As String) As String()
    
    Dim strSearchTextFound() As String
    Dim foundText As Long
    Dim pos1 As Long, pos2 As Long
    
    foundText = 0
    pos1 = 0
 
    pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)  'CHANGED THIS LINE
 
    Do While pos1 > 0 
        pos1 = pos1 + Len(strSearchFor)
        ReDim Preserve strSearchTextFound(foundText)
 
        strSearchTextFound(foundText) = Mid(strSearchText, pos1, InStr(pos1, strSearchText, strSeperator) - pos1)
        
        pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)
        foundText = foundText + 1
    Loop
 
    CadenasEntreCaracteres = strSearchTextFound
    
End Function

Open in new window

0
 
j8547Author Commented:
ok it is now just getting the event id and path but leaving blank rows in excel where the other information was (when log file had no events in it)
0
 
j8547Author Commented:
I just want it to store in the array the event id and path. so if it doesn't find it it moves onto the next folder and searches in that logfile.
0
 
FER_GCommented:
ok ... i have just change a little the code...so prove the next code snippet (it is complete)

Regards.
FerG
--
Saludos.
--
Ing. Fernando D. Giletta
San Francisco. Córdoba. Argentina
Public Function matrizInicializada(matriz As Variant) As Boolean
'Devuelve TRUE si la matriz pasada como parametro esta inicializada.
 
    On Error Resume Next
    
    Dim elementos As Long
    elementos = UBound(matriz)
    
    matrizInicializada = True
    
    If (Err.Number = 9) Then
        'MsgBox "SIN INICIAR"
        matrizInicializada = False
        Err.Clear
    End If
    
End Function
 
Private Sub getFiles()
 
    On Error Resume Next
    Dim aFiles() As String
    aFiles = filesInFolder(2008, 5)
    Dim j As Long, i As Long, n As Long
    Dim elementos As Long
    elementos = UBound(aFiles)
    
    If (Err.Number = 9) Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    Dim FileCont As String
    Dim eventNumber() As String, uncPath() As String
    Dim eventNumber1() As String, uncPath1() As String
    Dim searchI As String, searchF As String
    
    n = 1
 
    For j = LBound(aFiles) To UBound(aFiles)
        'MsgBox aFiles(j)
        FileCont = FileContent(aFiles(j))
        
        searchI = "for receive fax event: "
        searchF = vbCrLf
        eventNumber = CadenasEntreCaracteres(FileCont, searchI, searchF)
        
        searchI = "is stored as "
        searchF = vbCrLf
        uncPath = CadenasEntreCaracteres(FileCont, searchI, searchF)
        
        If (matrizInicializada(eventNumber)) Then
            For i = LBound(eventNumber) To UBound(eventNumber)
                'MsgBox "file: " & aFiles(j) & vbCrLf & _
                    "event number: " & eventNumber(i) & vbCrLf & _
                    "unc path: " & uncPath(i)
                Cells(n, 1) = eventNumber(i)
                Cells(n, 2) = uncPath(i)
                n = n + 1
            Next
        Else
            msbgox "file " & vbCrLf & aFiles(j) & vbCrLf & " doesn't has the searched strings"
        End If
    Next
 
End Sub
 
Private Function FileContent(filePath As String) As String
    Dim fileNro As Integer
    fileNro = FreeFile
    Open filePath For Input As #fileNro
    FileContent = Input(LOF(fileNro), fileNro)
    Close #fileNro
End Function
 
Public Function CadenasEntreCaracteres(strSearchText As String, _
strSearchFor As String, strSeperator As String) As String()
    
    Dim strSearchTextFound() As String
    Dim foundText As Long
    Dim pos1 As Long, pos2 As Long
    
    foundText = 0
    pos1 = 0
    pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)
    Do While pos1 > 0
        If pos1 > 0 Then pos1 = pos1 + Len(strSearchFor)
        ReDim Preserve strSearchTextFound(foundText)
        strSearchTextFound(foundText) = Mid(strSearchText, pos1, InStr(pos1, strSearchText, strSeperator) - pos1)
        
        pos1 = InStr(pos1 + 1, strSearchText, strSearchFor)
        'If pos1 > 0 Then pos1 = pos1 + Len(strSearchFor)
        foundText = foundText + 1
    Loop
    CadenasEntreCaracteres = strSearchTextFound
    
End Function
 
Private Function filesInFolder(year As Integer, month As Integer) As String()
    Dim strFolder As String
    Dim strPatern As String
    Dim sFile As String
    Dim strSubFolder As String
    Dim i As Long, j As Long, n As Long
    Dim aFiles() As String
    
    strPatern = year & Format(month, "00") & "*"
    strFolder = "F:\facsys Reporting\files\"
    strSubFolder = Dir(strFolder & strPatern, vbDirectory)
    
    Do While strSubFolder <> ""
        n = n + 1
        sFile = Dir(strFolder & strSubFolder & "\Receive.log", vbArchive)
        
        Do While sFile <> ""
            ReDim Preserve aFiles(i)
            aFiles(i) = strFolder & strSubFolder & "\" & sFile
            i = UBound(aFiles) + 1
            
            sFile = Dir   ' Obtiene siguiente entrada.
        Loop
        
        strSubFolder = Dir(strFolder & strPatern, vbDirectory)
 
        For j = 1 To n
            strSubFolder = Dir
            
        Next
    Loop
    
    filesInFolder = aFiles
 
End Function

Open in new window

0
 
j8547Author Commented:
yea that works perfectly. thanks. Just one quick question. What does matrizInicializada and SIN INICIAR mean. Thanks for all the help. I really appreciate it.
0
 
j8547Author Commented:
I have increase points as have a quick related question. I have all working. Searches through all logfiles of a particular month and prints the event id an path. It works perfectly.

The thing is I have to do the whole thing for another server aswell. The only thing I need to change is the server name, in that the logfile name etc is the name. I just want it to paste the event id's and path under the other ones for that month. Is ther a quick was of doing this, as in not having to do and the function again, use the same fuctions.
0
 
j8547Author Commented:
It's ok, got all working for both servers
0
 
FER_GCommented:
j8547; I'm Fernando Giletta, from Argentina... and my native language is Spanish, so the functions are in Spanish, and for a quick response i don't change it to English, so...
* matrizInicializada --> It's a function that returns TRUE if an array is initialized and FALSE if not. The translation to English will be... InitializedArray
* SIN INICIAR --> It's a message that the function will return if the array passed to the function is not initialized ...

with initialized i mean an array that is declared but it has no value on it...so if you wish to know a value , an error will raise... (Err.Number = 9)...

So, sorry about my english (a little poor) and my spanish on the code, but i think that if the code works, and it is useful to you...it's more valuable...and always have a time to ask a meaning of a word...

So. Best Regards and good luck!
FerG
--
Saludos.
--
Ing. Fernando D. Giletta
San Francisco. Córdoba. Argentina
0
 
aikimarkCommented:
some of the processing can be simplified if you replace the arrays with collections.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now