Here an example of the code used for one script. In this case a queryscript.
You can create this code for each script you want to connect with the Winshuttle Utility Kit and then connect these with VBa.
Sub RunPublishedQSQfile(myLogin As String) 'option to pass myLogin
'----------------------------------------------
' Macro to use WinshuttleStudioMacros addin with code
'----------------------------------------------
' Macro is build with use of Winshuttle Utility Kit.
' With this macro wordt the published query script is started.
' Don't change it with exception of AutoLogonCredentials.
'
' 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 = "<scriptname>" 'fill in your scriptname
StudioMacros.StartRow = 3 'startrow in script
' 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 = False
StudioMacros.RTV = ".........." 'will be filled by Winshuttle Utility Kit
StudioMacros.SheetName = "'............." Winshuttle Utility Kit
' User specific AutoLogonCredentials need to be addressed here. Uncomment below line to use AutoLogon Credentials
'StudioMacros.AlfName = "<autologonname>"
StudioMacros.AlfName = myLogin
'Set RunReason to provide reason for run. Uncomment next row if you need a Run Reason.
' StudioMacros.RunReason = "Run Reason"
' Set Runtype
' RunSpecifiedRange = 0,
' RunAndStopOnErrors = 1,
' RunFirstFiveRows = 2,
' RunOnlyErrorRows = 3,
' RunOnlyUnProcessedRows = 4,
' DebugSpecifiedRange = 5,
' DebugFirstRowOnly = 6,
' ValidateSpecifiedRange = 7,
' ValidateFirstFiveRows = 8,
' ValidateOnlyErrorRows = 9,
' ValidateOnlyUnProcessedRows = 10,
' Set StudioMacros.SyncCall to True to run scripts asynchronously
StudioMacros.SyncCall = True
' Call the Run Function to open published script
StudioMacros.OpenPublishedScript
' Call the Run Function to run script
StudioMacros.RunScript
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub
------------------------------
Jan van Asseldonk | Consultant
CTAC | +31629078169
------------------------------
Original Message:
Sent: 02-26-2020 04:25
From: Jan van Asseldonk
Subject: Script publishing
Hi Mark,
it's possible using VBa to run both (or more) scripts after another. Even query and transactionscripts.
And also without asking again for SAP Logon credentials a second time.
I've done it before. But beware when using VBa for this the Office version you use can be a problem in the future.
Vba for Excel2016, Excel2019 or Office365 is different. And an update of Office can result in a not working script.
That's one (main) reason I don't use it anymore.
I will search for an example how it was used in version 10.7 and Studio11 and attach it later.
------------------------------
Jan van Asseldonk | Consultant
CTAC | +31629078169
Original Message:
Sent: 02-25-2020 09:29
From: sigrid kok
Subject: Script publishing
No, Mark. That's a good workaround if you know VBA. :)
Thanks,
Sigrid
------------------------------
Sigrid Kok
PSE | Winshuttle NA
Original Message:
Sent: 02-25-2020 03:51
From: Mark Tiller
Subject: Script publishing
Hi Sigrid,
I there any reason you can't use VBA to run both scripts one after the other? I know it will ask for SAP logon credentials a second time but I think it would work.
Regards
------------------------------
Mark Tiller | Delivery Enhancement Developer
Transport for London (TfL) | 0203 054 4196
Original Message:
Sent: 02-21-2020 11:49
From: sigrid kok
Subject: Script publishing
Yes, Scott, on both counts.
I look forward to the day when that's not the case. :D
Sigrid
------------------------------
Sigrid Kok
PSE | Winshuttle NA
Original Message:
Sent: 02-21-2020 11:40
From: Scott Gorski
Subject: Script publishing
Well, good thing I didn't try it yet. :-)
So for Foundation users you cannot chain Query and Transaction Script, but for Multi-Scripts, would the process be the same as it was prior?
Thanks!
------------------------------
Scott Gorski
General Mills | Data Analyst
Original Message:
Sent: 02-21-2020 08:46
From: sigrid kok
Subject: Script publishing
Hi Scott - wish that were true, but it's not in v12. :) What is new in V12 is the ability to chain query and transaction/direct scripts, but only for Connect licensed customers.
Best Regards,
Sigrid
------------------------------
Sigrid Kok
PSE | Winshuttle NA
Original Message:
Sent: 02-20-2020 08:55
From: Scott Gorski
Subject: Script publishing
Depending on the version of Winshuttle you have, there will be a difference in how you accomplish this.
V11 and earlier you have to submit the Query script first, then take that submitted template file and use it to submit the Transaction script.
V12 I believe you can submit them together, but I have not tried this yet.
------------------------------
Scott Gorski
General Mills | Data Analyst
Original Message:
Sent: 02-20-2020 08:44
From: Vin Khokhrai
Subject: Script publishing
Brilliant. Thanks for that.
Vin
This communication is private and confidential and may contain information that is proprietary, privileged or otherwise legally exempt from disclosure. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. In accordance with our guidelines, emails sent or received may be monitored. Inmarsat Global Limited, Registered No. 3675885. Registered in England and Wales with Registered Office at 99 City Road, London EC1Y 1AX
This email has been scanned for viruses and malware by Mimecast Ltd
Original Message------
Yes, you can publish both the transaction and query scripts onto the same Excel Template file.
Once published, you will see both scripts in the drop-down menu on the Excel Winshuttle add-in.
------------------------------
Hemanth Vemanah | Analyst
Nutrien | 2155002457
------------------------------