Batch convert .txt files to .xls

Hi all,

I have 48 text files that need converting to Excel.
The obvious answer found in most forums uses the following code:

Sub TXTconvertXLS()

Dim wb As Workbook
Dim strFile As String
Dim strDir As String

'Directories
strDir = "\\xx\xx\xx\xx\Desktop\SignalLogs\"
strFile = Dir(strDir & "*.txt")

'Loop
Do While strFile <> ""
    Set wb = Workbooks.Open(strDir & strFile)
        With wb
            .SaveAs Replace(wb.FullName, ".txt", ".xls"), 50
            .Close True
        End With
    Set wb = Nothing
Loop

End Sub

The problem is this converts the text from the files in a single column of each excel workbook as opposed to distributed into columns.
Excel's Text Wizard sorts this problem for manual conversions, so using the macro recorder, I've identified a code for manual, correct conversion as :

Sub Macro1()

    ChDir "C:\XXXX\XXXX\Desktop\SignalLogs"
    Workbooks.OpenText Filename:= _
        "C:\XXXX\XXXX\Desktop\SignalLogs\WR2022092708M37.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, _
        1), Array(17, 1), Array(21, 1), Array(29, 1), Array(40, 1), Array(44, 1), Array(52, 1), _
        Array(57, 1)), TrailingMinusNumbers:=True
End Sub

Can someone please suggest a code which combines the two and allows me to convert all 48 .txt files into correctly formatted Excel files, please?

Thanks!!

Untested here :

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

https://stackoverflow.com/questions/...umns-using-vba

Thanks, but this still has the same problem. The excel files import all the .txt data into the 1st column of the excel files..
Attached is an example of the .txt files I'm working with

Here is a similar macro to yours that will distribute the data to different columns.
I agree with Marc L, you already have a macro to do what you are seeking ... why not use it ?

Sub DistributeData()

    Range("A1").Select
    ActiveSheet.Paste
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        OtherChar:="|", FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(17, 1), Array(21 _
        , 1), Array(30, 1), Array(41, 1), Array(44, 1), Array(53, 1), Array(58, 1)), _
        TrailingMinusNumbers:=True
End Sub