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]

Do you want to overwrite the old textfile?

Sub jec()
 With Application.FileDialog(msoFileDialogFilePicker)
    If .Show Then
       c00 = .SelectedItems(1)
       c01 = Replace(Split(c00, "\")(UBound(Split(c00, "\"))), ".txt", "") & "new" & ".txt"
       With CreateObject("scripting.filesystemobject")
          .createtextfile(c01).write Application.Trim(.opentextfile(c00).readall)
       End With
    End If
 End With
End Sub

 

[ExcelDev]

I want to save it an excel, can I get the excel with VBA code?

 

[JEC]

Do you want the text in 1 cell?

 

[ExcelDev]

In different different cell, what I am trying to do is I have an excel where a button is placed, when he click on it will prompt to select a text file. The selected text file should convert to a tab delimited file.

 

[JEC]

To me the desired output is still not clear. You could give this a try

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 = Filter(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, " ", "@"), "|"), "@", False)
         .Sheets(1).Cells(1, 1).Resize(, UBound(ar)) = ar
         .SaveAs c01, 51
         .Close
       End With
    End If
 End With
End Sub

 

[ExcelDev]

Thanks if I have data in another line will it store in to a new row like

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

 

[ExcelDev]

Also if possible keep the empty text as is and if there are any space after text I would like to trim. Like

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

 

[JEC]

What about the straight dashes

 

[ExcelDev]

For now remove and place it in different cell with white spaces if it is empty