Write to a text file

[j.smith1981]

How do I get VBA in Excel to detect if a txt file exists.

Then if it doesnt create one called timer.txt

Write the start of the macro in a time format like so:

Started: 05/10/2009 11.30

Then at the end, open that txt file put a new line and put the finishing time again like so:

Finished: 05/10/2009 11.45 (whatever time the macro finishes)

Then allow it to add another below that leaving a new blank line below each time it is run, how is this possible please?

Thanks in advance again.

[GTO]

Greetings J,

I think you could use FSO (File System Object) for this, with OpenTextFile. Details in vba Help, but in short, the third arg being TRUE will create a file if none exists, or, just open if its there...

In a Standard Module:

Option Explicit

Sub exa()
Dim FSO As Object '<--- FileSystemObject
Dim TFile As Object '<--- TextStream
Dim strPath As String
Dim strTFileName As String

'// Change to suit //
strPath = ThisWorkbook.Path & Application.PathSeparator
strTFileName = "Timer.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set TFile = FSO.OpenTextFile(strPath & strTFileName, 8, True, -2)

With TFile
.WriteBlankLines 2
.WriteLine "Started at: " & Format(Now(), "mm/dd/yyyy hh.mm")

MsgBox "Your other code here. Get a cup of coffee, relax, and " & vbCrLf & _
"click the <OK> button in a couple of minutes...", 0, ""

.WriteLine "Ended at: " & Format(Now(), "mm/dd/yyyy hh.mm")
.Close
End With
End Sub


Hope that helps,

Mark