Better way to 'unhang' my workbook

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

I have some scripts that open workbooks on a server,
updates them by running the workbook's code, then closes
them. They run every day and some run more than once per
day. It works fine until the workbook errors for any
reason. When that happens the workbook pretends to still
be open. There is no instance of Excel open in memory
(Checking Task Manager's Processes tab). The temp file is
there but I can't delete it because of sharing violation.
The only solution is to reboot the server. I'd rather not
reboot the server 2-3 times a day, espcially since I'm
sharing it with someone.

The problem is that there is no error handing yet, in any
of the scripts or VBA code. I'm working on that, and it
should solve my problem. But it's going to take time to
get to them all. In the meantime, could someone offer a
way I can 'unhang' the workbook without rebooting the
server?

tod
 
Fundamentally your architecture isn't very stable. Excel and other desktop
products are not designed to run server side. It can be done (I have also
done the same thing) but Microsoft don't recommend this. There is a
document or two from Microsoft on this topic. I also started writing an
article on how to make best endeavours to ensure a reasonable degree of
stability/fault tolerance however it's still a compromise. I'll dig out the
link and finish the article if there's enough interest.

If you expect high volume usage there is a component which is designed to
run server side without Excel being installed. Not quite as flexible as the
real thing but appears to be quite interesting. Never tried it personally
but it looks very nice.

http://www.knowledgedynamics.com/

Sticking to the server side solution you should definitely consider shifting
the processing job to a non-critical server.

Renaming the open temporary file will allow creation of another one but this
is no solution really.

If you're going to stick with this option either way you'll have to build in
some stability Excel side so spending time on the error trapping would be
time well spent. Once that's sorted you can look at how best to setup your
environment (programmatically) to best ensure a working model.



--

Regards,


Bill Lunney
www.billlunney.com
 
I don't know a direct answer to your question, but based on what you are
doing an alternative may be to use FileCopy to copy the file locally, update
them and then FileCopy them back to the server.

Hth,
Tim
 
Back
Top