Transpose from .txt files

[Davorito]

Case:
Public Sub Example1()'dialog result
Dim intDialogResult As Integer
'path selected by user
Dim strPath As String
'single line of data from the text file
Dim strLine As String
'string seperated by the delmiter
Dim arrString() As String
'curren row in excel sheet
Dim i As Integer
Dim FileName As String
'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show
'if the user selected a file
If intDialogResult <> 0 Then
'path selected by the user
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
'close the file index #1 if its already opened
Close #1
'open the file for reading
Open strPath For Input As #1
i = 1
'loop while the end of file has not been reached
While EOF(1) = False
'read one line of data from the text file
Line Input #1, strLine
'split string based on delimiter
arrString = Split(strLine, " ")
'I only need the 6th column values
ActiveCell(i, 2) = arrString(6)
i = i + 1
Wend
End If
Close #1
ActiveCell.Offset(0, 1).Activate
End Sub


Basically what it does is it takes values from 6th column from a .txt file and puts them next to each other each time the macro runs.


Problem 1: I want to store them horizontally (up to 200 values in each column from .txt file)


Problem 2: I want to set the first cell as the file name

[snb]

Please use code tags !


Sub M_snb()
sn=split(createobject("scripting.filesystemobject").opentextfile("G:\OF\example.txt").readall,vbcrlf)

for j=0 to ubound(sn)-1
sn(j)=split(sn(j),";")(5)
next

sheets(1).cells(1).resize(,ubound(sn))=sn
End Sub