Import TXT files VBA please help

[roberttkim]

Hey everyone,

I have a code that works great for importing multiple txt files and copying into one sheet in excel but I have a problem was hoping one of you experts can help with. One of the txt files we have sometimes gets copied into one cell. I'm thinking it's a delimiting issue on the original file? Every other file gets copied fine though. Would really appreciate the help.

Sub test()
    Dim myDir As String, fn As String, txt As String, x
    myDir = "\\04vfile002\TREASOPS\Fixed Income and Treasury Operations\FedMail\09-2017" '<- change to actual folder path
    fn = Dir(myDir & "\*.txt")
    Do While fn <> ""
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & "" & fn).ReadAll
        x = Application.Transpose(Split(txt, vbCrLf))
        Sheets(1).Range("a" & Rows.Count).End(xlUp)(2).Resize(UBound(x, 1)).Value = x
        fn = Dir()
    Loop
End Sub

 

[Domenic]

Maybe because I forgot to follow it up by changing the red highlighted vbCrLf to vbLf in the next line.

That's great. I was wondering why it didn't work. I missed it completely.