very so often I work on a project that involves extracting data from excel where it’s easier just to code the extraction up as a macro. For these projects, these two functions serve me well by enabling my macros to write text to an output file.

First, add a reference in Microsoft Visual Basic for Applications to Microsoft Scripting Runtime (c:\windows\syswow64\scrrun.dll)

This function can then be used to write text out to a file. It will append the text to the end of the file so the function can be used to write out text line by line.

Sub WriteToFile(sfilename As String, stext As String)
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

    Dim stream As TextStream
    Set stream = fso.OpenTextFile(sfilename, ForAppending, True)

    stream.WriteLine stext

    stream.Close
End Sub
    

If you would like to start out with an empty file each time, this function can be used to clear out the contents of an already existing file.

Sub ClearFile(sfilename)
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

    Dim stream As TextStream
    Set stream = fso.OpenTextFile(sfilename, ForWriting, True)

    stream.WriteLine ""

    stream.Close
End Sub