Skip to content

Instantly share code, notes, and snippets.

@jakelosh
Created September 23, 2013 15:08
Show Gist options
  • Save jakelosh/6671866 to your computer and use it in GitHub Desktop.
Save jakelosh/6671866 to your computer and use it in GitHub Desktop.
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…
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment