hello there - I am a newbie and could do with a bit of a hand. I have this bit of code which is taking data from a text file and importing it in to excel.

However i am having a few problems with the code that i cant solve:
1) When I run it brings up the contents of the text file 7 times in column A1 - as there are 7 items in the txt file.

2) When I import the data it does not split the text for e.g in the text file i have a description caled SC and a value of 24.00 (tab delimited) but the code brings everyting in one cell.

I would post the text file but I dont think I can attach anything on this forum.

Any help is more than appreciated

Cheers

Option Explicit

Sub Read_Text_File()
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoTS As Scripting.TextStream
Dim vaData As Variant
Dim i As Long, j As Long

Set fsoObj = New Scripting.FileSystemObject
Set fsoFile = fsoObj.GetFile("c:\testing.txt")
Set fsoTS = fsoFile.OpenAsTextStream(ForReading, TristateFalse)

vaData = Split(fsoTS.ReadAll, Chr(13))

fsoTS.Close

i = UBound(vaData)

For j = 0 To i
    vaData(j) = Application.Clean(vaData(j))
Next j

Range(Cells(1, 1), Cells(i, 1)).Value = vaData

Set fsoFile = Nothing
Set fsoObj = Nothing
End Sub

This seems to work:

Sub Read_Text_File()
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoTS As Scripting.TextStream
Dim vaData As Variant
Dim i As Long, j As Long

Set fsoObj = New Scripting.FileSystemObject
Set fsoFile = fsoObj.GetFile("c:\testing.txt")
Set fsoTS = fsoFile.OpenAsTextStream(ForReading, TristateFalse)


j = 1
Do Until fsoTS.AtEndOfStream
    vaData = Split(fsoTS.ReadLine, Chr(44))
    Range(Cells(j, 1), Cells(j, 2)).Value = vaData
j = j + 1
Loop

fsoTS.Close
Set fsoFile = Nothing
Set fsoObj = Nothing
End Sub