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.Note:
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.
Notes: Be sure that the Excel Add-in is not active (that you are not logged on to Connect).
Asynchronous macro runs are supported.
Transaction 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 Transaction Addin object).
Dim StudioMacros.PublishedFile = "MM02_MacroTest"
- Open the published script.
StudioMacros.OpenPublishedScript
- Run AddinObject to run script.
StudioMacros.RunScript
Run an existing Transaction 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 Transaction script to run, and define other run properties (see Properties for a Transaction Addin object).
The following is for running the solution locally: Dim strShuttleFile = "C:\Users\Normal_Tx_Macro.txr" The following is for running the solution from Evolve: StudioMacros.LibraryName = "Transaction" Set to Solution Name for solutions submitted from Evolve. strShuttleFile = "MM02"
- Open the script to Run.
StudioMacros.OpenScript (strShuttleFile)
- Run AddinObject to run script.
StudioMacros.RunScript
In case of chain scripts with "All from individual" settings, when the user uses a macro to run the script on SAP Trust type SAP server, the SAP logon dialog appears for each script. This can be handled by using the parameter "StudioMacros.UseSameSAPServerForChain = True" in the macro code so that the SAP session of the first script gets used for all the subsequent chain scripts.
Properties for a Transaction Add-in object
Run settings or Advance Run Options set in the script are not respected during the Macro run. These need to be set via the various properties that have been exposed for the Add-in object.
| Property/ Function | Value(s)/ Parameter | Description |
|---|---|---|
|
PublishedFile |
Published script description |
Selects the published file to run |
|
OpenPublishedScript |
NA |
Opens the published script |
|
OpenScript() |
Script file path |
Opens the script to run |
| LibraryName | Library name | Opens the library which includes the solution |
|
StartRow |
Row number |
Excel row from which data upload should start |
|
EndRow |
Row number |
Last Excel row from which data should be uploaded |
|
WriteHeader |
NA |
Write headers during the run |
|
LogColumn |
Column/Cell |
Column or cell where run logs should be written |
|
SheetName |
Name of sheet |
Sheet name to be used for the run |
|
ConnectionName |
SAP connection name |
SAP connection name Example StudioMacros.ConnectionName = "w6r" Used in case of Evolve and Studio Manager. |
|
RunReason |
Run reason string |
Specify a reason for this run |
|
RunSelectedRows |
NA |
True – run only the rows that are selected in Excel |
|
RunFilteredRows |
NA |
True – run only the displayed rows in Excel |
|
RunType |
0 – Run Specified Range 1 - Run And Stop on Errors 2 – Run First Five Rows 3 – Run Only Error Rows 4 – Run Only Unprocessed Rows 5 – Debug Specified Range 6 – Debug First Row Only 7 – Validate Specified Range 8 – Validate First Five Rows 9 – Validate Only Error Rows 10 – Validate Only Unprocessed Rows 11 - Simulate Specified Range 12 - Simulate First Five Rows 13 - Simulate Only Error Rows 14 - Simulate Only Unprocessed Rows 15 – Run Unprocessed Rows and Error Rows 16 - Validate Unprocessed and Error Rows 17 - Simulate Unprocessed and Error Rows |
Select the type of run from the possible values
|
|
RunScript |
NA |
Run the script |
|
SyncCall |
NA |
True – Run the script in a synchronous fashion |
Sample code: Run a published Transaction script
this sample code will be updated with the "Run Error Row and Unprocessed Rows" run type.
Sub RunPublishedTXRfile()
'----------------------------------------------
' Macro to use WinshuttleStudioMacros addin with code
'----------------------------------------------
'
' RunPublishedfile Macro
'
' Dim StudioMacrosAddin, StudioMacros
On Error GoTo ErrHandler
' GET ADDIN OBJECT FROM EXCEL
Set StudioMacrosAddin = Application.COMAddIns.Item("WinshuttleStudioMacros.AddinModule")
If StudioMacrosAddin Is Nothing Then
MsgBox "Unable to initialize object of WinshuttleStudioMacros.AddinModule addin"
Exit Sub
End If
' Get com object from addin object
Set StudioMacros = StudioMacrosAddin.Object.Macros
If StudioMacros Is Nothing Then
MsgBox "Unable to initialize com object of Macros"
Exit Sub
End If
' Select the published file to Run
StudioMacros.PublishedFile = "MM02_MacroTest"
StudioMacros.StartRow = 2
StudioMacros.EndRow = 6
' Set True to write headers while Run
StudioMacros.WriteHeader = True
'StudioMacros.LogColumn = "H"
'StudioMacros.SheetName = "Sheet2"
'StudioMacros.ConnectionName = "w6r-800"
' Set RunReason to provide reason for run.
StudioMacros.RunReason = "Run Reason"
'StudioMacros.RunSelectedRows = True
'StudioMacros.RunFilteredRows = True
' RunSpecifiedRange = 0,
' RunAndStopOnErrors = 1,
' RunFirstFiveRows = 2,
' RunOnlyErrorRows = 3,
' RunOnlyUnProcessedRows = 4,
' DebugSpecifiedRange = 5,
' DebugFirstRowOnly = 6,
' ValidateSpecifiedRange = 7,
' ValidateFirstFiveRows = 8,
' ValidateOnlyErrorRows = 9,
' ValidateOnlyUnProcessedRows = 10,
' SimulateSpecifiedRange = 11,
' SimulateFirstFiveRows=12,
' SimulateOnlyErrorRows=13,
' SimulateOnlyUnprocessedRows=14,
' RunUnProcessedAndErrorRows=15,
' ValidateUnProcessedAndErrorRows=16,
' SimulateUnProcessedAndErrorRows=17
StudioMacros.RunType = 0
' Call the Run Function to open published script
StudioMacros.OpenPublishedScript
' Call the Run Function to run script
StudioMacros.RunScript
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub
Sample code: Run an existing Transaction script
Sub RunNormalTXRfile()
'----------------------------------------------
' Macro to use WinshuttleStudioMacros addin with code
'----------------------------------------------
'
' RunPublishedfile Macro
' Dim StudioMacrosAddin, StudioMacros
On Error GoTo ErrHandler
' GET ADDIN OBJECT FROM EXCEL
Set StudioMacrosAddin = Application.COMAddIns.Item("WinshuttleStudioMacros.AddinModule")
If StudioMacrosAddin Is Nothing Then
MsgBox "Unable to initialize object of WinshuttleStudioMacros.AddinModule addin"
Exit Sub
End If
' Get com object from addin object
Set StudioMacros = StudioMacrosAddin.Object.Macros
If StudioMacros Is Nothing Then
MsgBox "Unable to initialize com object of Macros"
Exit Sub
End If
StudioMacros.StartRow = 2
StudioMacros.EndRow = 0
' Set True to write headers while Run
StudioMacros.WriteHeader = True
'StudioMacros.LogColumn = "H"
'StudioMacros.SheetName = "Sheet2"
'StudioMacros.ConnectionName = "w6r-800"
' Set RunReason to provide reason for run.
StudioMacros.RunReason = "Run Reason"
'StudioMacros.RunSelectedRows = True
'StudioMacros.RunFilteredRows = True
' RunSpecifiedRange = 0,
' RunAndStopOnErrors = 1,
' RunFirstFiveRows = 2,
' RunOnlyErrorRows = 3,
' RunOnlyUnProcessedRows = 4,
' DebugSpecifiedRange = 5,
' DebugFirstRowOnly = 6,
' ValidateSpecifiedRange = 7,
' ValidateFirstFiveRows = 8,
' ValidateOnlyErrorRows = 9,
' ValidateOnlyUnProcessedRows = 10,
' SimulateSpecifiedRange = 11,
' SimulateFirstFiveRows=12,
' SimulateOnlyErrorRows=13,
' SimulateOnlyUnprocessedRows=14,
' RunUnProcessedAndErrorRows=15,
' ValidateUnProcessedAndErrorRows=16,
' SimulateUnProcessedAndErrorRows=17
StudioMacros.RunType = 0
' Set in case of evolve subitted file
StudioMacros.LibraryName = "Transaction"
' Set it to Solution Name in case of evolve submitted file
' For local File,set strShuttleFile to local file path
strShuttleFile = "MM02"
' Call the Run Function to open specified script
StudioMacros.OpenScript (strShuttleFile)
' Call the Run Function to run script
StudioMacros.RunScript
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub Known issues
- Macros that were created in Transaction or Query v10.x are not automatically converted to the Studio 20.3 format. Macros in Studio 20.3 format are not compatible with v10.x. To run, the macros must be manually converted.
- While you are using macros, the Automate Add-ins should not be loaded (you should not be logged on to Evolve/Studio Manager). The Automate Add-ins can be enabled.
- Start Row is always 2 unless you specify a different row.
- RunSelectedRows and RunFilteredRows are mutually exclusive options. The script will use the one that is set last.
- Data files must not use the Data Review workflow to be executed by using macros in Evolve/Studio Manager mode.
- Save to Evolve/Studio Manager will not work when a macro is used to run the data file in Evolve/Studio Manager mode.