reading a data file

[Srikanth]

Hello,

I am trying to read a text file with data(+headers) with the following
format.

X
12.1
23.
43
54
..
..
..
Y
5
76
87
34
23
..
..
X
..
..
..
Y
..
..
..

I want to rearrange it into two columns X and Y. How do I do that. I
want to write en excel macro. Please help. Thanks

Srikanth

 

[Myrna Larson]

I haven't tested this, but I think it's "close".

Sub ReadTextFile()
Dim A As Variant
Dim C As Long
Dim CC As Long
Dim F As Long
Dim i as Long
Dim j as Long
Dim X() as Double
Dim Y() AS Double

C = 0
i = 0
j = 0

F = Freefile()
Open "MyFile.txt" For Input as #F '<<< REPLACE WITH CORRECT FILE NAME

Do While Not Eof(F)
'read a line from the file
Line Input #F, A

If IsNumeric(A) = False Then
'text -- check for valid header line
'if consists of single letter, X or Y,
'it's a flag ts determine which array to use
'any other text is ignored
If Len(A) = 1 Then
CC = Instr("XY",UCase$(A))
'if X or Y, change value of C appropriately
If CC Then C = CC
End If

Else
'numbers: save if C is 1 or 2, else discard
If C = 1 Then
'update counter, expand the array, and save value
i = i + 1
Redim Preserve X(1 To i)
X(i) = A
ElseIf C = 2 Then
'ditto for Y values
j = j + 1
Redim Preserve Y(1 to j)
Y(j) = A
End If
End If
Loop
Close #F

With ActiveSheet
'if there are X values, put them in column A, starting at A2
If i Then
Cells(2, 1).Resize(i, 1).Value = Application.Transpose(X())
End If

'if there are Y values, put them in column B, starting at B2
If j Then
Cells(2, 2).Resize(j, 1).Value = Application.Transpose(Y())
End If

End With
End Sub