PDA

View Full Version : Solved: extract specific data into the sheet



rafi_07max
01-16-2011, 07:44 PM
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/46b40b45f57f4f299b1c49d332eb0bf1

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/1499df067db18205e922a20ea17a8671

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
5243

Tinbendr
01-17-2011, 05:02 AM
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
Private Sub CommandButton1_Click()
Call KeywordSearch("execute")
End Sub

In module1

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

rafi_07max
01-17-2011, 11:17 PM
Thanks david for your time and help. It works :)