Search for text in txt file and copy the words to excel sheet

aejaz7

New Member
Joined
Nov 6, 2018
Messages
20
Hi,

I have a particular software which contain my client user name and it's password along with other misc details like address full name etc.
Client password going to expired after every three month. I have sheet which column A contain user name and column b for password.

Client password in text file is written after user name like

VBA Code:
sampleusername     password1     
      sampleusername1 password2                              sampleusername3                                       password3


I would like to use excel vba to search the text file for particular user name and copy password in excel sheet.

Is there any code I could use to solve the problem? Thank you for your help.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi, I am not an expert but thought I may be able to help. Is there a reason why you can't just copy all of the data from the txt file into Excel and have Excel pick out what you need with Forumula's such as Search or Text?
 
Upvote 0
Hi, I am not an expert but thought I may be able to help. Is there a reason why you can't just copy all of the data from the txt file into Excel and have Excel pick out what you need with Forumula's such as Search or Text?
I can't because text file is around 8 to 10 mb big, have much lines and other data also contain like address, mobile no, email, file no, pincode etc.
one thing is common that password is after username and some space (space between user name and password also not fix).
 
Upvote 0
I can't because text file is around 8 to 10 mb big, have much lines and other data also contain like address, mobile no, email, file no, pincode etc.
one thing is common that password is after username and some space (space between user name and password also not fix).
So you can't do Ctrl+A, then copy and paste it all in an Excel Sheet, then use this method/Formula to Split it:


Then create another Sheet in the Same workbook to retrieve the data from the Columns that contain the usernames and passwords. (Hide the Sheet with the data if you want too, so the other data - such as addresses - can't be seen)

Hopefully that will work for you.
 
Upvote 0
So you can't do Ctrl+A, then copy and paste it all in an Excel Sheet, then use this method/Formula to Split it:


Then create another Sheet in the Same workbook to retrieve the data from the Columns that contain the usernames and passwords. (Hide the Sheet with the data if you want too, so the other data - such as addresses - can't be seen)

Hopefully that will work for you.
text file data not in such format to use this formula.

i need some correction in below mentioned code.

VBA Code:
Sub MrExcelTxt()
Dim FSO As Object
Set FSO = CreateObject("scripting.filesystemobject")
Dim Src As Object
Dim Str As Object
Dim Content As String
Dim Arr As Variant
Dim Arr2 As Variant
Dim x As Long, y As Long, p As Long
p = 1
Set Src = FSO.GetFile(InputBox("Enter Full file path here"))
Set Str = Src.OpenAsTextStream
Content = Str.ReadAll
Arr = Strings.Split(Content, ";")
For x = 0 To UBound(Arr)
    If InStr(Arr(x), "Year") Then
        Arr2 = Strings.Split(Arr(x), ",")
            Cells(p, 1) = Arr2(UBound(Arr2) - 1)
            p = p + 1
    End If
Next
MsgBox "Done"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top