Kill temp files vba

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
170
Hi all,

I have a code in my macro to kill all existing .tmp files. This code works fine when it is used in a directory with less files, however, when I use it in a directory with, lets say, more than 20 files then it does not execute the kill function. When I go through my code with f8, then it works fine and kills all the .tmp files. Does anyone know what the problem is? This is the code:

*Without stepping through the code with f8, the variable WTempDir1 gives a value with the whole path like J:\Kwaliteit Helmond\.... However, when I step through the code with f8, it gives a value like 26KP949.tmp, so it finds the .tmp file and then it works.

Code:
    WTempDir1 = Dir("J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\" & Matnr & "\*.tmp")
    Stop
    If WTempDir1 = "" Then
        'Do nothing
    Else
        DoEvents
        WTempDir1 = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\" & Matnr & "\*.tmp"
        Kill WTempDir1
        On Error GoTo 0
    End If
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Have you tried something like this instead?

Code:
parentFolder = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\" & Matnr & "\"
tempFile = Dir(parentFolder & "*.tmp")
While tempFile <> ""
    Kill parentFolder & tempFile
    tempFile = Dir
Wend

Dir() returns the file name; not the whole path.

WBD
 

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
170
It works only once? I took the whole code and put it in VBA
Why do you use tempFile = Dir? Do I have to put a directory after that?
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
In the first call to Dir() you should provide the whole wildcard including the folder e.g.

Code:
tempFile = Dir("C:\Users\WBD\Desktop\*.tmp")

tempFile will then either contain a file name of an empty string. If it contains a filename, it's on the name of the file and not the whole path e.g. "deleteme.tmp". If you want to delete it then you'll need to do this:

Code:
Kill "C:\Users\WBD\Desktop\" & tempFile

Subsequent calls to Dir() don't need to specify anything; it will just fetch the next file that matches your original search criteria:

Code:
tempFile = Dir()

Comment out the line that says Kill ... and add a new one underneath it:

Code:
Debug.Print tempFile

Then you should see in the debug window a list of all the tmp files in the folder.

WBD
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Oops. I forgot that Kill accepts a wildcard. I guess that's what you were doing originally but I've never heard of that not working due to the number of files in the folder. You should be able to re-write your original code as this:

Code:
    WTempDir1 = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\" & Matnr & "\*.tmp"
    If Dir(WTempDir1) <> "" Then Kill WTempDir1

WBD
 
Last edited:

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
170
Both of the codes seem to work if there are not more than 5 tmp files to remove. Otherwise it gives me an error that it couldn't find the tmp file..

Is it possible to use vba for lets say 20 tmp files to remove?
 

Forum statistics

Threads
1,148,293
Messages
5,745,931
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top