Find text in File, Copy and Paste in Cells Excel VBA

Question

I am writing a code in VBA to look into a file for a characters that the unser inputs in the macro, after the macro finds the characters provided by the user I want it to extract that whole line and past it in a sheet in excel by taking the first characters and placing them in cell 1a then taking the next set of characters which are seperated by a space in the text and placing them into another cell, here is what I have so far and want to replace line 17 with the extraction of data;

    Dim oFSO As Object
    Dim arrData() As String

    Sub test()
    Dim f As Integer
    Dim IngLine As Long
    Dim strLine As String
    Dim bInFound As Boolean
    f = FreeFile
    Tfile = "C:\TAXETI.TXT"
    staffdat = InputBox(Prompt:=" Please enter the staff number", Title:="Load Staff Data")
         Open Tfile For Input As #f
             Do While Not EOF(f)
                  IngLine = IngLine + 1
                  Line Input #f, strLine
                  If InStr(1, strLine, staffdat, vbBinaryCompare) > 0 Then
                     MsgBox "Search string found in line" & IngLine, vbInformation
                     bInFound = True
                     Exit Do
                  End If
             Loop
             Close #f
             If Not bInFound Then
             MsgBox "Search string not found", vbInformation
             End If
    End Sub

score 1 · Accepted Answer · edited May 08 '18 at 21:48

1

I suggest you look into the Split() function.

MSDN Reference

Another example

I Think your code would end up like the following in #17(You would have to either trim/determine where to 'tokenize' your line, depending what your input file looks like):

If InStr(1, strLine, staffdat, vbBinaryCompare) > 0 Then
 foundArr = Split(strLine, " ")'change " " to whatever fits your needs
 For i = LBound(foundArr) to UBound(foundArr)
    'enter each string into a cell, etc
 Next i

edited May 08 '18 at 21:48

Community

1
1

answered Dec 05 '13 at 16:39

Max Alcala

781
6
17

No problem! Seems like you already had most of the problem solved. – Max Alcala Dec 05 '13 at 18:53
Hey Max, how about if once instr function finds the character I want it to go 3 lines down and do the mid() function, what would I use for this? – Alfredo A. Dec 12 '13 at 22:44
Hi @AlfredoA., you can use the same method you are using to read in lines `Line Input #f, strLine` to "read" in the next 3 lines when you find your character - thus going down the way you wanted to. However be aware you may find your character less than 3 lines down to the end of your file, so you may need to do error trapping for this scenario. Also, you may want to look into [FileSystemObject](http://ss64.com/vb/filesystemobject.html) for files I/O, I find it much easier. – Max Alcala Dec 20 '13 at 21:02

Find text in File, Copy and Paste in Cells Excel VBA

1 Answers1