Executing EXCEL and VBA app using PowersShell

[Ray Tham]

I have three Excel apps using VBA and running into memory problems. The topmost app PPMS_SYSTEM is basically a Project control app which keeps track of project id's and project related attributes. The user selects a project and PPMS_SYSTEM creates a sheet which is to be passed onto one of the other sub apps EST_SYSTEM or RBS_SYSTEM. However upon trying to load EST_SYSTEM we run into insufficient memory. PPMS_SYSTEM is not unloaded until EST_SYSTEM is loaded. Both PPSM_SYSTEM and EST_SYSTEM function independently, except for the project sheet (20 cells only) to be passed from PPMS_SYSTEM to either the EST_STYSTEM or RBS_SYSTEM. I was contemplating using PowerShell to load PPMS_SYSTEM and create the Project ID data sheet as an ASCII file and then EXIT out of EXCEL and reload EXCEL and EST(or RBS)_SYSTEM and then read in the PROJECT ID ASCII file. Doing it in this way I avoid having both the PPMS_SYSTEM and EST(or RBS)_system in memory at the same time.

Does anyone have experience executing EXCEL and VBA programs using PowerShell. I am looking at a Powershell User Interface, whereby the user can select:

(a) from Windows, Powershell will be activated and load EXCEL and start the PPMS_SYSTEM, upon selection of a Project ID by the user, PPMS_SYSTEM creates the Project ID ASCII file and exits out of EXCEL (ie unloads EXCEL) and control is transferred back to Powershell
(b) Powershell loads EXCEL and the user can select either the EST_SYSTEM or the RBS_SYSTEM and add more project data
(c) when user has completed processing/adding/modifying project data, the user will exit out of Excel and return to Windows

 

[Andrzejek]

So, this has nothing to do with Visual Basic (Microsoft) VB.NET...

But, since all of that is in VBA in Excel and you need to have Excel running for PPMS_SYSTEM, EST_SYSTEM or RBS_SYSTEM - why not just have a small Excel VBA app that:
[ul]
[li]Starts PPMS_SYSTEM[/li]
[li]Gathers data per selected project id's[/li]
[li]Closes PPMS_SYSTEM[/li]
[li]Starts either EST_SYSTEM or RBS_SYSTEM[/li]
[li]Does its magic[/li]
[li]...[/li]
[/ul]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Ray Tham]

You are right I should have posted it to VB Office (Classic).

I thought of the same process you suggested. However, how can I access VBA to create the 'small Excel VBA app' (the driver app)to perform what you suggested.
I have no problem accessing or storing workbook/sheets across directories (ie: PPMS_SYSTEM, EST_SYSTEM and RBS_SYSTEM reside in different dirs).
How do I unload an app and release all associated memory to restore EXCEL to its original settings before loading another app. Also how can I make
the apps more efficient in terms of memory usage. I will post this on VB Office (Classic) as well