You are here: Transaction > Run a transaction script > Use macros to run a script

Use macros to run a script

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.
Important: 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).

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).
Dim strShuttleFile = "C:\Users\Normal_Tx_Macro.txr"
Open the script to Run.
StudioMacros.OpenScript (strShuttleFile)
Run AddinObject to run script.
StudioMacros.RunScript

Properties for a Transaction Addin 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
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
AlfName	Auto logon file	Name of auto logon to use
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	Select the type of run from the possible values
RunScript	NA	Run the script

Split a document

This macro runs in Excel, and does not use any add-in objects. See the sample code.

Sample code: Run a published script


  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
                   
       ' 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,
                 
       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
                  
       ' 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,
                   
       StudioMacros.RunType = 0
                    
       strShuttleFile = "C:\Users\ssingh\Documents\Winshuttle\Studio\Script\Normal_Tx_Macro.txr"
                   
       ' 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

Sample code: Split a document


  Option Base 1

Sub doSplit()
    frmSplitDocInfo.Show
    
End Sub

Sub splitDocuments()
    Dim strColumn As String, _
        rowTypeCol As String, _
        amountCol As String, _
        headerRowValue As String, _
        detailRowValue As String, _
        maxLines As String, _
        offsetCol As String, _
        offsetAccount As String, _
        currentLineNumber As Integer, _
        headerSumCell As String, _
        rowInsertedAt As String, _
        offsetValue As Currency, _
        offsetCell As String, _
        insertHeaderRow As Boolean
    
    With frmSplitDocInfo
        rowTypeCol = .tbRowTypeColumn
        headerRowValue = .tbHeaderRowValue
        detailRowValue = .tbDetailRowValue
        maxLines = .tbMaxLines
        offsetAccount = .tbOffsettingGLAcct
        amountCol = .tbAmountColumn
        offsetCol = .tbOffsetAccountColumn
        
    End With
    
    Unload frmSplitDocInfo

    insertHeaderRow = True

    strColumn = rowTypeCol & ":" & rowTypeCol
    Range(strColumn).Select
    currentLineNumber = 0
    
    Do
        ActiveCell.Offset(1).Select
    
    Loop Until ActiveCell = "H"
    
    headerSumCell = ActiveCell.Address
    
    ActiveCell.Offset(1).Select
    
    While ActiveCell.Offset(1) <> ""
        While currentLineNumber < Val(maxLines) - 1 And ActiveCell <> ""
            currentLineNumber = currentLineNumber + 1
            ActiveCell.Offset(1).Select
        Wend

'End if there's no need to insert more header rows
        If ActiveCell = "" Then insertHeaderRow = False

'Capture row address for inserting rows; insert rows
        rowInsertedAt = ActiveCell.Address
        Range(rowInsertedAt).Select
        insertLines (2)
        ActiveCell.Offset(-1).EntireRow.Select
        Selection.Copy
        ActiveCell.Offset(1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False

'Change amount cell in newly pasted detail line to the offsetting entry value
        Range(amountCol & Mid(ActiveCell.Address, InStr(2, ActiveCell.Address, "$"))).Select
        offsetCell = ActiveCell.Address
        Selection.ClearContents
        Range(offsetCol & Mid(ActiveCell.Address, InStr(2, ActiveCell.Address, "$"))).Select
        ActiveCell = offsetAccount
        
'Capture value for offsetting entry amount
        Range("$" & amountCol & Mid(headerSumCell, InStr(2, headerSumCell, "$"))).Select
        ActiveCell.Formula = "=SUM(R[1]C:R[" & Trim(str(currentLineNumber + 1)) & "]C)"
        ActiveCell.NumberFormat = "General"
        offsetValue = ActiveCell.Value * -1
        Range(offsetCell).Value = offsetValue

'Select first header row for copying to next header row
        If insertHeaderRow = False Then
            Range(amountCol & ":" & amountCol).Select
            Selection.NumberFormat = "General"
            Range("a2").Select
            End
        End If
        
        Range(headerSumCell, "$" & amountCol & Mid(headerSumCell, InStr(2, headerSumCell, "$"))).Select
        Selection.Copy
        Selection.End(xlDown).Select
        ActiveCell.Offset(1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        headerSumCell = ActiveCell.Address
        
        currentLineNumber = 0
        
    Wend
    
    
End Sub

Sub insertLines(howMany As Integer)
    Dim count As Integer
    
    For count = 1 To howMany
        Selection.EntireRow.Insert
        'ActiveCell.Address(rowabsolute:=True).Interior = RGB(0, 255, 0)
        'Selection.EntireRow.Color = RGB(0, 255, 0)
    Next
    
End Sub