Using Microsoft Scripting Runtime

 1999-12-17    File access    3    267

It is possible to use the Microsoft Scripting Runtime library to manipulate text files. Microsoft Scripting Runtime is included in these products: Windows98, Windows2000, IE5, and Office2000. The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Scripting Runtime.

Writing to text files

Sub WriteToTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream, i As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForWriting, True) ' ascii format
    ' Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForWriting, True, True) ' unicode format
    With f
        For i = 1 To 100
            .WriteLine "This is line number " & i
        Next i
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub

Adding to text files

Sub AppendToTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream, i As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForAppending, True) ' ascii format
    ' Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForAppending, True, True) ' unicode format
    With f
        For i = 1 To 100
            .WriteLine "Added line number " & i
        Next i
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub

Reading from text files

Sub ReadFromTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream, i As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForReading, False) ' ascii format
    ' Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", ForReading, False, True) ' unicode format 
    With f
        i = 0
        While Not .AtEndOfStream
            i = i + 1
            Cells(i, 5).Formula = .ReadLine
        Wend
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub