Checkboxes are duplicated over and over as emf in the Temp folder

[Guest]

Hi,

I created an app with some checkboxes. The checkboxes are not in a form, but
are on the worksheet. The app is running 24/7. From time to time the app is
closed and opened again. In these events it took about 20 - 30 minutes to
close or open the app. After doing some investigation, problem turned out to
be the temp folder. I found about 20.000 emf files. These emf files appeared
to be multiple copies of the checkboxes used in my app.
Has anyone an idea what's happening here? How can I avoid this? If not
avoided, can I, from within XL, delete these emf files in the temp folder?

Thx in advance
Tom

 

[Tim Zych]

Good troubleshooting. You're right. Excel stores stuff in a temp
directory, and it is a good idea to clean this out periodically for the
reason you noticed. I don't think that Excel files are as easily corrupted
anymore, but older versions of Excel workbooks can actually become corrupted
because of the buildup of this folder, usually when they have controls on
the worksheet.

The directory is located at Start -> Run %temp%, or in VBA Excel will tell
you via Environ("temp")

Delete everything in there. If there are locking issues, reboot but don't
start Excel and delete everything.

 

[Guest]

Hi Tim,

Can I automate this from within VBA or XL with a macro? Let say every day
the temp folder will automatically be cleaned up?

 

[Tim Zych]

Here's some code that will delete files within a folder.

The filesystemobject has a folder.delete method, but if any file in a folder
is locked (common for the temp directory) that will fail. This loops through
the files and sets up a collection of all of the files to delete. Then it
loops through the collection of files to be deleted and kills them. Killed
files are not sent to the recycle bin and can't be recovered.

Function DeleteFiles()
Dim oFso As FileSystemObject
Set oFso = New FileSystemObject
Dim cFilesToDelete As Collection
Set cFilesToDelete = New Collection
Dim oFile As File
Dim sFilename As String
Dim sExt As String
sExt = ".emf" '<- Optional, to limit by file type
' sExt = "" '<- to return everything
For Each oFile In oFso.GetFolder(Environ("temp")).Files '<- modify
directory as needed
sFilename = oFile.Path
If sFilename Like "*" & sExt Then
cFilesToDelete.Add sFilename, sFilename
End If
Next
Dim i As Long
For i = 1 To cFilesToDelete.Count
Debug.Print "Deleting: " & cFilesToDelete(i)
On Error Resume Next ' In case a file is locked, continue
Kill cFilesToDelete(i)
On Error GoTo 0
Next
End Function

I modified this from some code I have that loops through every subdirectory
with a root directory and returns a list of all files found. But for what
you are doing it seems you can stay focused in the temp root.

 

[Dave Peterson]

I use this .VBS script from Michael Harris:
http://groups.google.com/groups?threadm=#bXVsIHnAHA.920@tkmsftngp02

I put the file in a nice location on my harddrive and add a shortcut on my
desktop.

Then I can run it whenever I want.

 

[Guest]

Tim, Dave,

Thx for the quick responses. For now, I will not be able to work on this
issue till beginning next month, due to other issues. In the meantime, I
assume my 'problem' solved with your answers provided.

Have a nice weekend!
Tom