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

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.


"Essenga_Tom" <(E-Mail Removed)> wrote in message
news:CE74E7CE-1452-45BF-A01A-(E-Mail Removed)...
> 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

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" wrote:

> 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.
>
>
> "Essenga_Tom" <(E-Mail Removed)> wrote in message
> news:CE74E7CE-1452-45BF-A01A-(E-Mail Removed)...
> > 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
>
>
>
>
>

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.


"Essenga_Tom" <(E-Mail Removed)> wrote in message
news:3A3275A0-1C29-482A-A3A3-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>>
>>
>> "Essenga_Tom" <(E-Mail Removed)> wrote in message
>> news:CE74E7CE-1452-45BF-A01A-(E-Mail Removed)...
>> > 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
>>
>>
>>
>>
>>

I use this .VBS script from Michael Harris:
http://groups.google.com/groups?thre...%40tkmsftngp02

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.

Essenga_Tom wrote:
>
> 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" wrote:
>
> > 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.
> >
> >
> > "Essenga_Tom" <(E-Mail Removed)> wrote in message
> > news:CE74E7CE-1452-45BF-A01A-(E-Mail Removed)...
> > > 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
> >
> >
> >
> >
> >

--

Dave Peterson

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

"Tim Zych" wrote:

> 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.
>
>
> "Essenga_Tom" <(E-Mail Removed)> wrote in message
> news:3A3275A0-1C29-482A-A3A3-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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.
> >>
> >>
> >> "Essenga_Tom" <(E-Mail Removed)> wrote in message
> >> news:CE74E7CE-1452-45BF-A01A-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
> >>
> >>
>
>
>