Geek asks and answers

Search for VBA text file

I am trying to write a VBA procedure that searches for usernames in a text file to find the IP address of a user. So, for example, given the input below, if I'm looking Chris Trucker, I want to see 192.168.130.22messages in the window.

> 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 this is 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
+3
source share
4 answers

Here is 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 can see, this function throws an error if the username is not found.

, (Chris Trucker) (Truckerc) (2012-05-18 17:28:11). , , IP-, . , , .

, Option Explicit .

+3

Private Function ReturnNames(fPath$, pName$) As String
    'this This example uses **Microsoft VBScript Regular Expressions 5.5** and **Microsoft Scripting Runtime**
    Dim result$
    Dim re As New RegExp, fso As New FileSystemObject
    If fso.FileExists(fPath) = True Then
        Dim contents$, mt As Match, mts As MatchCollection
        contents = fso.OpenTextFile(fPath, ForReading).ReadAll
        With re
            .Global = True
            .MultiLine = True
            .Pattern = "^> *([^,\r\n]+),([^,\r\n]+),([^,\r\n]+),([^,\r\n]+)$"
            If .test(contents) = True Then
                Set mts = .Execute(contents)
                For Each mt In mts
                    If LCase(mt.SubMatches(1)) = LCase(pName) Then
                        result = mt.SubMatches(0)
                        Exit For
                    End If
                Next mt
            End If
        End With
        If result = "" Then
            result = "No matches found for '" & pName & "'."
        End If
    Else
        result = "File not found."
    End If

    ReturnNames = result

End Function


Public Sub test000()
    MsgBox ReturnNames("C:\Documents and Settings\Patel_81\Desktop\1.txt", "Chris Trucker")
End Sub
+3

!

, , , , IP- , :

Function GetIPAddress(fileName As String, userName As String) As String

  Dim userinfo As String
  Dim tokens As Variant
  Dim laststring As Variant
  Dim userIP As String

  ' read text file into string
  userinfo = GetText(fileName)
  ' remove everything after the name we are looking for
  tokens = Split(userinfo, userName)(0)
  ' get the second-to-last comma-delimited value
  laststring = Split(tokens, ",")(UBound(Split(tokens, ",")) - 1)
  ' split by > and get second element
  userIP = Trim$(Split(laststring, ">")(1))

  GetIPAddress = userIP
End Function

Charley Kyd:

Function GetText(sFile As String) As String
  Dim nSourceFile As Integer, sText As String
  ''Close any open text files
  Close
  ''Get the number of the next free text file
  nSourceFile = FreeFile
  ''Write the entire file to sText
  Open sFile For Input As #nSourceFile
  sText = Input$(LOF(1), 1)
  Close
  GetText = sText
End Function


Sub testgetip()
  Debug.Print GetIPAddress("\\server\tsusers\Users.txt", "Chris Trucker")
End Sub

, , ( 9), .

:

Function GetIPAddress(fileName As String, searchTerm As String) As String

  Dim userinfo As String
  Dim tokens As Variant
  Dim i As Long
  Dim userIP As String

  ' read text file into string
  userinfo = GetText(fileName)
  ' split text file by line breaks
  tokens = Split(userinfo, vbCrLf)

  ' loop through array and look for line that contains search term
  For i = LBound(tokens) To UBound(tokens)
    If InStr(tokens(i), searchTerm) > 0 Then  ' found it
      ' get first element of comma-split string, then second element of space-split string
      GetIPAddress = Split(Split(tokens(i), ",")(0), " ")(1)
      Exit For
    End If
  Next i
End Function

- Charley Kyd.

, , , , . Jean, , .

Sub testgetip()
  Dim ipaddr As String
  ipaddr = GetIPAddress("\\server\tsusers\Users.txt", "Trucker")

  If Len(ipaddr) = 0 Then
    MsgBox "Could not find IP address for that search term"
  Else
    Debug.Print ipaddr
  End If
End Sub
0

More articles:


All Articles