Importing a text file to excel using VB
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
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))
i = UBound(vaData)
For j = 0 To i
vaData(j) = Application.Clean(vaData(j))
Range(Cells(1, 1), Cells(i, 1)).Value = vaData
Set fsoFile = Nothing
Set fsoObj = Nothing