Draft note about automating Excel scripts

I run a number of Excel macros which rely on addins eg Fame to work. In order to make sure these happen reliably I run these in the following manner.

On a PC which is the controller I run daily tasks. The list of daily tasks has a graphical user IF that runs the tasks once. It also has an automatic mode which is run from the scheduler.

Eg DailyTasks.au3 –> DailyTasks.exe graphical editor

Then the actually running of the tasks is done by

DoDailyTasks.au3 –>DoDailyTasks.exe

These are compiled to Exes to make the whole thing more reliable and less dependent on a number of files being in the right version and in the right place.

To run an excel macro I have a script that opens a Remote Desktop connection and then runs a child script. This child script may need to be placed in the Program Files subdirectory of the Remote Desktop computer in order to have permission to run automatically.

The child script will open and Excel file and run a specific Macro.

The macro closes Excel when finished. The child script is waiting for excel to close and then closes the remote desktop. This then allows the top level script to continue to the next action.

The Excel file that it runs is usually just a pretty blank file and is a receptacle for VB code to run other Excel sheets. I have enclosed some sample code:

Sub aa_UpdateSomething()
GetConstants
aa_StartUpTrick
Dim i
SeriesRow = 2
While st(SeriesRow, 1) <> ""
UpdateSomething

SeriesRow = SeriesRow + 1
Wend
GetConstantsClose
End Sub

Sub bb_UpdateSomething()
aa_UpdateSomething
Application.Quit
End Sub

Private Sub UpdateSomething

End Sub

The aa_ version is designed to run by hand from the spreadsheet for testing and the bb_ version if designed to be run automatically.