Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a data file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a data file
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 On 14 Sep 2004 15:09:24 -0700, (Srikanth) wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reading from another file and pasting to current file, "combobox" | New Users to Excel | |||
Reading a text file ? | Excel Worksheet Functions | |||
reading contents of a file | Excel Programming | |||
Reading from ini file | Excel Programming | |||
Reading from file | Excel Programming |