Excel VBA to load the text file and convert to tab delimited file

[ExcelDev]

I am having a text file where I would like to convert it to tab delimited file by trimming the extra spaces if any.

Sample format in my file is as follows

0|04/07/1998|               |               |               |     |0|    |          | |N|

In an excel I would like to have a button where it should select the file to convert and once after selecting it should convert and save it to a new file.

 

[JEC]

This should be it then

Sub jec2()
 With Application.FileDialog(msoFileDialogFilePicker)
    If .Show Then
       c00 = .SelectedItems(1)
       c01 = Replace(Split(c00, "\")(UBound(Split(c00, "\"))), ".txt", "") & "new" & ".xlsx"
       With Workbooks.Add
          ar = Split(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, vbLf)
          With .Sheets(1).Cells(1, 1)
             .Resize(UBound(ar)) = ar
             .CurrentRegion.TextToColumns .Offset, xlDelimited, xlNone, True, , , , , True, "|"
          End With
         .SaveAs c01, 51
         .Close
       End With
    End If
 End With
End Sub

 

[ExcelDev]

Can you check the conversation I sent you the actual text

 

[JEC]

A small change, forgot to transpose the array

Sub jec2()
 With Application.FileDialog(msoFileDialogFilePicker)
    If .Show Then
       c00 = .SelectedItems(1)
       c01 = Replace(Split(c00, "\")(UBound(Split(c00, "\"))), ".txt", "") & "new" & ".xlsx"
       With Workbooks.Add
          ar = Split(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, vbLf)
          With .Sheets(1).Cells(1, 1)
             .Resize(UBound(ar) + 1) = Application.Transpose(ar)
             .CurrentRegion.TextToColumns .Offset, xlDelimited, xlNone, True, , , , , True, "|"
          End With
         .SaveAs c01, 51
         .Close
       End With
    End If
 End With
End Sub

 

[ExcelDev]

Thanks Jec if anything required I will create a new thread

 

[JEC]

You're welcome. This shows how important the way of asking is .

 

[ExcelDev]

Hi instead of creating a new excel file can I use the existing one please see my other thread here

Microsoft access database form with controls, read text file and convert to csv

This is an extension for this https://www.mrexcel.com/board/threads/excel-vba-to-load-the-text-file-and-convert-to-tab-delimited-file.1181840/ I have create two button and two text boxes as follows, when user clicks on browse input it should read the text file and after selecting it should...

www.mrexcel.com