ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   .txt In .xls out Macro (https://www.excelbanter.com/excel-programming/348430-txt-xls-out-macro.html)

Telecommm
.txt In .xls out Macro
 
Hi Experts,
Hoping you in good health. I have the following question:
I have a text file which is approximately 50 to 70 pages long. It contains a
specific word €œBSS€ again and again, what I want to do is to design an excel
sheet which takes this text file as input and finds out this required word
(which is BSS), copy the next 20 words after BSS and paste it into excel
sheet in cell B4. It should search the text file again and find the word BSS,
and copies next 20 words in cell B5 and so on.

Thanks for your support

Dick Kusleika[_4_]
.txt In .xls out Macro
 
Telecommm

Try this:

Sub ReadBSS()

Dim sFname As String
Dim lFnum As Long
Dim lRowCnt As Long
Dim sLine As String
Dim lBssFound As Long

sFname = "C:\tester.txt"
lFnum = FreeFile

Open sFname For Input As lFnum

Do While Not EOF(lFnum)
Line Input #lFnum, sLine

If sLine = "BSS" Then
lBssFound = 1
End If

If lBssFound 1 Then
lRowCnt = lRowCnt + 1
Sheet1.Cells(lRowCnt, 1).Value = sLine
End If

If lBssFound = 21 Then
lBssFound = 0
End If

If lBssFound = 1 Then
lBssFound = lBssFound + 1
End If

Loop

End Sub


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Telecommm wrote:
Hi Experts,
Hoping you in good health. I have the following question:
I have a text file which is approximately 50 to 70 pages long. It
contains a specific word "BSS" again and again, what I want to do is
to design an excel sheet which takes this text file as input and
finds out this required word (which is BSS), copy the next 20 words
after BSS and paste it into excel sheet in cell B4. It should search
the text file again and find the word BSS, and copies next 20 words
in cell B5 and so on.

Thanks for your support



Telecommm
.txt In .xls out Macro
 
Hi,
Thanks for the reply. I have tried this but its not giving any output. Excel
Sheet is having no output.
Am i doing something wrong. Please advice.
Thanks



"Dick Kusleika" wrote:

Telecommm

Try this:

Sub ReadBSS()

Dim sFname As String
Dim lFnum As Long
Dim lRowCnt As Long
Dim sLine As String
Dim lBssFound As Long

sFname = "C:\tester.txt"
lFnum = FreeFile

Open sFname For Input As lFnum

Do While Not EOF(lFnum)
Line Input #lFnum, sLine

If sLine = "BSS" Then
lBssFound = 1
End If

If lBssFound 1 Then
lRowCnt = lRowCnt + 1
Sheet1.Cells(lRowCnt, 1).Value = sLine
End If

If lBssFound = 21 Then
lBssFound = 0
End If

If lBssFound = 1 Then
lBssFound = lBssFound + 1
End If

Loop

End Sub


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Telecommm wrote:
Hi Experts,
Hoping you in good health. I have the following question:
I have a text file which is approximately 50 to 70 pages long. It
contains a specific word "BSS" again and again, what I want to do is
to design an excel sheet which takes this text file as input and
finds out this required word (which is BSS), copy the next 20 words
after BSS and paste it into excel sheet in cell B4. It should search
the text file again and find the word BSS, and copies next 20 words
in cell B5 and so on.

Thanks for your support




kounoike
.txt In .xls out Macro
 
some quetion:

1.what do you mean by "word"? for example, "BSSABC" is the specific word €œBSS€
or not?
2.if next BSS appear while copying the next 20 words after BSS, how should this
BSS be treated?

keizi

"Telecommm" wrote in message
...
Hi Experts,
Hoping you in good health. I have the following question:
I have a text file which is approximately 50 to 70 pages long. It contains a
specific word €œBSS€ again and again, what I want to do is to design an excel
sheet which takes this text file as input and finds out this required word
(which is BSS), copy the next 20 words after BSS and paste it into excel
sheet in cell B4. It should search the text file again and find the word BSS,
and copies next 20 words in cell B5 and so on.

Thanks for your support

Dick Kusleika[_4_]
.txt In .xls out Macro
 
Telecommm wrote:
Hi,
Thanks for the reply. I have tried this but its not giving any
output. Excel Sheet is having no output.
Am i doing something wrong. Please advice.
Thanks


I assumed that each "word" was on its own line and there was nothing else on
that line. If you think it will help, post a small sample of the text
that's in the file. If there's more on the "BSS" line than just that,
you'll have to adjust this line:

If sLine = "BSS" Then

to something like

If Instr(1, sLine, "BSS") 1 Then

which will find BSS anywhere in that line. Or, if it's always at the start

If Left$(sLine, 3) = "BSS" Then


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com



Telecommm
.txt In .xls out Macro
 
Hi,
Please have a look on the following text:


SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 110
Distribution Max. : 123
Distribution Min. : 83
Bin : 0 0 0 1 1394 1425 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 134
Distribution Min. : 90
Bin : 0 0 0 0 1840 731 3 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 111
Distribution Max. : 120
Distribution Min. : 88
Bin : 0 0 0 1 908 2228 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 121
Distribution Min. : 89
Bin : 0 0 0 1 914 283 0 0 0 0



I want to extract the number after the word "Distribution Mean" ( in last
group its 106).
Thanks a lot for the replies.
Best Regards,

Telecommm








"kounoike" wrote:

some quetion:

1.what do you mean by "word"? for example, "BSSABC" is the specific word €œBSS€
or not?
2.if next BSS appear while copying the next 20 words after BSS, how should this
BSS be treated?

keizi

"Telecommm" wrote in message
...
Hi Experts,
Hoping you in good health. I have the following question:
I have a text file which is approximately 50 to 70 pages long. It contains a
specific word €œBSS€ again and again, what I want to do is to design an excel
sheet which takes this text file as input and finds out this required word
(which is BSS), copy the next 20 words after BSS and paste it into excel
sheet in cell B4. It should search the text file again and find the word BSS,
and copies next 20 words in cell B5 and so on.

Thanks for your support

kounoike
.txt In .xls out Macro
 
it seems very diffrent one from your first post to me.
if the format is uniform, it could be written more simple and smart way.
but try this, if you don't mind.

Sub testdo()
Dim exflname
Dim i As Long, k As Long
Dim filenum1
Dim textline As String
Dim Words
Dim findword As Boolean
Const word1 = "Distribution"
Const word2 = "Mean"

MsgBox "select data file"

exflname = Application.GetOpenFilename

If exflname = False Then
MsgBox "file not selected"
Exit Sub
End If

filenum1 = FreeFile()
Open exflname For Input As #filenum1
k = 1
Do While Not EOF(filenum1)
Line Input #1, textline
Words = Getwords(textline)
i = LBound(Words)
Do While (i <= UBound(Words))
If findspword(Words(i), word1) Then
i = i + 1
If findspword(Words(i), word2) Then
i = i + 1
Do While (i <= UBound(Words))
If IsNumeric(Words(i)) Then
Cells(k, "b") = Words(i)
k = k + 1
Exit Do
End If
i = i + 1
Loop
End If
End If
i = i + 1
Loop
Loop
Close #filenum1
End Sub

keizi

"Telecommm" wrote in message
...
Hi,
Please have a look on the following text:


SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 110
Distribution Max. : 123
Distribution Min. : 83
Bin : 0 0 0 1 1394 1425 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 134
Distribution Min. : 90
Bin : 0 0 0 0 1840 731 3 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 111
Distribution Max. : 120
Distribution Min. : 88
Bin : 0 0 0 1 908 2228 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 121
Distribution Min. : 89
Bin : 0 0 0 1 914 283 0 0 0 0



I want to extract the number after the word "Distribution Mean" ( in last
group its 106).
Thanks a lot for the replies.
Best Regards,

kounoike
.txt In .xls out Macro
 
very sorry for posting imcomplete one. i forgot to attach two auxiliay functions.
this is full one.

Sub testdo()
Dim exflname
Dim i As Long, k As Long
Dim filenum1
Dim textline As String
Dim Words
Dim findword As Boolean
Const word1 = "Distribution"
Const word2 = "Mean"

MsgBox "select data file"

exflname = Application.GetOpenFilename

If exflname = False Then
MsgBox "file not selected"
Exit Sub
End If

filenum1 = FreeFile()
Open exflname For Input As #filenum1
k = 1
Do While Not EOF(filenum1)
Line Input #1, textline
Words = Getwords(textline)
i = LBound(Words)
Do While (i <= UBound(Words))
If findspword(Words(i), word1) Then
i = i + 1
If findspword(Words(i), word2) Then
i = i + 1
Do While (i <= UBound(Words))
If IsNumeric(Words(i)) Then
Cells(k, "b") = Words(i)
k = k + 1
Exit Do
End If
i = i + 1
Loop
End If
End If
i = i + 1
Loop
Loop
Close #filenum1
End Sub

Function findspword(ByVal s As String, ByVal spword As String) As Boolean
If s = spword Then
findspword = True
Else
findspword = False
End If
End Function

Function Getwords(ByVal s) As Variant
Getwords = split(Application.Trim(Application.Clean(s)), " ")
End Function

keizi

"kounoike" wrote in message
...
it seems very diffrent one from your first post to me.
if the format is uniform, it could be written more simple and smart way.
but try this, if you don't mind.


"Telecommm" wrote in message
...
Hi,
Please have a look on the following text:


SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 110
Distribution Max. : 123
Distribution Min. : 83
Bin : 0 0 0 1 1394 1425 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 134
Distribution Min. : 90
Bin : 0 0 0 0 1840 731 3 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 111
Distribution Max. : 120
Distribution Min. : 88
Bin : 0 0 0 1 908 2228 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 121
Distribution Min. : 89
Bin : 0 0 0 1 914 283 0 0 0 0



I want to extract the number after the word "Distribution Mean" ( in last
group its 106).
Thanks a lot for the replies.
Best Regards,

Telecommm
.txt In .xls out Macro
 
Hi,

First of all i apologize for the inconvenience which you had to face becuase
of my misleading question ( I wanted to ask a general question so that it
will not take too much of your time to answer) Secondly, I am grateful to you
for your help. It worked and worked perfectly ! ! !
Thanks again,



"kounoike" wrote:

very sorry for posting imcomplete one. i forgot to attach two auxiliay functions.
this is full one.

Sub testdo()
Dim exflname
Dim i As Long, k As Long
Dim filenum1
Dim textline As String
Dim Words
Dim findword As Boolean
Const word1 = "Distribution"
Const word2 = "Mean"

MsgBox "select data file"

exflname = Application.GetOpenFilename

If exflname = False Then
MsgBox "file not selected"
Exit Sub
End If

filenum1 = FreeFile()
Open exflname For Input As #filenum1
k = 1
Do While Not EOF(filenum1)
Line Input #1, textline
Words = Getwords(textline)
i = LBound(Words)
Do While (i <= UBound(Words))
If findspword(Words(i), word1) Then
i = i + 1
If findspword(Words(i), word2) Then
i = i + 1
Do While (i <= UBound(Words))
If IsNumeric(Words(i)) Then
Cells(k, "b") = Words(i)
k = k + 1
Exit Do
End If
i = i + 1
Loop
End If
End If
i = i + 1
Loop
Loop
Close #filenum1
End Sub

Function findspword(ByVal s As String, ByVal spword As String) As Boolean
If s = spword Then
findspword = True
Else
findspword = False
End If
End Function

Function Getwords(ByVal s) As Variant
Getwords = split(Application.Trim(Application.Clean(s)), " ")
End Function

keizi

"kounoike" wrote in message
...
it seems very diffrent one from your first post to me.
if the format is uniform, it could be written more simple and smart way.
but try this, if you don't mind.


"Telecommm" wrote in message
...
Hi,
Please have a look on the following text:


SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 0
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 110
Distribution Max. : 123
Distribution Min. : 83
Bin : 0 0 0 1 1394 1425 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 1
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 134
Distribution Min. : 90
Bin : 0 0 0 0 1840 731 3 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 2
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 111
Distribution Max. : 120
Distribution Min. : 88
Bin : 0 0 0 1 908 2228 0 0 0 0

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3

SITE : 4 CELL #: LAC=029Ah CI=E2B9h
RTF ID : 0 3
MEAS_TYPE : PATH_BALANCE
Distribution Mean : 106
Distribution Max. : 121
Distribution Min. : 89
Bin : 0 0 0 1 914 283 0 0 0 0



I want to extract the number after the word "Distribution Mean" ( in last
group its 106).
Thanks a lot for the replies.
Best Regards,

kounoike
.txt In .xls out Macro
 
your welcome!
keizi

"Telecommm" wrote in message
...
Hi,

First of all i apologize for the inconvenience which you had to face becuase
of my misleading question ( I wanted to ask a general question so that it
will not take too much of your time to answer) Secondly, I am grateful to you
for your help. It worked and worked perfectly ! ! !
Thanks again,


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com