The problem you have writing to the text file is that the write buffer
hasn't actually been written to the disk file. It is cached in memory
until the internal IO routines decide it is time to write to disk or
the until the file handle is explicitly closed. This is done to
increase IO efficiency.

You can use the Scripting.TextStream object to get around this. First,
in VBA, go to the Tools menu, choose References, and put check next to
"Microsoft Scripting RunTime". Then, use code like

Sub Test()
Dim FSO As Scripting.FileSystemObject
Dim TStream As Scripting.TextStream

Set FSO = New Scripting.FileSystemObject
Set TStream = FSO.CreateTextFile( _
Filename:="C:\A.txt", overwrite:=True, unicode:=False)
TStream.WriteLine "Some Text"
'
' Use TaskMgr to kill the Excel.exe process before the
' next line of code executes.
TStream.WriteLine "next line"
TStream.Close
End Sub

Step through the code line by line using F8 and when then line

TStream.WriteLine "next line"

is highlighted and ready to be executed, use TaskMgr to kill the
Excel.exe process (don't just Exit Excel). You'll see that the file
"C:\A.txt" contain the line "Some Text". The text got written to the
disk file even if thought the file wasn't properly closed.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 24 Aug 2009 08:44:02 -0700, David Gerstman
wrote:

I created a macro for capturing data and recording it.

The person who asked for this macro also wanted me to keep a back up file in
case of a power loss or carelessness on part of the operator.

I wrote code for creating a parallel text file. The problem is that when I
use the Task Manager to shut down Excel (to mimic the effect of a power
outage) the text file is created but no data is recorded in it. I've created
text files both for output and for append and neither one writes to the file.
Is there a way to record the data? Or am I better off just saving the
spreadsheet after each new entry?