Posted by José Lopes

This post shows how to run external scripts from an MS Excel file.

Lets supose you have an Excel file with a set of information used has base for other things. You need to automaticaly execute an external script to Excel when closing the file, generating some output (for instance an HTML page on a site).

the advantage to use an external script is that you may use whatever programing language that you like, carry out complicated calculations or assoications and avoiding to overload the excel file with additional code. You may share the Excel file with other without being concerned that they may damage your code.

To run an external script, or several, you must use a Visual Basic function in the Excel file. This function code is described in a moment.

On the other hand, the script must be executable in Windows. For instance, if we use a python script it must be converted to executable before being called by Excel.

Mentioned that, you can follow this sequence:

• Open Excel's Visual Basic Editor from the file you have the base information.
• On the framed called Project - VBAProject select ThisWorkbook.
• Write down the following Visual Basic code:

  (1)Private Sub Workbook_BeforeClose(cancel As Boolean)
  (2)  ActiveWorkbook.Save
  (3)  Application.DisplayAlerts = False
  (4)  Shell ("!PATH!")
  (5)  Me.Saved = True
  (6)End Sub

  Explaining the code:
  (1) Creates the function to run when the Excel file is closed (the code must be exactly like this).
  (2) Saves the Excel file (optional).
  (3) Deactivates Excel's automatic warning messages, lioke avoiding the question to confirm saving the file.
  (4) Runs the external script, so you must replace !PATH! by the full path to your script. For each script you add a dedicated line.
  (5) Deactivates the saving question when closing the file. This is a stupid question since we said on line 2 to save the file, but Excel also saves other irrelevant data (like the hour) and insists in asking for confirmation even after deactivating the warning on line 3.
  (6) Closes the function.
  
  
• Save and leave Visual Basic Editor. When you close the Excel file this function will run executing the external script.