VBA macro to write from text file to excel

[michielvrijb]

Hey,

I want to write a little vba program that does the following:

1) I have a txt file, e.g. test.txt which contains several columns.
for example :
x y z
0.215 0.315 0.3877
0.351 0.215 0.358
0.254 0.157 0.389

And i want the xcolumn to be writen on worksheet 1 column A and column y to excel column B

Does anyone know who to do this? (the excel file is in same directory as test.txt)

Thanks,

Michiel

[Bob Phillips]

Just do it with the macro recorder turned on, you will get all your code that way.

[TomSmith]

I can see at least two possible methods. (1) one method is to switch on the macro recorder, then you would open your file test.txt in Excel, use the "text to columns" conversion function, then copy or move the two columns "x" and "y" to their destinations. Then modify the recorded macro to make it more efficient. (2) I have created utilities to import numerical data from text files many times in the past, but not recently. But the method I used then is what I still prefer now: Write the VBA routine to open the test.txt file "for input" (see your VBA Help), then perform a loop to read each line of input in the input file, parse the line to extract the data I wanted, then pop this data into the desired position on the spreadsheet, then repeat the loop until the end of file or end of data. This method seems like more work but you will have more control over the final results and better understanding of the process. It also allows you to ignore lines in your input file that are not "data".

[fixo]

Hey,

I want to write a little vba program that does the following:

1) I have a txt file, e.g. test.txt which contains several columns.
for example :
x y z
0.215 0.315 0.3877
0.351 0.215 0.358
0.254 0.157 0.389

And i want the xcolumn to be writen on worksheet 1 column A and column y to excel column B

Does anyone know who to do this? (the excel file is in same directory as test.txt)

Thanks,

Michiel

Hi Michiel
Give this a shot



Sub ImportTextFile()
Dim iLastRow&, sFilt$, sTitle, sLine$, ffOpen$, i&, j&
iLastRow& = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Cells(iLastRow&, 1).Select
sFilt = "Text Files (*.txt), *.txt"
sTitle = "Select text file to import"
ffOpen = Application.GetOpenFilename(FileFilter:=sFilt, FilterIndex:=1, Title:=sTitle)
If ffOpen <> vbNullString Then
MsgBox "Open " & ffOpen
End If
Application.ScreenUpdating = False
Open (ffOpen) For Input As #1
i = iLastRow&
Do While Not EOF(1)
Input #1, sLine
Dim x
x = Split(sLine, Chr(32))
For j = 0 To UBound(x)
Cells(i, j + 1) = x(j)
Next j
i = i + 1
Loop
Close #1
With ActiveSheet
.Columns.AutoFit
.Rows.AutoFit
.UsedRange.Select
Selection.NumberFormat = "0.000"
End With
Application.ScreenUpdating = True
End Sub


~'J'~