Delete a workbook if it exist From Access

[Trevor G]

I am looking to check to see if a workbook exists at the beginning of some code and if it does I want to delete it, but if it doesn't then continue with the code.

Currently I use the Kill statement, but if someone goes into the location of the workbook and they delete/rename it the next time I run it I get a debug on the Kill line.

Kill CurrentProject.Path & "\Peer Review Report.xls"

 

[Trevor G]

Re: lete a workbook if it exist From Access

I have just altered the code and it is now working

On Error Resume Next
Kill CurrentProject.Path & "\Peer Review Report.xls"
On Error GoTo 0

Thread resolved.

 

[ghudson]

Re: lete a workbook if it exist From Access

I do not recommend using On Error Resume Next. You should test if the file exists, if true, then delete it.

If Dir(CurrentProject.Path & "\Peer Review Report.xls" ) <> "" Then Kill CurrentProject.Path & "\Peer Review Report.xls"

 

[Trevor G]

Re: lete a workbook if it exist From Access

Is there a reason for not using the on error, I have tested this about 20 times so far since I placed it and it works each time.

I would like to gain a better understanding and would happily change the code to your suggestion

If Dir(CurrentProject.Path & "\Peer Review Report.xls" ) <> "" Then Kill CurrentProject.Path & "\Peer Review Report.xls"

 

[ghudson]

Re: lete a workbook if it exist From Access

Is there a reason for not using the on error, I have tested this about 20 times so far since I placed it and it works each time.

I would like to gain a better understanding and would happily change the code to your suggestion

Sure it works but it is not the proper way to program your code for handling errors. >>> Error Handling In VBA

 

[Trevor G]

Re: lete a workbook if it exist From Access

Thank you,

I will read the link and amend accordingly.

 

[boblarson]

Re: lete a workbook if it exist From Access

On Error Resume Next is a potentially dangerous piece of error handling and should only be used sparingly. It can disguise some real problems because it will just go on and not stop at any real errors that might be important to know.

 

[Trevor G]

Re: lete a workbook if it exist From Access

Thank you both,

I can see the releveance.

My code has been adjusted, and also noted for the future.

 

[gemma-the-husky]

Re: lete a workbook if it exist From Access

in this instance it may not matter - but the kill instruction would probably fail, if someone had the xls file open, for instance

just resuming after any error regardless may therefore be insufficient - depending on what you want to do