coverting text data to excel

[anupamduttabesu]

Hi
I have atext file like this.
< ZEEO:ALL;
LOADING PROGRAM VERSION 22.10-0


BSC3i BSC1000 2012-04-10 11:23:47
BASE STATION CONTROLLER DATA
NUMBER OF PREFERRED CELLS ........................(NPC).... 3
GSM MACROCELL THRESHOLD ..........................(GMAC)... 35 dBm
GSM MICROCELL THRESHOLD ..........................(GMIC)... 33 dBm
COMMAND EXECUTED

I want get the in excel in the format.

BSC	NPC	GMAC	GMIC
BSC1000	3	35 dBm	33 dBm

<tbody>
</tbody>

I have written the code as.

Sub Macro1()
'
' Macro1 Macro
'

Dim sFileName As Variant
Dim iFileNum As Integer
Dim sBuf As String

Dim bolRecordAdding As Boolean
Dim aryData As Variant
Dim aryOutput As Variant

ReDim aryData(1 To 3, 0)

sFileName = Application.GetOpenFilename(",*.txt", , , , True)

'If Len(Dir$(sFileName)) = 0 Then
' Exit Sub
'End If
For a = LBound(sFileName) To UBound(sFileName)
iFileNum = FreeFile()

Open sFileName(a) For Input As iFileNum
Do While Not EOF(iFileNum)
Line Input #iFileNum, sBuf
If Not InStr(sBuf, "ZEEO:ALL") = 0 Then
Do While Not sBuf = "BASE STATION CONTROLLER DATA"
Line Input #iFileNum, sBuf
If sBuf Like "*BSC*" Then
If Not InStr(1, sBuf, " ") = 0 Then
abcd = Trim(Mid(sBuf, InStr(1, sBuf, " ") + 1, (InStr(12, sBuf, " ")) - (InStr(1, sBuf, " ") + 1)))
End If
End If
Loop
Do While Not sBuf = "COMMAND EXECUTED"

Line Input #iFileNum, sBuf

If sBuf Like "NUMBER OF PREFERRED CELLS ........................(NPC).*" Then
If Not InStr(1, sBuf, ".") = 0 Then
ReDim Preserve aryData(1 To 3, 1 To UBound(aryData, 2) + 1)
aryData(1, UBound(aryData, 2)) = abcd
aryData(2, UBound(aryData, 2)) = Trim(Mid(sBuf, InStr(1, sBuf, "(NPC)") + 9))
bolRecordAdding = True
End If

ElseIf sBuf Like "GSM MACROCELL THRESHOLD ..........................(GMAC)*" Then
If Not InStr(1, sBuf, ".") = 0 _
And bolRecordAdding Then

aryData(1, UBound(aryData, 2)) = Trim(Mid(sBuf, InStr(1, sBuf, "(GMAC)") + 9))
bolRecordAdding = False
End If

End If

Loop
End If
Loop
Close iFileNum
Next a

aryOutput = Application.Transpose(aryData)
Sheets("ZEEO").Select
Range("A2").Resize(UBound(aryOutput, 1), 3).Value = aryOutput
MsgBox ("Happy to Help You")
'
End Sub

Now when I am running the macro the output is coming for a single text file as input like..

BSC	NPC	GMAC	GMIC
BSC1000	3	35 dBm	33 dBm
BSC1000	3	35 dBm	33 dBm
BSC1000	3	35 dBm	33 dBm

<tbody>
</tbody>

It should be only a single row as output,but here it is coming in multiple rows(as no of columns) the same data .this is the problem.when I run the same macro for multiple text file it is working properly.

Please help

 

[patel45]

please upload the text file and xls with desired output on a share site then paste the link here

 

[anupamduttabesu]

please upload the text file and xls with desired output on a share site then paste the link here

How to upload the text and excel in a shared site?

 

[Rick Rothstein]

How to upload the text and excel in a shared site?

Use one of the free file sharing websites on the internet; for example, www.box.net/files is one such site (just be sure to note the URL they give you for your file... that is what you will need to post back here for us).