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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
Sub Clear_Temp_Files()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 "
End Sub

Open in new window

0
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.