Problem deleting old Word file after save as to new file name

[Bouncinalong]

I have a macro that saves a Word form as another filename (SaveAs) in another
folder on a network drive and I use the filesystemobject to delete the old
file from the original folder after the "save as" is complete. It works fine
on some PC's but others won't let the file be deleted. It appears that Word
still has a handle to the original file after the save as is complete which
won't let me delete the old file (I also can't delete them manually using
Windows Explorer until I close the instance of Word). Shouldn't Word let go
of the original file after a "save as" is completed? Is this a registry
issue?

Thanks for any help!

 

[Peter T. Daniels]

That seems like the behavior most people would want -- usually when
you Save As, it's because you want two copies of the file with
different names, so it protects you from accidentally deleting the
original.

 

[Gordon Bentley-Mix on news.microsoft.com]

Some versions of Word do indeed maintain pointers to files - handles, as you
call them - even after the file is closed, and these pointers are only
released when Word is shut down completely. (This is one of the reasons why
many experienced Word users recommend against editing documents on removable
media, such as flash drives; these unreleased pointers can cause file
corruption when the removable media is... well... removed.) The "strength" of
these pointers seems to vary between versions, with newer versions
maintaining a stronger grip than older versions.

Can you spot any differences between versions of Word and/or operating
system on the computers that don't allow the file to be deleted vs those that
do? In addition, can you provide more detail (in the form of a code snippet
preferrably) on how your macro deletes the old file?
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix on news.microsoft.com]

But clearly this *isn't* the behaviour that the OP wants, or he/she wouldn't
have a macro designed to do otherwise.

Peter, your post is in no way helpful. When will you learn that the way you
prefer to work isn't necessarily the way others do? It's blatantly obvious
that the OP is not "most people" - and more importantly, not you. The
implicit message in your post is that the OP is doing something "wrong" and
shouldn't be trying to solve this problem because doing it the "right" way
(read: your way) would avoid the problem entirely. This is incorrect. The OP
is doing nothing "wrong"; he/she simply wants to do work in a way that meets
his/her needs and is looking for help with overcoming an impediment to this
process.

Pull your head in and stop providing "non-answers" to questions outside of
your very limited area of expertise.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Bouncinalong]

Gordon,

Thanks for the response! I am running into this issue with Word 2003 and
2007 on Microsoft Xp. The wierd part is that it happens on some machines but
not others. My machine has no problems but others do (not all others). I
use the following code to do the file manipulation:

' Save the document
' This is necessary because the network may not release the file handle
' which will cause problems when we try to delete the file from
' the In Process folder
ThisDocument.Save
' Save the doc in the appropriate folder (Complete)
' NOTE: The file will be saved in the completed folder by Cust. Name, RMA
' Number, Part Number, and Serial Number
ThisDocument.SaveAs (tmpFilePath & ThisDocument.FormFields("Text15").Result
& "_" & ThisDocument.FormFields("Text8").Result & "_" &
ThisDocument.FormFields("Text7").Result & "_" &
ThisDocument.FormFields("Text9").Result)
' See if the form is in the "In Process" folder and delete it
' Because the file may not be in the folder (the user finished it out the
first time)
' we need to check before trying to delete it
If objFileSystem.FileExists(tmpFilePath2 &
ThisDocument.FormFields("Text8").Result & "_" &
ThisDocument.FormFields("Text7").Result & "_" &
ThisDocument.FormFields("Text9").Result & "_" &
ThisDocument.FormFields("Text15").Result & ".doc") Then
' Delete the file
objFileSystem.DeleteFile (tmpFilePath2 &
ThisDocument.FormFields("Text8").Result & "_" &
ThisDocument.FormFields("Text7").Result & "_" &
ThisDocument.FormFields("Text9").Result & "_" &
ThisDocument.FormFields("Text15").Result & ".doc")

End If ' objFileSystem.FileExists(tmpFilePath2 &
ThisDocument.FormFields("Text8").Result & "_" &
ThisDocument.FormFields("Text7").Result & "_" &
ThisDocument.FormFields("Text9").Result & "_" &
ThisDocument.FormFields("Text15").Result & ".doc")

I realize that these lines of code are a bit long and hard to read due the
method in which I assemble the filename. Sorry 'bout that. I kludged in the
initial "Save" as an attempt to get Word to release the file pointer but it
doesn't help (I will take that line out on the next revision). I have also
tried skipping the "FileExists" check and going straight to the delete and it
still fails. We are currently performing a manual delete after ending the
macro in the cases where it fails.

I plan to do an experiment with Excel and others over the weekend which may
prove a systemic issue and not specific to Word. I will update this post
with the results.

Thanks so much!!!!

 

[Gordon Bentley-Mix on news.microsoft.com]

A couple of suggestions:

When you do your next version, you might want to look as using String
variables for the various filenames. It'll save you a lot of work around
trying to identify the file because you can just build the filenames once and
then use the variables throughout. This will also help you to avoid errors
(because I know how easy it is to miss something small like the exact name of
a FormField - and how bloody hard it can be to spot the error!) I'm not
positive, but I think it might possibly help with your original problem
because your code only needs to "query" the document at the start of code
execution, which might make it so the document is "released" sooner. Just a
SWAG but it might be worth investigating.

Second, using With statements makes your code A LOT easier to read - and not
just for others. They also save a lot of typing. For example, something like

objFileSystem.DeleteFile (tmpFilePath2 &
ThisDocument.FormFields("Text8").Result & "_" &
ThisDocument.FormFields("Text7").Result & "_" &
ThisDocument.FormFields("Text9").Result & "_" &
ThisDocument.FormFields("Text15").Result & ".doc")

