The following is a list of some scripting objects you can use to do different things:
❖File: Gives you access to create a workbook, open workbook, check the file exists etc. Examples follow:
The different functions associated with the File object come up with intelligent code completion:
This script creates a workbook with a sheet called ‘Finance’:
This script adds another worksheet to the workbook called ‘Logistics’:
This script runs a query from the Finance Object and then pastes in the results to the Logistics worksheet of the workbook. Finally, it saves the workbook in the models folder as test.xlsx:
❖Host: The Host Object allows you to log results or progress, display status messages etc. As with all objects, the full list of functions available for this is brought up by intelligent code completion:
The following is an example:
Explanation: This is an export function used in Action Scripts (see Action Script section), what this does is log in the Results window “Hello”, then it logs the progress of the operation in a splash screen with the words “very busy”, finally it will give a message box that says “It’s done”.
This is what happens when the script is then run:
