How to counteract Excel unexpectedly crashing on an overnight process

[RockandGrohl]

Hi all,

I'm trying to build a new tool that takes a 26 hour manual process and automates it, which should run overnight for 10-16 hours. The trouble is working around Excel crashing or halting unexpectedly.

I'm testing what I have now and in the last 20 minutes it's crashed or halted three times. How do I troubleshoot these crashes? The code will loop over 600 times and within the loop there are probably 12 sheets that open and close in total, numerous tab switches..

Sometimes it will work flawlessly and other times it seems to really struggle. Sometimes the code will just stop running and not let me know at what part it stopped.

How do I troubleshoot this? Seems very tricky to identify where the problem is, let alone amending it.

 

[WaterGypsy]

An inefficient way to do this would be to have an audit trail tab that you write messages to at specific stages in the process, giving you enough information to find out where it happens... if you include timestamps you will also see where it takes a long time and you might be able to speed that process up.

 

[RockandGrohl]

An inefficient way to do this would be to have an audit trail tab that you write messages to at specific stages in the process, giving you enough information to find out where it happens... if you include timestamps you will also see where it takes a long time and you might be able to speed that process up.

Hi mate,

I actually have that kind of system in place from some improvements I made to this process a few months ago, to identify which parts of code were taking the longest to execute.

I'm considering having a Windows script that opens and executes the macro, and when each line is attempted it writes a flag and saves, then if it fails 3 times, it just skips and goes to the next line.

That way I can open this script once every hour to re-boot the process if it doesn't work.