I found there is two ways of creating and writing to a text file in Excel VBA: by using microsoft standard library or by using Microsoft Scripting Runtime. Are they equivalent? Below we have examples of both.
Sub WriteToTextFile_FirstWay()
Open "D:\Temp1.txt" For Output As #1
Print #1, "This text file was created using microsoft standard library."
Close #1
End Sub
Sub WriteToTextFile_SecondWay()
Dim FSO As New FileSystemObject
Set txtstream = FSO.CreateTextFile("D:\Temp2.txt", True, True)
txtstream.Write "This text file was created using Microsoft Scripting Runtime."
txtstream.Close
End Sub
Here are the results of 3 tests:
VBA_Lib - Size: 410 Mb, Time: 11.597 sec
FSO_Asc - Size: 371 Mb, Time: 20.241 sec
FSO_Uni - Size: 743 Mb, Time: 19.822 sec
The code
Option Explicit
Private Const ITERATIONS = 10000000
Private Const TEST_STRING = "This text file was created using "
Private fso As FileSystemObject 'Requires reference to Microsoft Scripting Runtime
Public Sub WriteToTextFile_FirstWay()
Const FILE_TYPE = "VBA_Lib"
Const FILE_NAME = "D:\" & FILE_TYPE & ".txt"
Const FILE_TEXT = TEST_STRING & FILE_TYPE & "."
Dim i As Long, t As Double
Set fso = New FileSystemObject
t = Timer
Open FILE_NAME For Output As #1 'Encode in UTF-8
For i = 1 To ITERATIONS
Print #1, FILE_TEXT
Next
Close #1
ShowResult FILE_TYPE, FILE_NAME, t
End Sub
Public Sub WriteToTextFile_SecondWay()
Set fso = New FileSystemObject
TestFSO False 'Encode in UTF-8
TestFSO True 'Encode in UCS-2 LE-BOM
End Sub
Private Sub TestFSO(Optional ByVal asUnicode As Boolean = False)
Const FILE_TYPE = "FSO"
Const FILE_NAME = "D:\" & FILE_TYPE
Const FILE_TEXT = TEST_STRING & FILE_TYPE & "."
Dim i As Long, t As Double, fsoFile As TextStream, fName As String, isASC As String
isASC = IIf(asUnicode, "Uni", "Asc")
fName = FILE_NAME & "_" & isASC & ".txt"
t = Timer
Set fsoFile = fso.CreateTextFile(fName, True, asUnicode)
For i = 1 To ITERATIONS
fsoFile.WriteLine FILE_TEXT
Next
fsoFile.Close
ShowResult FILE_TYPE & "_" & isASC, fName, t
End Sub
Private Sub ShowResult(ByVal fType As String, ByVal fName As String, ByVal t As Double)
Dim msg As String
msg = fType & " - Size: " & fso.GetFile(fName).Size \ 1048576 & " Mb"
Debug.Print msg & ", Time: " & Format(Timer - t, "0.000") & " sec"
End Sub
FileSystemObject Methods (MSDN)