I run on a daily basis a set of VBA-rich Excel files. Most of them include MS Office application cross-talk, but also employ third-party applications and MySQL. Due to the fact of running those files in a specific order and at a specific time of day I set up an xlsm-based scheduler to run those files and control outputs. Since my 'application' is growing I'm facing a problem of resource usage and typical garbage collector errors in VBA (not mentioning lack of active VBA support from MS, poor IDE and debugging etc) therefore I'm looking for other ways of automation.
Processes I'm running are typical ETL from 3rd party sources and CRMs & RDBMSs, integration of the data to edible by non-DataScience users and tools (must cover Excel 64- and 32-bit architecture). Most of it runs along data validation after imports, type testing before further analysis, sometimes picture2data replacement, applying business logic to data, pushing outlook notifications, SharePoint data I/O etc.
My question is whether PowerShell-based scheduler running those VBA and VB scripts may be more effective when it comes to memory usage or it will only as effective as the VBA/VB code is?
It is worth noting that due to the given policy I'm strongly discouraged using excellent R or Python libraries for working with data, so I need to stick with MS tools.
Looking forward for any advice, hint or even a loose tip on the topic.