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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
801
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top