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