Help Center>Studio (Connect)

Use macros to run a script

To run a script by using a macro, create the macro in Visual Basic for Applications (VBA).

  1. Open the data file that is associated with the script that you want to run.

    Important: Be sure that you are using a macro-enabled, or .xlsm, file.

  2. Press Alt+F11.
  3. In the VBA Editor, under Microsoft Excel Objects, double-click the sheet that contains the data you want to run.
  4. Copy and paste the code below into the window.
  5. 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.
  6. Click Save and then close the VBA Editor.
  7. In Excel, click the View tab, and then click Macros.
  8. 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

  1. Define Addin objects.

    Dim StudioMacrosAddin, StudioMacros

  2. Get Addin object from Excel.

    Set StudioMacrosAddin = Application.COMAddIns.Item ("WinshuttleStudioMacros.AddinModule")

  3. Get COM object from Addin object.

    Set StudioMacros = StudioMacrosAddin.Object.Macros

  4. Select the published file to run, and define other run properties (see Properties for a Query Addin object).

    StudioMacros.PublishedFile = "Table_20150113_150602"

  5. Open the published script file.

    StudioMacros.OpenPublishedScript

  6. Run AddinObject to run Query script.

    StudioMacros.RunScript

Run an existing Query script

  1. Define Addin objects.

    Dim StudioMacrosAddin, StudioMacros

  2. Get Addin object from Excel.

    Set StudioMacrosAddin = Application.COMAddIns.Item ("WinshuttleStudioMacros.AddinModule")

  3. Get COM object from Addin object.

    Set StudioMacros = StudioMacrosAddin.Object.Macros

  4. Select the Query script to run, and define other run properties (see Properties for a Query Addin object).

    Dim strShuttleFile = "C:\Table_20140930_143519.qsq"

  5. Open the script to run.

    StudioMacros.OpenScript (strShuttleFile)

  6. 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

AlfName

Auto logon file

Name of auto logon to use

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 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
                
       ' 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
                
       ' Set RunReason to provide reason for run.
       StudioMacros.RunReason = "Run Reason"
              
       'Run = 0, RunOnlyFiftyRecords = 1
       StudioMacros.RunType = 0
                    
       strShuttleFile = "C:\Users\ssingh\Documents\Winshuttle\Studio\Script\Table_20140930_143519.qsq"
                
       ' 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

  1. Macros that were created in Transaction or Query 10.x are not automatically converted to the Studio 12.x format. Macros in Studio 12.x format are not compatible with 10.x. To run, the macros must be manually converted.
  2. While you are using macros, the Winshuttle Add-ins should not be loaded (you should not be logged on to Connect). The Winshuttle Add-ins can be enabled.
  3. ExtractAllRecords fetches all records even if the number of records are more than maximum number of records allowed for download.
  4. Start Row is always 2 unless you specify a different row.