Reading Text File into Excel

[ysvsr1]

Hi all,
I am trying to read a space delimited text file into sheet2 named as "Columns" of a workbook starting at cells(2,1) or A2.

Input text file has three columns and the excel has prefined formulas in it.

I am a beginner and so i am not sure what is the easiest way to do it.

I tried to open the text file, parse each line into strings and then write to the "Columns sheet" but i am having problem as the third column has , in it. Ex: Decimal(15,6). So it is getting truncated to Decimal(15. I am not sure how to escape the "," as delimiter. There may be other easier ways to do this. Following is the code i am using:

Dim str As String
Dim str1 As String
Dim str2 As String

Sub ImportData()

Open "C:\Documents and Settings\abcxyz\Desktop\test.txt" For Input As #1
i = 6
Do While (Not EOF(1))

Input #1, streng
StrArray = Split(streng, " ")
For j = LBound(StrArray) To UBound(StrArray)
Sheets("Columns").Cells(i, j + 1) = StrArray(j)
Next j
i = i + 1
Loop
Close #1
End Sub

[mohanvijay]

Try this


Dim O_FYS As Object
Dim O_TxtFile As Object
Dim Txt_File As String
Dim Temp_Str As String
Dim Hld_Str
Dim i As Long, j As Long
i = 0
Txt_File = "C:\Documents and Settings\abcxyz\Desktop\test.txt"
Set O_FYS = CreateObject("Scripting.FileSystemObject")
Set O_TxtFile = O_FYS.OpenTextFile(Txt_File)
With O_TxtFile
Do Until .AtEndOfStream = True
Temp_Str = .ReadLine
Hld_Str = Split(Temp_Str, " ")
i = i + 1
For j = 0 To UBound(Hld_Str)
Cells(i, j + 1).Value = CStr(Hld_Str(j))
Next j
Loop
.Close
End With
Set O_TxtFile = Nothing
Set O_FYS = Nothing