Every 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