ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Kill Delayed? (https://www.excelbanter.com/excel-programming/422663-kill-delayed.html)

Nigel[_2_]

Kill Delayed?
 
In my application, by design I move files from one folder to another.

I open the file, makes some changes to cells and save the file to another
folder, finally use Kill to remove it from the source folder. The new file
is then closed.

Most of the time this works, on the corporate network I occasionally get a
failure, where the new file is created but the old file is not killed.
Users have the right access rights to the server.

Q1. Why might this happen?
Q2. Can I apply a delay or some other action to prevent, warn or otherwise
fix this.

Sample of the code sequence in VBA code - as follows

' store old file name
sOldFile = ThisWorkbook.FullName

' file saved in different place and name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & sFile
Application.DisplayAlerts=True

' remove old file
Kill sOldFile

' close the new file
ThisWorkbook.Close savechanges:=False

--

Regards,
Nigel





Ron de Bruin

Kill Delayed?
 
Do you have the same problem when you use SaveCopyAs
Like this:
Save a copy
Close the file
Delete the file

'store old file name
sOldFile = ThisWorkbook.FullName

' Save copy of this file in different place
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs sPath & sFile
Application.DisplayAlerts = True

' close file
ThisWorkbook.Close savechanges:=False

' Delete file
Kill sOldFile


You can also Move the file
See
http://www.rondebruin.nl/folder.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nigel" wrote in message ...
In my application, by design I move files from one folder to another.

I open the file, makes some changes to cells and save the file to another
folder, finally use Kill to remove it from the source folder. The new file
is then closed.

Most of the time this works, on the corporate network I occasionally get a
failure, where the new file is created but the old file is not killed.
Users have the right access rights to the server.

Q1. Why might this happen?
Q2. Can I apply a delay or some other action to prevent, warn or otherwise
fix this.

Sample of the code sequence in VBA code - as follows

' store old file name
sOldFile = ThisWorkbook.FullName

' file saved in different place and name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & sFile
Application.DisplayAlerts=True

' remove old file
Kill sOldFile

' close the new file
ThisWorkbook.Close savechanges:=False

--

Regards,
Nigel





Nigel[_2_]

Kill Delayed?
 
Hi Ron

SaveCopyAs does not fix it. But I will check out your move file code.

Thanks

--

Regards,
Nigel




"Ron de Bruin" wrote in message
...
Do you have the same problem when you use SaveCopyAs
Like this:
Save a copy
Close the file
Delete the file

'store old file name
sOldFile = ThisWorkbook.FullName

' Save copy of this file in different place
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs sPath & sFile
Application.DisplayAlerts = True

' close file
ThisWorkbook.Close savechanges:=False

' Delete file
Kill sOldFile


You can also Move the file
See
http://www.rondebruin.nl/folder.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nigel" wrote in message
...
In my application, by design I move files from one folder to another.

I open the file, makes some changes to cells and save the file to another
folder, finally use Kill to remove it from the source folder. The new
file is then closed.

Most of the time this works, on the corporate network I occasionally get
a failure, where the new file is created but the old file is not killed.
Users have the right access rights to the server.

Q1. Why might this happen?
Q2. Can I apply a delay or some other action to prevent, warn or
otherwise fix this.

Sample of the code sequence in VBA code - as follows

' store old file name
sOldFile = ThisWorkbook.FullName

' file saved in different place and name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & sFile
Application.DisplayAlerts=True

' remove old file
Kill sOldFile

' close the new file
ThisWorkbook.Close savechanges:=False

--

Regards,
Nigel




Nigel[_2_]

Kill Delayed?
 
Since I use SaveAs to create the file in the destination, its path will be
different and possibly it's name will also change. So by saving the file
FullName in sOldFile, it will be different and no conflict should arise
between the old file and the new file.

But it seems Kill sOldFile does raise an error (sometimes!).

Do you think using the FSO will avoid any conflicts?

--

Regards,
Nigel




"Nigel" wrote in message
...
Hi Ron

SaveCopyAs does not fix it. But I will check out your move file code.

Thanks

--

Regards,
Nigel




"Ron de Bruin" wrote in message
...
Do you have the same problem when you use SaveCopyAs
Like this:
Save a copy
Close the file
Delete the file

'store old file name
sOldFile = ThisWorkbook.FullName

' Save copy of this file in different place
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs sPath & sFile
Application.DisplayAlerts = True

' close file
ThisWorkbook.Close savechanges:=False

' Delete file
Kill sOldFile


You can also Move the file
See
http://www.rondebruin.nl/folder.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nigel" wrote in message
...
In my application, by design I move files from one folder to another.

I open the file, makes some changes to cells and save the file to
another folder, finally use Kill to remove it from the source folder.
The new file is then closed.

Most of the time this works, on the corporate network I occasionally get
a failure, where the new file is created but the old file is not killed.
Users have the right access rights to the server.

Q1. Why might this happen?
Q2. Can I apply a delay or some other action to prevent, warn or
otherwise fix this.

Sample of the code sequence in VBA code - as follows

' store old file name
sOldFile = ThisWorkbook.FullName

' file saved in different place and name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & sFile
Application.DisplayAlerts=True

' remove old file
Kill sOldFile

' close the new file
ThisWorkbook.Close savechanges:=False

--

Regards,
Nigel






All times are GMT +1. The time now is 04:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com