-
Solved: extract specific data into the sheet
Extract a specific word into the spreadsheet\
Once opened the file,
Look for the word “execute”
1. If the word “execute’ is the first word of the line, then extract the next word after the word :”execute” and place in column A Take a look at the image below
http://www.iimmgg.com/image/46b40b45...1c49d332eb0bf1
2. If the word “execute’ is not the first word of the line, then extract the next word after the word :”execute” and place in column B Take a look at the image below
http://www.iimmgg.com/image/1499df06...22a20ea17a8671
I have attached a sample workbook
Sheet 1 shows how my sheet looks like once extracted
Sheet 2 contains a button where the codes should be inserted
I have also attached a sample .txt file that I need to extract
extract.zip
-
I did build in a little functionality in that you can pick the file and change the search string in the click event.
in sheet 2 code
[vba]Private Sub CommandButton1_Click()
Call KeywordSearch("execute")
End Sub
[/vba]
In module1
[vba]Sub KeywordSearch(SearchWord As String)
Dim FFile As Long
Dim FN$
Dim Temp$
Dim TempArray$()
Dim LastRow As Long
FN$ = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FN$ <> "" Then
FFile = FreeFile
With Sheet1
LastRow = .Range("A65536").End(xlUp).Row + 1
.Range("A1:B" & LastRow).ClearContents
.Range("A1").Value = "Last Run " & Now
End With
Open FN$ For Input As FFile
Do
Line Input #FFile, Temp$
If InStr(Temp$, SearchWord) > 0 Then
Temp$ = Trim(Temp$)
If Left(Temp$, Len(SearchWord)) = SearchWord Then
TempArray$ = Split(Temp$, " ")
With Sheet1
LastRow = .Range("A65536").End(xlUp).Row + 1
Sheet1.Cells(LastRow, 1).Value = TempArray$(1)
End With
Else
TempArray$ = Split(Temp$, " ")
With Sheet1
LastRow = .Range("B65536").End(xlUp).Row + 1
Sheet1.Cells(LastRow, 2).Value = TempArray$(UBound(TempArray$))
End With
End If
End If
Loop Until EOF(FFile)
Close FFile
End If
End Sub
[/vba]
-
Thanks david for your time and help. It works
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules