Append Line in text file

[smpatty08]

I am trying to Append a text file with the date, time, user info, and time a macro took to run in order to generate data about a lengthy piece of code. Here is the code I have so far, but it just overwrites the original entry.

Sub CalculateRunTime_Seconds()
Dim StartTime, SecondsElapsed As Double, fso, oFile As Object, strFile_Path As String
Set fso = CreateObject("Scripting.FileSystemObject")
WinUser = Environ("USERNAME"): Debug.Print "WinUser = " & WinUser
Set oFile = fso.CreateTextFile("H:\Beta 1,3 Logs.txt")
strFile = "H:\Beta 1,3 Logs.txt"
'Remember time when macro starts
  StartTime = Timer


'*****************************
'Insert Your Code Here...
'Call "ModuleName"."Procedure Name"
Call FillDownList.FillDownList
'*****************************


'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)
  
  Set fso = Nothing
  Set oFile = Nothing
  Open strFile For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "Time: " & Now & vbNewLine & " User: " & WinUser & vbNewLine & "Raw Data Import took " & SecondsElapsed & " seconds to complete."
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  Debug.Print "This code ran successfully in " & SecondsElapsed & " seconds"
End Sub

There has to be an easy way to make this work correctly. Any help would be appreciated. Thanks!

 

[Domenic]

Your Open statement is correct. It should append any data written to the file specified by strFile. Try setting a breakpoint at the line containing your Open statement. Now run your code again. When it stops at that breakpoint, place your cursor over strFile and look at the path and filename assigned to it. Now run it again. When it stops at that breakpoint again, look at the path and filename assigned to it this time. Is the same?

 

[smpatty08]

Your Open statement is correct. It should append any data written to the file specified by strFile. Try setting a breakpoint at the line containing your Open statement. Now run your code again. When it stops at that breakpoint, place your cursor over strFile and look at the path and filename assigned to it. Now run it again. When it stops at that breakpoint again, look at the path and filename assigned to it this time. Is the same?

Yes the file path is the same. "H:\Beta 1,3 Logs.txt"

 

[smpatty08]

I figured it out. So my original code created a new text file every time and would overwrite the original, which is why it would never append. I created the following code to correct that issue.

Sub CalculateRunTime_Seconds()
On Error GoTo Errhandler
3   Dim StartTime, SecondsElapsed As Double, strFile_Path As String
4   Call CreateTextFile
5   WinUser = Environ("USERNAME"): Debug.Print "WinUser = " & WinUser
6   strFile = "H:\Beta 1,3 Logs.txt"
'Remember time when macro starts
8     StartTime = Timer


'*****************************
'Insert Your Code Here...
'Call "ModuleName"."Procedure Name"
13  MsgBox "blah"
'*****************************


'Determine how many seconds code took to run
17    SecondsElapsed = Round(Timer - StartTime, 2)
18    Open strFile For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
19    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "Time: " & Now & vbNewLine & " User: " & WinUser & vbNewLine & "Raw Data Import took " & SecondsElapsed & " seconds to complete."
20    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
21    Debug.Print "This code ran successfully in " & SecondsElapsed & " seconds"
Done:
23  Exit Sub
Errhandler:
25  Debug.Print "Line: " & Erl & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description
End Sub


Sub CreateTextFile()
Dim fso, oFile As Object, FilePath As String
3   If Dir("H:\Beta 1,3 Logs.txt") = "" Then
4       Set fso = CreateObject("Scripting.FileSystemObject")
5       Set oFile = fso.CreateTextFile("H:\Beta 1,3 Logs.txt")
6       Set fso = Nothing
7       Set oFile = Nothing
8   Else
9   End If
End Sub

 

[Domenic]

Sorry, I missed the fact that you're using the FileSystemObject to create your text file. But with Append, if the file doesn't already exist, it will automatically be created. So there's no need to use the FileSystemObject.

 

[smpatty08]

Sorry, I missed the fact that you're using the FileSystemObject to create your text file. But with Append, if the file doesn't already exist, it will automatically be created. So there's no need to use the FileSystemObject.

Yes, so this also works. Thanks for your help

Sub CalculateRunTime_Seconds()
On Error GoTo Errhandler
3   Dim StartTime, SecondsElapsed As Double, strFile_Path As String
4   WinUser = Environ("USERNAME"): Debug.Print "WinUser = " & WinUser
5   strFile = "C:\Users\" & WinUser & "\Documents\Beta 1,3 Logs.txt"
'Remember time when macro starts
7     StartTime = Timer


'*****************************
'Insert Your Code Here...
'Call "ModuleName"."Procedure Name"
12  Call Sheet4.EnzymeImportRawData
'*****************************


'Determine how many seconds code took to run
16    SecondsElapsed = Round(Timer - StartTime, 2)
17    Open strFile For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
18    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "Time: " & Now & vbNewLine & " User: " & WinUser & vbNewLine & "Raw Data Import took " & SecondsElapsed & _
      " seconds to complete." & vbNewLine
20    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
21    Debug.Print "This code ran successfully in " & SecondsElapsed & " seconds"
Done:
23  Exit Sub
Errhandler:
25  Debug.Print "Line: " & Erl & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description
26  Open strFile For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
27  Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "Time: " & Now & vbNewLine & " User: " & WinUser & vbNewLine & "ERROR" & vbNewLine & "Error on Line: " & Erl _
    & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description & vbNewLine
29  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub