backup file

[David Gerstman]

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?

 

[Patrick Molloy]

My preference would be as you do, to dump the data to a text file so that it
can be retrieved later, This should be faster than saving Excel tiself.

Show us your code.

 

[David Gerstman]

Patrick,

Here's the code that creates the backup file, date2string creates a time
stamp in the form of 082409_1030 so that the time that macro started running
is recorded.

Rem
Rem Creating back up text file for emergencies
Rem

fino = FreeFile

Dim bu As String

Call date2string(bu)

Open "backup_" & bu & ".txt" For Output As #fino


Here's the code that actually writes the data:

Rem
Rem writing backup data to a text file in case there's a mistake made
Rem

Dim textline As String
textline = ""
textline = EntryNo & ";" & lblTime.Caption & ";" & cbSource.Text & ";" &
cbDirection.Text & ";" & tbComments.Text

Write #fino, textline

 

[Chip Pearson]

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)