Append Line in text file

smpatty08

Board Regular
Joined
May 16, 2014
Messages
155
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.

Code:
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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,298
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

Board Regular
Joined
May 16, 2014
Messages
155
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

Board Regular
Joined
May 16, 2014
Messages
155
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.
Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,298
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

Board Regular
Joined
May 16, 2014
Messages
155
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,652
Messages
5,524,101
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top