Use Macros to Run a Query Script - Automate_Studio

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.

Note:

Be sure that the Excel Add-in is not active (that you are not logged on to Connect).

Note:

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


Sub RunPublishedQSQfile()
'----------------------------------------------
' 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 = "Table_20150113_150602"


     StudioMacros.StartRow = 2

     ' if not set, then records will be fetched as per script settings

     StudioMacros.RecordsToFetch = 100

      ' True to fetch all records. If set false then StudioMacros.RecordsToFetch will be respected

     StudioMacros.ExtractAllRecords = True

      ' Set True to write headers while Run

     StudioMacros.WriteHeader = True



 'StudioMacros.LogCell = "H2"

 'StudioMacros.SheetName = "Sheet2"



 'StudioMacros.ConnectionName = "w6r-800"


    ' Set RunReason to provide reason for run.

     StudioMacros.RunReason = "Run Reason"

    'Run = 0, RunOnlyFiftyRecords = 1

     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 Query script


                        
  Sub RunNormalQsqFile()

'----------------------------------------------
' Macro to use WinshuttleStudioMacros addin with code
'----------------------------------------------
'
' RunNormalQsqFile 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

    ' if not set, then records will be fetched as per script settings

     StudioMacros.RecordsToFetch = 100

      ' True to fetch all records. If set false then StudioMacros.RecordsToFetch will be respected

     StudioMacros.ExtractAllRecords = True


     ' Set True to write headers while Run

     StudioMacros.WriteHeader = True

 'StudioMacros.LogCell = "H2"

 'StudioMacros.SheetName = "Sheet2"


 'StudioMacros.ConnectionName = "w6r-800"

     ' Set RunReason to provide reason for run.

     StudioMacros.RunReason = "Run Reason"


     'Run = 0, RunOnlyFiftyRecords = 1

     StudioMacros.RunType = 0


 ' Set in case of evolve subitted file

 StudioMacros.LibraryName = "Query"


 ' Set it to Solution Name in case of evolve submitted file

 ' For local File,set strShuttleFile to local file path

     strShuttleFile = "Table_MARA"

      '  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

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.

Use Macros to Run a Query Script - Automate_Studio - 20.3

Automate Studio with Connect User Guide

Query macros

Known issues