VBA text delimited to excel

[ericlch16]

Hi

I have a text file with mulitple rows. Two sample row with text are as follows:-

|xxxx| jjjjj | kkkk |
|yy| llll | mmm |

How can i use VBA to put it in excel so that

cell(1,1) = xxxx
cell(1,2) = jjjjj
cell(1,3) = kkkk

cell(2,1) = yy
cell(2,2) = llll
cell(2,3) = mmm

Do I need to explode the text in an array first and then loop into to paste it in the cell row by row. Any idea how to do it efficiently in VBA? I am looping in the textfile to output it in excel.

Thanks,

 

[VoG]

If you import it into Excel you should be able to use Data > Text to Columns.

 

[ericlch16]

If you import it into Excel you should be able to use Data > Text to Columns.

I would like VBA to do it. Any idea how?

 

[VoG]

Record a macro whilst using Text to Columns.

 

[Rick Rothstein]

I would like VBA to do it. Any idea how?

This macro will read in your text file directly and then output the cell values you want into the ActiveSheet (starting at cell A1)...

Sub ReadTextFileAndSplitIntoRowsAndColumns()
  Dim FileNum As Long, TotalFile As String, Lines() As String
  FileNum = FreeFile
  Open "[COLOR=#FF0000][B]c:\temp\textfile.txt[/B][/COLOR]" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  TotalFile = Replace(Replace(Replace(Replace(TotalFile, "| ", "|"), " |", "|"), "|" & vbCr, vbCr), vbLf & "|", vbLf)
  Lines = Split(Mid(TotalFile, 2, Len(TotalFile) - 2), vbNewLine)
  Range("A1").Resize(UBound(Lines) + 1) = Application.Transpose(Lines)
  Columns("A").TextToColumns Range("A1"), xlDelimited, xlTextQualifierDoubleQuote, False, False, False, False, False, True, "|"
End Sub

NOTE: Change the red highlighted example path/filename that I used to the actual path/filename for your text file.

 

[DocAElstein]

Hi,

. Almost the identical question was asked in this thread a couple of days ago:
http://www.mrexcel.com/forum/excel-questions/795675-run-macro-txt-file.html?#post3917591
. If you take the codes or the supplied File with macros in it in my reply in #7 of that Thread, then apply that to your data, you will get this in the Excel file:

Book1
	A	B	C	D	E
1		xxxx	jjjjj	kkkk
2		yy	llll	mmm
3
MacroTextToExcel

(It is offset one column from what you want as you unusually have a separator at the beginning, but the codes can easily be modified to exactly what you want.)

. If you need any more help get back and one of us will help


Alan Elston.