Need help on converting a batch of txt to excel using VBA

[kingh]

I was trying to batch convert text files to excel using VBA and found this code in this web. This is how my text looks like:
ABC|123|DEF
ZZZ|233|YTU
HHH|789|HJK


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub LoopAllFiles()
Dim sPath As String, sDir As String
sPath = "C:\work"
If Right(sPath, 1) <> "" Then sPath = sPath & ""
sDir = Dir$(sPath & "*.txt", vbNormal)
Do Until Len(sDir) = 0
Workbooks.Open (sPath & sDir)
With ActiveWorkbook
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
.SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.Close
End With
sDir = Dir$
Loop
End Sub


</code>After using the code, the exported excel looks like this:
ABC 123 DEF (3 separated cell)
ZZZ|233|YTU (1 single cell)
HHH|789|HJK(1 single cell)
Anyone have idea why the code only convert the first row and stop afterwards?
Thanks a lot.

 

[Fluff]

Hi & welcome to MrExcel.
At a guess only cell A1 was select when you ran the code.
Try changing Selection to Range("A:A")

 

[kingh]

Hi & welcome to MrExcel.
At a guess only cell A1 was select when you ran the code.
Try changing Selection to Range("A:A")

Hi I tried changing the Range to A:A and the result is still the same

 

[John_w]

Importing each text file using a QueryTable (Data tab -> From Text) is better than the Workbooks.Open and TextToColumns method. For an example see https://www.mrexcel.com/forum/excel...nto-excel-2010-a-post4662237.html#post4662237

Record a macro whilst doing a Data -> From Text import of one of your text files and incorporate the generated code into the With .QueryTables.Add .... End With part of the linked code.