Excel renaming file to a temp and deletes original (long)
 

We have a journal entry log spreadsheet that users go in
to log their transactions. The logging process is done
automatically through VBA. It usually opens the workbook,
logs the entry, saves, and closes the workbook pretty fast
(2 secs). Most of the time it isn't a problem. A problem
will occur if a user accesses the log while it is being
saved by another user. This will create a temp file and
delete the original file. It's probably not related to
virus software or permission. It's probably caused by the
interruption during the saving process. You would think
that throughout the day users wouldn't run into each other
with the 2 secs it takes to open and close the log.
However, it does happen. I am wondering if I can lock the
file until a user is finish with it. I did code some
logic to it that if the file is read-only then close the
file, but apparently that still interrupts the process.
Must be the initial opening of the file that messes things
up. I may have to test if the file is read-only using
another way instead of the workbooks.open property. The
only other option I see is to use an Access database to
log the entries. Any help will be appreciated. Thanks...

John

Excel renaming file to a temp and deletes original (long)
 

John,

I use plain text files as logs. If that's possible for you, then check
the 'Open statement' in VBE help. Look at the 'lock' parameter.

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------


We have a journal entry log spreadsheet that users go in
to log their transactions. The logging process is done
automatically through VBA. It usually opens the workbook,
logs the entry, saves, and closes the workbook pretty fast
(2 secs). Most of the time it isn't a problem. A problem
will occur if a user accesses the log while it is being
saved by another user. This will create a temp file and
delete the original file. It's probably not related to
virus software or permission. It's probably caused by the
interruption during the saving process. You would think
that throughout the day users wouldn't run into each other
with the 2 secs it takes to open and close the log.
However, it does happen. I am wondering if I can lock the
file until a user is finish with it. I did code some
logic to it that if the file is read-only then close the
file, but apparently that still interrupts the process.
Must be the initial opening of the file that messes things
up. I may have to test if the file is read-only using
another way instead of the workbooks.open property. The
only other option I see is to use an Access database to
log the entries. Any help will be appreciated. Thanks...

John