title: VBA Convert delimiter text file to Excel
body: <p>I have a code that convert files in folder from .txt (with "|" separator) to xslx, 
  But the code works fine for some files (when I open it in excel), the others are wrong, when I try to import the wring one manually by excel ribbon(Get external data -- > from text), the files are correct.</p>
  
  <p>This is my code :</p>
  
  <pre><code>Sub tgr()
  
      Const txtFldrPath As String = "C:\...\txtFiles"     
      Const xlsFldrPath As String = "C:\excelFiles"     
  
      Dim CurrentFile As String: CurrentFile = Dir(txtFldrPath &amp; "\" &amp; "*.txt")
      Dim strLine() As String
      Dim LineIndex As Long
  
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      While CurrentFile &lt;&gt; vbNullString
          LineIndex = 0
          Close #1
          Open txtFldrPath &amp; "\" &amp; CurrentFile For Input As #1
          While Not EOF(1)
              LineIndex = LineIndex + 1
              ReDim Preserve strLine(1 To LineIndex)
              Line Input #1, strLine(LineIndex)
          Wend
          Close #1
  
          With ActiveSheet.Range("A1").Resize(LineIndex, 1)
              .Value = WorksheetFunction.Transpose(strLine)
              .TextToColumns Other:=True, OtherChar:="|"
          End With
  
          ActiveSheet.UsedRange.EntireColumn.AutoFit
          ActiveSheet.Copy
          ActiveWorkbook.SaveAs xlsFldrPath &amp; "\" &amp; Replace(CurrentFile, ".txt", ".xlsx"), xlOpenXMLWorkbook
          ActiveWorkbook.Close False
          ActiveSheet.UsedRange.ClearContents
  
          CurrentFile = Dir
      Wend
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/EwaBz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EwaBz.png" alt="enter image description here"></a>
  this picture show the result in excel and the original file .txt</p>