Code for Converting text file to CSV File

The following code if for converting the text file to CSV (Comma Separated Values) format.

The following code can run under Visual Basic or VBA. The prerequisite is to add a reference of “Microsoft Scripting Runtime”

Public Sub ExportToCsv()
On Error GoTo Err_v
Dim x
Dim MyLineData As String
Dim MyNewLineData As String
Dim OneChar
Dim SourceFile, TargetFile
Dim Pos As Long
Dim StartPos As Long
Dim EndPos As Long
Dim fs As New FileSystemObject
Dim tsSource As TextStream
Dim tsTraget As TextStream


SourceFile = "C:\" & & ".txt"

TargetFile = "C:\" & & ".csv"

If TargetFile <> "" Then
Set tsSource = fs.OpenTextFile(SourceFile, ForReading)
fs.CreateTextFile TargetFile, True
Set tsTraget = fs.OpenTextFile(TargetFile, ForWriting)
Do While Not tsSource.AtEndOfStream
MyLineData = tsSource.ReadLine
Pos = 0
Pos = InStr(1, MyLineData, ",", vbTextCompare)
If Pos > 0 Then
StartPos = InStrRev(MyLineData, Chr(9), Pos, vbTextCompare)
EndPos = InStr(Pos, MyLineData, Chr(9), vbTextCompare)
End If

For x = 1 To Len(MyLineData)
OneChar = Mid(MyLineData, x, 1)
If Pos > 0 Then
If x = StartPos And OneChar = Chr(9) Then OneChar = "," & Chr(34)
If x = EndPos And OneChar = Chr(9) Then
OneChar = Chr(34) & ","
Pos = InStr(EndPos, MyLineData, ",", vbTextCompare)
If Pos > 0 Then
StartPos = InStrRev(MyLineData, Chr(9), Pos, vbTextCompare)
EndPos = InStr(Pos, MyLineData, Chr(9), vbTextCompare)
End If
End If
End If
If Asc(OneChar) = 9 Then OneChar = ","
MyNewLineData = MyNewLineData & OneChar
Next x

tsTraget.WriteLine MyNewLineData
MyNewLineData = ""
Loop

tsSource.Close
tsTraget.Close


End If

MsgBox "Completed"

Exit Sub
Err_v:

tsSource.Close
tsTraget.Close

End Sub