Two ways to write to text file using Excel VBA: Microsoft Standard Library vs Microsoft Scripting Runtime

Question

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

Your first way is standard Microsoft Basic - no need to reference any libraries. You should use Close #1, since it's the #1 channel you're using. — grahamj42, Mar 24 '18 at 23:10
Thank you, I made the required changes. So, do you think, if I intend to write heavily to txt files, are both ways equivalent, concerning speed and available resources? — sergio trajano, Mar 24 '18 at 23:20
The first way has the advantage of simplicity, so is probably faster, but you are stuck with the WIndows codepage (the reference syays ASCII, but it's probably windows-1252 for most of us). FileSystemObject.CreateTextFile has a Unicode option. I can't claim to have used it. — grahamj42, Mar 24 '18 at 23:31

paul bica · Accepted Answer · 2018-03-25T04:26:30.423

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)

Two ways to write to text file using Excel VBA: Microsoft Standard Library vs Microsoft Scripting Runtime

1 Answers1

Linked