To run a script by using a macro, create the macro in Visual Basic for Applications (VBA).
- Open the data file that is associated with the script that you want to run.
Be sure that you are using a macro-enabled, or .xlsm, file.
- Press Alt+F11.
- In the VBA Editor, under Microsoft Excel Objects, double-click the sheet that contains the data you want to run.
- Copy and paste the code below into the window.
- Replace the data file path, script file path, sheet name, and auto logon name below with the information for your files and your auto logon name.
- Click Save and then close the VBA Editor.
- In Excel, click the View tab, and then click Macros.
- Click the macro that you want to run, and then click Run.
Be sure that the Excel Add-in is not active (that you are not logged on to Connect).
An asynchronous macro run is supported.
Query macros
Run a published script
- Define Addin objects.
Dim StudioMacrosAddin, StudioMacros
- Get Addin object from Excel.
Set StudioMacrosAddin = Application.COMAddIns.Item ("WinshuttleStudioMacros.AddinModule")
- Get COM object from Addin object.
Set StudioMacros = StudioMacrosAddin.Object.Macros
- Select the published file to run, and define other run properties (see Properties for a Query Addin object).
Dim StudioMacros.PublishedFile = "Table_20150113_150602"
- Open the published script file.
StudioMacros.OpenPublishedScript
- Run AddinObject to run Query script.
StudioMacros.RunScript
Run an existing Query script
- Define Addin objects.
Dim StudioMacrosAddin, StudioMacros
- Get Addin object from Excel.
Set StudioMacrosAddin = Application.COMAddIns.Item ("WinshuttleStudioMacros.AddinModule")
- Get COM object from Addin object.
Set StudioMacros = StudioMacrosAddin.Object.Macros
- Select the Query script to run, and define other run properties (see Properties for a Query Addin object).
The following is for running the script locally:Dim strShuttleFile = "C:\Table_20140930_143519.qsq"The following is for running the script from Evolve: StudioMacros.LibraryName = "Query"
Set to the Solution Name for a file submitted from Evolve. For a local file, set strShuttleFile to the local file path.strShuttleFile = "Table_MARA"
- Open the script to run.
StudioMacros.OpenScript (strShuttleFile)
- Run AddinObject to run the Query script.
StudioMacros.RunScript
Properties for a Query Addin object
Run settings or advanced run options that are set in the script are not respected during the macro run. These need to be set by using the various properties that have been exposed for the Addin object.
Property/ Function | Value(s)/ Parameter | Description |
---|---|---|
PublishedFile |
Published file description |
Selects the published file to run |
OpenScript () |
Script file path |
Selects the script to run |
OpenPublishedScript |
NA |
Opens the published script |
StartRow |
Row number |
Excel row from which downloaded records should be written |
RecordsToFetch |
No. of records |
Number of records to be returned by the Query run |
WriteHeader |
True/False |
Write mapping headers for the downloaded data |
ExtractAllRecords |
True/False |
Override RecordsToFetch and extract all records |
RunReason |
Run reason string |
Specify a reason for this run |
LogCell |
Cell |
Cell where run logs should be written |
SheetName |
Name of sheet |
Sheet name to be used for the run |
ConnName |
SAP connection name |
SAP connection name |
RunType |
Type for Run |
0 – Run to fetch records as per settings 1 – Override RecordsToFetch and download only first 50 records |
RunScript |
NA |
Run the script |
SyncCall |
NA |
True – Run the script in synchronous fashion |
Sample code: Run a published Query script
|
Sample code: Run an existing Query script
|
Known issues
- While you are using macros, the Automate Add-ins should not be loaded (you should not be logged on to Evolve). The Automate Add-ins can be enabled.
- ExtractAllRecords fetches all records even if the number of records are more than maximum number of records allowed for download.
- Start Row is always 2 unless you specify a different row.
- Data files must not use the Data Review workflow to be executed by using macros in Evolve mode.
- Save to Library will not work when a macro is used to run the data file in Evolve mode.