Text File

''TextStream Object
'
'Description
'
'Facilitates sequential access to file.
'
'Syntax
'
'TextStream.{property | method}
'
'The property and method arguments can be any of the properties and methods associated with the TextStream object. Note that in actual usage TextStream is replaced by a variable placeholder representing the TextStream object returned from the FileSystemObject.
'

Option Explicit
'Create Text file on desktop and write few lines and close it.
Sub CreatingNewTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream

Set fso = New Scripting.FileSystemObject
Set txt = fso.CreateTextFile(Environ("userprofile") & "\desktop\test1.txt")

    txt.Write "Created on: " & Now & vbNewLine
    txt.WriteLine "Created by: " & Environ("username")
    txt.WriteBlankLines (2)
    txt.Write ("Data starts from here:")
     txt.WriteBlankLines (2)
txt.Close

Set fso = Nothing

End Sub
'Append a text file and write from the excel file
Sub AddDataToaTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForAppending, False)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write vbTab
    Next counter
   
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub

'Append a CSV file and write from the excel file
Sub AddDataToaCSVFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.csv", ForAppending, True)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write ","
    Next counter
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub
'Reading from text file and pasting it on excel
Sub ReadfromTextFile()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim tabposition As Integer
Dim textline As String

Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)
'txt.SkipLine

Worksheets.Add

Do Until txt.ReadLine = "Data starts from here:"
Loop

Do Until txt.AtEndOfStream

     textline = txt.ReadLine
     tabposition = InStr(textline, vbTab)
   
     Do Until tabposition = 0
        ActiveCell.value = Left(textline, tabposition - 1)
        ActiveCell.Offset(0, 1).Select
        textline = Right(textline, Len(textline) - tabposition)
        tabposition = InStr(textline, vbTab)
     Loop
   
    ActiveCell.value = textline
    ActiveCell.Offset(1, 0).End(xlToLeft).Select
   
Loop

txt.Close
Set fso = Nothing

End Sub

'Reading from text file and pasting on excel via text to column
Sub ReadfromTextFileEasierMethod()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream


Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)

Worksheets.Add
Do Until txt.AtEndOfStream

   
        ActiveCell.value = txt.ReadLine
       
        ActiveCell.Offset(1, 0).Select
   
Loop
Range("A:A").TextToColumns Tab:=True
txt.Close
Set fso = Nothing

End Sub
'Easiest method to open text file in excel
Sub ReadfromTextFileEasiestMethod()

Workbooks.OpenText filename:=Environ("userprofile") & "\desktop\test1.txt", Tab:=True

End Sub