Excel - Having problems with Kill

[DOMAL]

I'm trying to delete three workbooks when closing a fourth workbook.

Basically, I generate these "exports" from one workbook through macros and have the user save the files somewhere else. I had to save the workbooks in the process so I could move data from one book to another without the variable bookX when adding new workbooks.

So now I need to delete them and I cant get the code to work on close.

I cant even get it to work when just running the macro, nevermind automatically on close.

This is the code I'm using at the moment:



Sub Auto_Close()

Kill ("X:\User Groups\Cost Centres.xls")
Kill ("X:\User Groups\Subjectives.xls")
Kill ("X:\User Groups\CC and Subj.xls")

End Sub

Thanks

DOMAL

 

[HarleyQuinn]

What's happening? Nothing? An error?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[DOMAL]

Nothing.

No error, doesnt delete the file. Nothing.

I've just tried another macro I found online:

Sub DeleteExample3()
'You can use this to delete one xls file in the folder Test
On Error Resume Next
Kill "X:\User Groups\Cost Centres.xls"
On Error GoTo 0
End Sub


Does nothing.

The workbooks I am trying to delete are not open.

 

[combo]

You can try to add a message at the beginning of macro to see if it fires. If so, do you have enough rights to delete files in X:\User Groups?

combo

 

[DOMAL]

Yes, system admin.

I'll try the message, thanks.

 

[DOMAL]

Yes the macro does fire, even when the file is closing.

Any ideas? Tried to see if it would work on my C:\ drive, didnt work then either.

Macro security is set to low and VB is allowed interaction (the checkbox on the bottom of the second security tab).

 

[combo]

Fhat about file attributes tested with GetAttr (details in vba help file)?
BTW, there is no need to allow access to vba project (used when the code is created by code) and set securty to low (typical setting is medium - macros allowed manually).

combo

 

[DOMAL]

Anyone any ideas?

 

[Fenrirshowl]

Can you not just use the (VBA) Delete method?

Fen

 

[DOMAL]

What method is that??

 

[Fenrirshowl]

Essentially it is,

File.Delete (boolean value depending on what you want to delete)

or

Folder.Delete (boolean value depending on what you want to delete)

The help file implies that it should work (then again, Kill should have worked!)

I suspect you may need to define your folder or file using the likes of GetFolder/GetFile script to define folder objects before you delete(I normally crib directly from the Help files for GetFolder/GeetFile code but usually use them to search folders and move files rather than delete them).

 

[DOMAL]

Ok, I'll give it a try, thanks!