Solved Kill working manually but not in code

[Alc]

I'm running the following line of code in a module

kill "Q:\Auto\Used Data\CasesCurrentlyBeingImported.csv"

It doesn't produce an error message, but doesn't do anything. The module ends at that point and exits out.

I thought it was a permission thing, but if I insert a break point just before that and manually run the same line, it works.
What's the difference? I'm clearly missing something.

 

[The_Doc_Man]

As a simple-minded test, find that KILL line and stick a DoEvents right after it. Might work, might not - but the fact that it appears to work when you single-step through it implies that the implied debug pause (while your process waits for you do take another step with F8 or F5) lets something else happen. DoEvents is another way to let something happen. This IS a guess, at best an educated one.

 

[MajP]

Are you sure the procedure you think you are calling is the correct procedure
Try this in your procedure to verify it is being called, the file exists, and it is being deleted

msgbox "File Exists: " & dir("Q:\Auto\Used Data\CasesCurrentlyBeingImported.csv") = ""
kill "Q:\Auto\Used Data\CasesCurrentlyBeingImported.csv"
msgbox "File Deleted: " & dir("Q:\Auto\Used Data\CasesCurrentlyBeingImported.csv") = ""

 

[Isaac]

I'm running the following line of code in a module

kill "Q:\Auto\Used Data\CasesCurrentlyBeingImported.csv"

It doesn't produce an error message, but doesn't do anything. The module ends at that point and exits out.

I thought it was a permission thing, but if I insert a break point just before that and manually run the same line, it works.
What's the difference? I'm clearly missing something.

Maybe a pause needed file in use?

 

[Isaac]

Make sure you do not have On Error Resume Next in the procedure

 

[Bennet]

My guess is that the actual reason for failure is that the file is still technically being accessed by your own procedure earlier on, and it hasn't "let go" of the file yet.

Could try putting the actual import in a child procedure, and maybe once that ends the parent procedure will be able to perform the deletion. Or just leave the file where it is after the import and put a deletion line in the exit code of the database, or in a little timer event on the form if you aren't already using the timer for anything.

 

[ebs17]

line of code in a module

Modules are not executed, only called procedures and functions.
It can also simply be possible that a specific line is not accessed => not called, bypassed due to a logical switch or something else.

 

[Gasman]

Modules are not executed, only called procedures and functions.
It can also simply be possible that a specific line is not accessed => not called, bypassed due to a logical switch or something else.

Yes, simple walking the code would identify if that is the case, yet people seem so reluctant to do so. Just chuck it all in and hope for the best?

 

[Alc]

Thanks, both.

DocMan: DoEvents didn't help, sadly.

MajP: Did as suggested and the same thing happened. The code gets to the 'kill' line, then nothing happens to the file I'm trying to delete and the code stops running.

 

[Gasman]

I am curious as to why intellisense has not change kill to Kill ?

 

[Alc]

Apologies to ebs17 and Gasman. I didn't see your responses before I last replied.
I can confirm 100% the line causing the problem is being reached and it's the the one that's meant to kill the file.

Bennet: I think you're right. I added extra error handling and the result is "Error #70. Permission denied".
I know I can delete the file using other methods, so that's not the issue, suggesting I may have it locked at that point.
As a test, I added some code to stop all Excel processes running on the computer, just before the kill is attempted, thinking this would free up any locks I might have had on the file. I confirmed in task manager that Excel is no longer running, but attempting to the file using 'kill' still produces the same error message.
At least it feels like I'm one step closer.

 

[NauticalGent]

I am curious as to why intellisense has not change kill to Kill ?

Exactly. "Kill" is built in and doesn't require any additional references. I would remove all error-handling, as suggested earlier, and see if and where it breaks.

 

[NauticalGent]

Apologies to ebs17 and Gasman. I didn't see your responses before I last replied.
I can confirm 100% the line causing the problem is being reached and it's the the one that's meant to kill the file.

Bennet: I think you're right. I added extra error handling and the result is "Error #70. Permission denied".
I know I can delete the file using other methods, so that's not the issue, suggesting I may have it locked at that point.
As a test, I added some code to stop all Excel processes running on the computer, just before the kill is attempted, thinking this would free up any locks I might have had on the file. I confirmed in task manager that Excel is no longer running, but attempting to the file using 'kill' still produces the same error message.
At least it feels like I'm one step closer.

I have similar issues with my agency. I can do things manually, like run Power Shell scripts, but as soon as I try to do it with VBA, it throws Error # 70. Our IT department says this is due to a STIG (Security Technical Implementation Guide) and can only be relaxed by God. Gotta love this stuff...

 

[ebs17]

code to stop all Excel processes

What do Excel processes have to do with a CSV? A CSV is a text file.
A file can also be locked if this file is linked; it does not have to be explicitly "opened" in another program. Consequently, you should analyze how this file is used so that you can stop using it and then delete it.

So you should think in terms of processes instead of complaining about individual lines of code.

Kill working manually but not in code

So there is a different situation or a different process, contrary to a superficial perception.

 

[Isaac]

Apologies to ebs17 and Gasman. I didn't see your responses before I last replied.
I can confirm 100% the line causing the problem is being reached and it's the the one that's meant to kill the file.

Bennet: I think you're right. I added extra error handling and the result is "Error #70. Permission denied".
I know I can delete the file using other methods, so that's not the issue, suggesting I may have it locked at that point.
As a test, I added some code to stop all Excel processes running on the computer, just before the kill is attempted, thinking this would free up any locks I might have had on the file. I confirmed in task manager that Excel is no longer running, but attempting to the file using 'kill' still produces the same error message.
At least it feels like I'm one step closer.

Like I said get rid of all the on error resume next. Then add a pause

 

[DickyP]

Almost as an aside if Kill doesn't work why not try Delete, or even better File System Object File.Delete? The ue of FSO especially gives full control of the process where you can check File exists to start with and then check that object has be released after delete.

 

[Alc]

In case it's of any use to anybody else, I found the problem but not the cause.

The file in question had some property which was preventing it from being deleted using the method I was trying.
I had the person who produced it remove and recreate it and all then worked, form my end of things.

I appreciate this isn't a final answer to the issue but, as is often the case, I've been pulled to work on something else.
Thanks to everyone who offered suggestions.

 

[ebs17]

What custom methods can a CSV file have?
A plaintext file with only data is the most innocent thing you can imagine.

 

[The_Doc_Man]

The file in question had some property which was preventing it from being deleted using the method I was trying.
I had the person who produced it remove and recreate it and all then worked, form my end of things.

It might actually be instructive to ask that person what that property was so that we can add it to the list of file property issues to consider if there is a repetition of this problem. I'm wondering if this was a "kiss of the web" case where there was a hidden "prefix" to the file that showed it was not a locally generated file.

 

[Alc]

I asked but he wasn't very forthcoming. I get the impression whatever he did was either embarrassing (and he doesn't want his boss to know) or he doesn't know himself.
His explanation was that "it was a permissions thing". I pushed for him to expand on that and he just said I the permissions on the file were wrong. That doesn't explain why I could delete it using some methods not others, but I don't think I'm going to get any more out of him and this is beyond my expertise, I'm afraid.