Excel temp files

P

Thread Starter

Pat Levinson

Hello, I'm trying to make a simple HMI using Excel/VBA and an ActiveX driver for PLC communications. Basic functionality for the PLC part is OK (reading and displaying PLC registers in worksheet cells and sampling periodically with the ontime function).

However, as soon as I begin programming macros with more elaborate functions such as charts, moving data from one sheet to another, removing/adding rows, etc., a lot of temporary files are created and left behind in the Windows temp directory (files such as msoXXX.TMP and some other extensions). As far as I know, Excel creates temp files only as needed and should be able to clean them all up when it is closed, and not keep adding and adding temp files while it is running.

After a few hours or days, operation may become slow and erratic and the application needs to started up again, removing manually the temp files.

Could anybody shed a light into this subject? I'm using windows 98 and Excel 2000. I tried to strip down the application to detect which functions leave behind the temp files, but even the most basic ones seem to have that problem (simply adding a row programatically and filling it with data can generate temporary files which aren't deleted when Excel closes)

Pat
 
J

Johan Bengtsson

Your basic concern is probably why the temp files are created
in the first place, I don't really have an answer to that but
I would reccomend taking a look at how much memory you have in
your computer and how heavily used it is. I have no idea if this
would make any difference however.
Do you have to use windows 98? My guess is that there is there
is better OS choices (windows 2000) for running for several
days.
Ok I am fireing a shotgun blindfolded here but anyway, it might
be something someone else could go on with...

If you want to have a way of deleting the tempfiles at exit then
that would be easier solved however, always start excel from a
..bat file, make sure that bat file waits until excel finishes
(can be done with the command start) and delete all tempfiles
you can delete (some might be open by other applications)


Thought of one more thing, if the tempfiles are closed but fails
to be deleted then you could have a bat file executed at regular
intervals (or maually) just deleting all tempfiles it could
delete. This is quite safe since you are not able to delete
tempfiles still needed, you don't need to shut down excel (or
even close the sheet).


Oh, by the way, do you have all the latest patches and
servicepacks and so on, at least to excel? It could be a bug
in the VBA library that have been fixed already....


/Johan Bengtsson

----------------------------------------
P&L, Innovation in training
Box 252, S-281 23 H{ssleholm SWEDEN
Tel: +46 451 49 460, Fax: +46 451 89 833
E-mail: [email protected]
Internet: http://www.pol.se/
----------------------------------------
 
P

Pat Levinson

Thanks for the advise, Johan. However, the VBA commands I tried (kill, delete, deletefile) exit with an error when they encounter an open file. Not even disabling system messages or setting the option to skip read-only files seems to work. With what command (either VBA or from batch file) can you delete all files in the temp directory leaving open ones untouched, with no user intervention? Thanks again; BTW, I need to do it without exiting Excel, as it would need user intervention.
 
J

Johan Bengtsson

del <directory> /q
deletes all files in the given directory (if you want to delete the files in the current directory that would be
del . /q

Ok, then...
you probably *need* to exit excel if you can't delete them I have no idea why they accumulate but exiting excel, (this by itself *should* delete all the files), deleting remaining ones and restart excel should do the trick.

Can't you write a VBA script that exists excel automatically after a certain time (or at a certain time of day if that is prefered)? I have not studied VBA very much but I think that
would be possible. You could then make the .bat file call itself to put the sheet up again. This is not a nice solution, I know that but I don't
have much other to offer - sorry... someone else might have better solutions.

/Johan Bengtsson

----------------------------------------
P&L, Innovation in training
Box 252, S-281 23 H{ssleholm SWEDEN
Tel: +46 451 49 460, Fax: +46 451 89 833
E-mail: [email protected]
Internet: http://www.pol.se/
----------------------------------------

 
Top