Write txt first line instead of last

Question

It is easy to find in the internet a way of write into a txt file but all I find is always writing in the very last line:

Sub write_log(sentence_to_be_written As String)

   Dim strFile_Path As String
   strFile_Path = "C:\Users\[user_name]\Desktop\log.txt"
   Open strFile_Path For Append As #1
   Print #1, Now() & " --> " & sentence_to_be_written 
   Close #1

End Sub

I would like to write instead into the first line of the txt file.

I do not believe it is possible to prepend without having to read the whole file (eg into a variable) and then overwrite it with your new string at the front — Joerg Wood, Jul 06 '20 at 06:47
Maybe you should be writing to a database instead, so you can have many advanced options. — braX, Jul 06 '20 at 06:54

FaneDuru · Accepted Answer · 2020-07-06T07:36:10.403

Try the next code, please. It needs a reference to Microsoft Scripting Runtime. It can be adapted to work without such a reference. In fact, I will also post a pice of code able to automatically add the necessary reference... It is possible to read the text using standard VBA Open, but only concatenating line by line and I think this solution is more elegant:

Sub write_log_OnTop(sentence_to_be_written As String)
   'It neds a reference to 'Microsoft Script Runtime'
   Dim strFile_Path As String, strText As String
   Dim fso As New FileSystemObject, txtStr As TextStream
   strFile_Path = "C:\Users\Fane Branesti\OneDrive\Desktop\log.txt"

   If Dir(strFile_Path) <> "" Then 'check if file exists
     Set txtStr = fso.OpenTextFile(strFile_Path)
        strText = txtStr.ReadAll
     txtStr.Close
   Else
     MsgBox "Wrong file path...": Exit Sub
   End If
   strText = Now() & " --> " & sentence_to_be_written & vbCrLf & strText
   Open strFile_Path For Output As #1
      Print #1, strText
   Close #1
End Sub

And Microsoft Scripting Runtime reference can be automatically add by running of the next code:

Private Sub Add_Scripting_Reference() 'Adds 'Microsoft Scripting Runtime'
 Dim wb As Workbook, r As Reference
 Set wb = ThisWorkbook
 For Each r In wb.VBProject.References
    If r.name = "Scripting" Then Exit Sub
 Next
 wb.VBProject.References.AddFromFile Environ("windir") & "\system32\scrrun.dll"
End Sub

If you do not want the reference, even if I would not understand such a choice, it is enough to comment/replace the code line

Dim fso As New FileSystemObject, txtStr As TextStream

with:

Dim fso As Object, txtStr As Object: Set fso = CreateObject("Scripting.FileSystemObject")

It is exactly what I was looking for. Thank you very much. Unfortunally, as some other users said, this method is much more slower than the original, therefore I am not going to use it. Instead I will use a database and SQL queries. Thanks again — Joracosu, Jul 06 '20 at 09:36
@Joracosu: Glad I could help! But you did not say anything about the frequency of log writing and the maximum estimated side of the log file... Everything is relative, from this point of view. The above code can be considered as fast enough, but depending of what I said before. I chosen the reading at once solution, to make it as fast as possible. For instance, it adds a line in about half second for a txt file having 20000 rows (about 7 MB). 20 times faster then opening it (Notepad, Wordpad)... If the file size increases, the problem would be more connected with the System Memory use. — FaneDuru, Jul 06 '20 at 10:16
yes, I did not talked about the frequency because I didn't thought about it XD. The problem is not the size of the file. The problem is that this function is going to be called 2000 times per day :P Anyway your code is very good and verywell understanding. For sure I will use that method in a future for other purposes. Thank you very much!! — Joracosu, Jul 06 '20 at 10:31
I've calculated the time needed and with my small file it takes more than double 2. Is very good! but I can not have that delay 2000 times per day (200ms vs 550ms and it increase with the file size). I didn't thought about it at the begining. When I asked, I thougth the solution would be as fast as the initial method. — Joracosu, Jul 06 '20 at 10:39

score 1 · Answer 2 · answered Jul 06 '20 at 07:36

There is no command to add text at the top (or the middle) of any file. I never heard about such command in any programming language. It's about (disk-)space management, if you add a line of text in front of any other text, the existing text needs to be moved, and this is a rather complicated operation.

If you deal with short files, you could solve that by reading the content of the file into memory and then recreate the file by first writing the new line(s) and the add the content - as Joerg Wood suggested in the comments. However, this would need lot of memory and/or disk IO if the file gets larger, and the process has to be repeated every time you want to add a line - maybe not an issue if you write only one line per hour, but quite an issue if you are writing multiple lines per second.

It seems you are writing a log file and probably you want to see what was going on lately. You could use a windows version of the tail command (that comes from Unix) or use the powershell command Get-Content "C:\Users\[user_name]\Desktop\log.txt" -Tail 10 (see https://stackoverflow.com/a/188126/7599798) for that - it will display the last lines of a file.

An alternative could be to write the log into an Excel sheet or a database - in both cases it is easy to fetch the data in any order.

I understand what you say. Thankyou very much for your answer. I tryed the @FaneDuru solution and works fine but happens what you said: it is much slower. I will do it with a database instead a txt file, because I don't want to go to the end each time I want to see what hapened last. Thank you very much — Joracosu, Jul 06 '20 at 09:34

Write txt first line instead of last

2 Answers2