Big upcoming overnight Automation project - How to ensure VBA completes its task when likelihood of crashing is high?

[RockandGrohl]

We have an upcoming project I will be head of at work, where the manual click / wait / click / wait task that takes about 16 hours will be ran overnight.

This task uses portals which interfaces with a kind of CSV database of information (advert file), but the portal opens up an Automation Hub before it writes adverts to the advert file.

The problem I am forseeing is that the Portal, an xlsm workbook, opens another xlsm workbook, calculates some stuff, then writes that information to the advert file. In theory this sounds fine, but we all know how temperamental Excel is when you open one macro workbook from another macro workbook.

VBA Project Library errors, corrupted workbooks and all the rest... Or just simple crashing.

Here's my thoughts:

1. Use a Windows script to open the "Super Automation" workbook every 10 minutes, which contains a list of all our advert papers.
2. Each row contains a paper which has a series of checkmarks next to it
3. Every time an advert is attempted to be assigned to the paper, it places a checkmark in an adjacent column
4. When the workbook is opened and the "Go!" Macro automatically executed, it loops down the list of rows until it finds a paper with no adverts assigned.
5. When it finds this paper, it checks whether it has tried to assign an advert before.
6. If the Super Automation has attempted three times to assign an advert, it flags the paper as an error and moves on to the next
7. (The above step is because sometimes, a paper cannot have an automated advert and it needs to be manually picked)
8. Otherwise if there are less than the required number of checks, it will open adselect process and attempt to automate the advert selection.
9. Once complete, workbook saves and loops down to the next row.
10. Once all selections are completed, the workbook marks as finished and stops being opened.

So hopefully if there's a crash, the workbook will re-open and restart where it was interrupted, and this way it can be left overnight. I'm wondering if there's any other potential problems that I haven't yet foreseen.

 

[Fazza]

I don't understand what you're doing, so my comments may be totally off track.

What about writing to a txt log file each step of the way. So output whatever details are helpful - time, iteration number, record ID, name, reference code, name & path of last file, whatever.

I'd guess this is done already, but I'll say it anyway. Put extra effort into using best practice with the code : all variables declared to correct type, object references set to nothing when finished, explicit byval/byref for sub parameters, error handling code, checking validity of inputs & setup, make every step as robust as possible, etc. I guess too optimise for speed any code elements that are looped many times

not sure that helps you. all the best

 

[RockandGrohl]

I don't understand what you're doing, so my comments may be totally off track.

What about writing to a txt log file each step of the way. So output whatever details are helpful - time, iteration number, record ID, name, reference code, name & path of last file, whatever.

I'd guess this is done already, but I'll say it anyway. Put extra effort into using best practice with the code : all variables declared to correct type, object references set to nothing when finished, explicit byval/byref for sub parameters, error handling code, checking validity of inputs & setup, make every step as robust as possible, etc. I guess too optimise for speed any code elements that are looped many times

not sure that helps you. all the best

That does actually help, thank you. The process needs to be waterproof.


Here's the process.

Selection Portal has a list of papers, you click on a paper and open an Advert selection window, then choose the appropriate advert. We have an automation function which chooses the adverts for you, based on criteria.

You choose the advert and it commits the information to a CSV database, then you repeat for the rest.

The whole process is a manual job that takes upwards of 16 hours per week to achieve.