Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
backup file
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
backup file
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" 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
backup file
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 "Patrick Molloy" wrote: 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" 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
backup file
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Everytime i close an excel file, it creates a new backup file | Excel Discussion (Misc queries) | |||
Saving backup file on file open | Excel Programming | |||
name a backup file | Excel Programming | |||
Backup file | Excel Discussion (Misc queries) | |||
backup file | Excel Programming |