VBA text file search

Question

I’m trying to write a VBA procedure that searches for usernames in a text file to find the user's IP address. So for example, given the input below, if I search Chris Trucker I want to see 192.168.130.22 in a message box.

> 192.168.2.151,Super Fly,ABC\Flys,2012-05-18 16:11:29 
> 192.168.2.200,Rain,ABC\rain,2012-05-17 15:42:05 
> 192.168.2.210,Snow,ABC\Snow,2012-05-16 08:24:39 
> 192.168.2.78,Wind,ABC\wind,2012-05-02 19:24:06 
> 192.168.130.21,Mike Jordan,ABC\Jordanm,2012-05-18 17:28:11 
> 192.168.130.22,Chris Trucker,ABC\Truckerc,2012-05-18 17:28:11 
> 192.168.130.23,Chris Jackson,ABC\JacksonC,2012-05-18 17:04:39

Tried the following but it's VBScript

Const ForReading = 1

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "JacksonC"  

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("\\server\tsusers\Users.txt", ForReading)

Do Until objFile.AtEndOfStream
    strSearchString = objFile.ReadLine
    osakapc = Left(strSearchString,14)
    Set colMatches = objRegEx.Execute(strSearchString)

    If colMatches.Count = 1 Then 
        For Each strMatch in colMatches  


        Next
    End If
Loop

Answer 1

Here's how I would do it:

Option Explicit

Sub tester()
    Dim inputFilePath As String
    inputFilePath = "\\server\tsusers\Users.txt"

    MsgBox GetUserIpAddress("Chris Trucker", inputFilePath) 
                            ' or "JacksonC" or "Bozo" or whatever

End Sub

Function GetUserIpAddress(whatImLookingFor As String, _
    inputFilePath As String)
    Const ForReading = 1

    Dim foundIt As Boolean
    Dim thisLine As String
    Dim ipAddress As String
    Dim FSO As Object
    Dim filInput As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set filInput = FSO.OpenTextFile(inputFilePath, ForReading)

    foundIt = False
    Do Until filInput.AtEndOfStream
        thisLine = filInput.ReadLine
        If InStr(thisLine, whatImLookingFor) <> 0 Then
            foundIt = True
            ipAddress = Replace((Split(thisLine, ",")(0)), "> ", "")
            Exit Do
        End If
    Loop

    If foundIt Then
        GetUserIpAddress = ipAddress
    Else
        Err.Raise 9999, , _
            "I stiiiiiiiill haven't foooouuuund what I'm looking for."
    End If
End Function

As you see, this function throws an error if the user name isn't found.

Note that this function allows you to search for the user name in long form (Chris Trucker) or short form (Truckerc) or even the timestamp (2012-05-18 17:28:11). But be aware that if there are more than one instance of your search term, then only the IP address corresponding to the first instance will be returned. You can adapt the code if you want all instances to be returned.

As a final comment, it is advisable to always declare all of your variables and force yourself to do so by having Option Explicit at the top of your code.