becomes a lot simpler if you use a construction like

With ThisDocument
objFileSystem.DeleteFile (tmpFilePath2 &
.FormFields("Text8").Result & "_" &
.FormFields("Text7").Result & "_" &
.FormFields("Text9").Result & "_" &
.FormFields("Text15").Result & ".doc")
End With

But then your probably already knew all this and were planning to make these
changes in the next rev, and I'm just telling you how to suck eggs. ;-P

Now on to the problem at hand.

I can't see anything glaringly wrong with your code. It looks to be doing
everything it ought to be doing (and everything *you* ought to be doing as
well, like checking for the existence of the file before trying to delete it)
and doing it properly. The only suggestion I can think of is maybe trying a
'Kill' statement to delete the file instead of "objFileSystem.DeleteFile".
The VBA help covers this process well enough.

What I find interesting is that the problem doesn't seem to be related to
version of Word. If the problem was specific to only Word 2007 then it might
be easier to figure out - a timing thing or something. ~shrug~ Any other
differences that you can see? Works on slow computers but not on fast ones?
Works for users in some locations but not others? I'm groping a bit here, but
any sort of pattern might give us a lead. (And I'm asking about computer
speed and location because I'm wondering if maybe the code is running so
"quickly" that there's not enough time for the original document to be
released and for Word to do its clean up processes before the code tries to
delete it - in which case something as simple as introducing a 5 second delay
in execution might solve the problem... I've got code around somewhere for
doing just that if you'd like to try.)

Anyway, I'd be interested to see the results of your testing with Excel. As
you say, this might show a systemic problem rather than a Word problem.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Bouncinalong]

Thanks for the tips. I usually forget about the "with" option...

On the last bit, my PC is in the middle for speed, memory, etc. PC's that
are faster and slower fail (and some work fine). I thought about the delay
option and had one in there in a previous revision but it still failed. did
notice that when we upgraded to 2007 that some of the PC's that failed now
work and vice versa (not in all cases though).

Thanks again for your comments!

 

[Greg Maxey]

Peter,

Clear departure from goal and objective statement 2:

2. Post when and wherever you like, but stay in your range of
expertise.
Always be open to the ideas and suggestion of others. If you venture
outside your range then try to be correct.

 

[Peter T. Daniels]

Did you overlook the fact that OP asked "_Shouldn't_ Word [do such and
such]"? I provided a very reasonable reason why Word "shouldn't" by
default, out-of-the-box, provide the idiosyncratic behavior that OP
needs.

 

[Greg Maxey]

Did you overlook the fact that OP asked "_Shouldn't_ Word [do such and
such]"? I provided a very reasonable reason why Word "shouldn't" by
default, out-of-the-box, provide the idiosyncratic behavior that OP
needs.

But clearly this *isn't* the behaviour that the OP wants, or he/she
wouldn't have a macro designed to do otherwise.

Peter, your post is in no way helpful. When will you learn that the
way you prefer to work isn't necessarily the way others do? It's
blatantly obvious that the OP is not "most people" - and more
importantly, not you. The implicit message in your post is that the
OP is doing something "wrong" and shouldn't be trying to solve this
problem because doing it the "right" way (read: your way) would
avoid the problem entirely. This is incorrect. The OP is doing
nothing "wrong"; he/she simply wants to do work in a way that meets
his/her needs and is looking for help with overcoming an impediment
to this process.

Pull your head in and stop providing "non-answers" to questions
outside of your very limited area of expertise.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion
Groups - no membership required!

No Peter, you offered your opinion and commentary on a topic and question
that is clearly over your head (read "outside your range of expertise.")

The user has a file named something. He wants to save it as a new file
named something esle. Then he wants to delete the original file. Nothing
odd or particularily idiosyncratic about that.

This can certainly be done in a single Word instance using out of the box
interface provided Word cooperates and releases its pointers to temporary
files. The OP is smart enough to know that it isn't default SaveAs
behaviour. That is very likely the reason he has constructed a macro.

If you really wanted to be helpful , instead of say 'bloat your post
numbers', then as soon as you saw the term "macro" in the OP you should have
considered that the matter is most likely over your head and left it alone.

Your assertion "...usually when you Save As, it's because you want two
copies of the file with
different names, so it protects you from accidentally deleting the original"
is one of the most
cockamamie things you've said yet. Utterly ridiculous.

Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

 

[Gordon Bentley-Mix on news.microsoft.com]

If you require further assistance, please feel free to contact me at the
email address listed in my profile.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix on news.microsoft.com]

You're such a plonker...
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

Did you overlook the fact that OP asked "_Shouldn't_ Word [do such and
such]"? I provided a very reasonable reason why Word "shouldn't" by
default, out-of-the-box, provide the idiosyncratic behavior that OP
needs.

But clearly this *isn't* the behaviour that the OP wants, or he/she wouldn't
have a macro designed to do otherwise.

Peter, your post is in no way helpful. When will you learn that the way you
prefer to work isn't necessarily the way others do? It's blatantly obvious
that the OP is not "most people" - and more importantly, not you. The
implicit message in your post is that the OP is doing something "wrong" and
shouldn't be trying to solve this problem because doing it the "right" way
(read: your way) would avoid the problem entirely. This is incorrect. The OP
is doing nothing "wrong"; he/she simply wants to do work in a way that meets
his/her needs and is looking for help with overcoming an impediment to this
process.

Pull your head in and stop providing "non-answers" to questions outside of
your very limited area of expertise.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Bouncinalong]

Expirement is complete... This appears to be related to Word and not Excel.
I will continue to cuss this one and update this post if I find a solution.

Thanks all!