Can I use VBA to clear temp files for Excel?

I have created a number of Excel workbooks with VBA with the help of EE.

Sometimes, when multiple workbooks are opened, VBA that normally runs without a hitch, fails.
I have to close down all instances of Excel, clear the user temp files and then reopen.

Is there a better solution? eg. Use VBA to clear temp files
spar-kleOperations DirectorAsked:
Who is Participating?
 
ProfessorJimJamCommented:
Ok.

place the below code in the module and then run the macro "DeleteFilesFolders"  

Remember, it is very important to know, that files deleted cannot go to recycle bin, you cannot retrieve anything deleted with the macro below. also you must be careful when you place the folder path name.

when you run this code. basically, it will delete all the temp files and folder which is in the folder C:\Users\UserName\AppData\Local\Temp   except for the file from which you run the macro and other files not related to excel that cannot be deleted, meaning they are currency in use, or you do not have permission to delete.


Sub DeleteFilesFolders()
    Call RecursiveFolder("C:\Users\YourUSERNAME\AppData\Local\Temp")
End Sub


Sub RecursiveFolder(MyPath As String)

 Dim FileSys As FileSystemObject
 Dim objfolder As Scripting.Folder
 Dim objSubFolder As Scripting.Folder
 Dim objFile As File

 Set FileSys = CreateObject("Scripting.FileSystemObject")
 Set objfolder = FileSys.GetFolder(MyPath)

On Error Resume Next
 For Each objFile In objfolder.Files
 If Left(objFile.Name, 1) <> "~" And objFile.Name <> ThisWorkbook.Name Then
 objFile.Delete
 End If
 Next objFile

 For Each objSubFolder In objfolder.SubFolders
 RecursiveFolder MyPath & "\" & objSubFolder.Name
 objSubFolder.Delete
 Next objSubFolder

 Set FileSys = Nothing
 Set objfolder = Nothing
 Set objSubFolder = Nothing
 Set objFile = Nothing
On Error GoTo 0

End Sub

Open in new window

0
 
ProfessorJimJamCommented:
Sub Clear_Temp_Files()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 "
End Sub

Open in new window

0
 
ProfessorJimJamCommented:
you can change the code with any of the below mentioned lines

RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255 (Deletes ALL History)
 RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1 (Deletes History Only)
 RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2 (Deletes Cookies Only)
 RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 (Deletes Temporary Internet Files Only)
 RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 16 (Deletes Form Data Only)
 RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 32 (Deletes Password History Only)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ProfessorJimJamCommented:
or if you meant to delete the files in temp directory then

**  important,  make sure you do not put a wrong directory in the code below.  the macro action cannot be undone.

Sub DeleteTemp()

Dim WFileType As String
Dim WTempDir As String

On Error Resume Next
 
WFileType = "*.tmp"
 WTempDir = "C:\Windows\Temp\" ' Modify the code as per your machine's temp folder, each specific machine might have its own temp directory
 
Kill WTempDir & WFileType  ' Delete Temporary Files,

On Error GoTo 0
End Sub

Open in new window

0
 
spar-kleOperations DirectorAuthor Commented:
Thanks for your response.
I am referring to C:\Users\UserName\AppData\Local\Temp
Will clearing these files be permitted whilst Excel is open?
Will deleting the files mean that any unsaved Excel files cannot be saved with any updates made?
0
 
ProfessorJimJamCommented:
the problem with this directory that you are referring to is that whenever you open a and excel file, it generates a dummy backup file with the name  tilde ~ in front of it .  then the above code in ID: 40762039  does not do anything, because the line  6 On Error Resume Next   basically ignores the error given by VBA.  if you remove that line, you will get an error of 70 Permission Error. meaning you cannot delete an open file or any associated file for an open file.
0
 
ProfessorJimJamCommented:
why do you want to delete the temp file for the excel file you already opened?  i do not understand.
0
 
spar-kleOperations DirectorAuthor Commented:
..as per original request above:

"Sometimes, when multiple workbooks are opened, VBA that normally runs without a hitch, fails.
I have to close down all instances of Excel, clear the user temp files and then reopen."

Basically I'm trying to avoid code failing.
0
 
spar-kleOperations DirectorAuthor Commented:
Thanks, however I get a Compile error:
User-defined type not defined

This error triggers for each of the following

Dim FileSys As FileSystemObject
Dim objfolder As Scripting.Folder
Dim objSubFolder As Scripting.Folder
Dim objFile As File
0
 
Wayne Taylor (webtubbs)Commented:
Why not try and fix the code that fails when multiple workbooks are open?
0
 
ProfessorJimJamCommented:
Can you add from "Microsoft scripting runtime" in the vba reference? And then try the error wi disappear
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.