Last week in my company's process improvements group we discussed writing out log messages from spreadsheet applications to text files. While the thought came from somebody in the group, I'm fairly certain the original source of the code they shared came from here: http://msdn.microsoft.com/en-us/library/dd439413%28v=office.12%29.aspx#Office2007…

vbaWriteTextFile

Public Sub WriteTextFile(ByVal strMessage As String)

    Dim objFile As FileSystemObject
    Set objFile = New FileSystemObject
    
    Dim txtStream As TextStream
    
    Dim strPath As String
        strPath = ThisWorkbook.Path & "\"
        
    Dim strFile As String
        strFile = strPath & ThisWorkbook.Name & " User Log File.txt"
    
    'This is where the magic happens: The text file is opened (ForAppending means we're going to add to it, as opposed to overwriting it)
    On Error Resume Next
    Set txtStream = objFile.OpenTextFile(strFile, ForAppending)
    'Error #53 is the 'File Not Found' error. If the text file isn't found, we create it.
    If Err.Number = 53 Then Set txtStream = objFile.CreateTextFile(strFile, True)
    
    txtStream.WriteLine strMessage
    txtStream.Close
    
End Sub