RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 801
- Office Version
- 365
- Platform
- 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:
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.
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:
- Use a Windows script to open the "Super Automation" workbook every 10 minutes, which contains a list of all our advert papers.
- Each row contains a paper which has a series of checkmarks next to it
- Every time an advert is attempted to be assigned to the paper, it places a checkmark in an adjacent column
- 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.
- When it finds this paper, it checks whether it has tried to assign an advert before.
- 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
- (The above step is because sometimes, a paper cannot have an automated advert and it needs to be manually picked)
- Otherwise if there are less than the required number of checks, it will open adselect process and attempt to automate the advert selection.
- Once complete, workbook saves and loops down to the next row.
- 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.