Word document doing a disappearing act

[EdwardSF]

Beth Melton suggested that I bring my question here from the Word Application
Errors board, as it involves some VBA programming.

I work in a transcription office, with several stand-alone computers hooked
through a router to a server. After typing a report, we run a macro I wrote
to add a line to a table in a document on the server called UCSFLOG.doc.

The problem is, every now and then (but with increasing frequency over the
past couple of weeks) the log file just disappears, without a trace. Not in
the Recycle Bin, not anywhere on the server or any of the computers in the
office. Poof.

The macro in question contains a loop where, if there's an error opening the
log file it'll display a MsgBox, then go back and try again. This was put in
place when the boss would open the log file to look at and leave it up on her
computer; now it's meant to catch the event where the log disappears
completely.

However, when the log file does its disappearing act, the MsgBox never comes
up; instead, Word displays its own error message, asking to reconnect to the
network (the connection is still up at this point) or insert a floppy disk.
This message is caught in an infinite loop, so you have to CTRL-ALT-DEL and
restart Word. When restarting Word, it can't recover the log file, even
though there's a copy of it in a TMP file.

So, it *looks* like the macro is opening the log file without a problem;
then when it goes to save it again it creates a TMP file, removes the
original, and then freaks out when it can't find it on the drive. Beth
suggested that this could happen when more than one person is running the
macro, but A) the error-handling loop ought to catch that, and 2) it's
happened when nobody else is even working in Word.

I can't see how anything I wrote in the macro could cause this, but
otherwise I'm stumped. I confess to being just a gifted amateur with VBA, so
feel free to rip me for doing something heinously wrong. The pertinent code
is below. Thanks.

CODE:

'
' Some data-parsing stuff here

' Then it saves the current document for sending to the client. (They want
the file names a certain way.)
ChangeFileOpenDirectory "F:\CL\UCSFSEND\"
varFileName = "7" + varDocType + varFacNum + Right$(varJobID, 4) + "." +
varTransID
ActiveDocument.SaveAs FileName:=varFileName + ".doc"

' Next an entry is made into a table in a document called UCSFLOG.doc.
This is the Amazing Disappearing File.
‘ Sometimes the boss opens the file to get statistics, and leaves the file
open. Then if
‘ someone runs the macro, they get an error message that the file is
already open. This
‘ next block of code originally meant to deal with that, so the user could
go tell the boss
‘ to close the file then try again, without the macro bombing out. Later,
when the
‘ UCSFLOG file started disappearing entirely, the MsgBox message was more
strongly
‘ worded, but the loop was left in place.

ChangeFileOpenDirectory "F:\CL\UCSF"
TryOpen:
On Error Resume Next
Documents.Open FileName:="UCSFLOG.doc"
varError = Err.Number
If varError <> 0 Then
MsgBox ("UCSFLOG cannot be found. Something has gone seriously
wrong. Do not panic.")
GoTo TryOpen
End If

‘ Once the UCSFLOG file has been closed elsewhere (and assuming that it’s
still on the server at this point), a line is added to the table.

‘ Now, we save the UCSFLOG file, as well as (recently added) a backup copy
on an
‘ entirely separate drive. It looks like the Word error message comes up
at this point.

ActiveDocument.SaveAs
ChangeFileOpenDirectory "Z:\"
ActiveDocument.SaveAs ("UCSFLOGbackup.doc")
ChangeFileOpenDirectory "F:\CL\UCSF"
ActiveDocument.Close
' Close the report as well
ActiveDocument.Close Savechanges:=wdSaveChanges

End Sub

 

[Astrid]

Hi Edward,

Maybe try the dir command to see if the file really exists:

for example:
Dir("C:\Autoexec.bat")
wil return autoexec.bat if the file really exists. If it doesn't exitst, it
will return an empty string

Dim sFileExists as String

sFileExists = Dir("F:\CL\UCSF\UCSFLOG.doc") Then
If Len(trim(sFileExists) = 0 then
'No file was found
End If

Some other tips to shorten your code which should make it easier to maintain
and read. You don't need to change the FileOpen directory in order to save a
file in another directory, just use:
ActiveDocument.SaveAs FileName:="Z:\UCSFLOGbackup.doc"

I haven't try to run your code, but have questions with you closing the doc
twice. If you save a document with another name, only one document is
opened, and not two.
Just try something like:

Sub MyRoutine()
Dim oDoc As Document
Dim sFileExists As String

sFileExists = Dir("F:\CL\UCSFUCSFLOG.doc")
If Len(Trim(sFileExists)) = 0 Then
'File doesn't exist
MsgBox "File does not exists"
'Stop running code
Exit Sub
End If

Set oDoc = Documents.Open(FileName:="F:\CL\UCSFUCSFLOG.doc")
'Do stuff
'Save the document, no need to specify the name, if no changes to name or
path are needed
oDoc.Save
'Save a backup copy
oDoc.SaveAs FileName:="Z:\UCSFLOGbackup.doc"
'Close the file
oDoc.Close savechanges:=wdDoNotSaveChanges

'Clean up
Set oDoc = Nothing
End Sub

If your code handles multiple documents however, it might be a better idea
to work with objectvariables that contain a document, instead of using
multiple activedocument which might point to different documents. This could
be a potential source of trouble.

For example:

Sub MultipleDocs()
Dim oOrgDoc As Document
Dim oBackupDoc As Document

Set oOrgDoc = Documents.Open(FileName:="c:\test1.doc")
Set oBackupDoc = Documents.Open(FileName:="c:\test2.doc")

' do stuff

oOrgDoc.Close savechanges:=wdSaveChanges
oBackupDoc.Close savechanges:=wdSaveChanges

Set oOrgDoc = Nothing
set oBackupdoc = nothing
End Sub



Hope this helps,
kind regards,
Astrid