Automate

 View Only

  • 1.  Winshuttle with VBA

    Posted 01-03-2019 10:18
    ​Hello to the community,

    We wonder about use VBA with Winshuttle

    We would like to chat with the community to have your feeling about it and to know if some people have the same reflections about it
    In our winshuttle's version (11.2) we have 2 patches (only usable in 64bits version) :

    - the first one is about trouble with decimal separator when a csv file is created directly from MS Excel (only "," was possible  (we are in french version with ";"))
    - the second one is about trouble when we used VBA to launch scripts

    * If you don't use this patch, when you launch a script, VBA don't wait the end of the script result and continue the program.
    ==> It will not possible to use data generated by the script with other VBA transaction (format, etc)

    * If you use this patch, when you launch a script, VBA wait the end of the script result and it will be possible to use data with others chained VBA transactions

    Unfortunatly, the second one patch is not available in 11.4 version (we don't know for the first one)

    FYI : this was perfectly functional with V10.7 without patch




    Coul youd please tell us if you met this VBA trouble and, if yes, how you managed it ?

    for us, it's essential to have this functionnality.

    Especially, when RPA is used combinated with Winshuttle

    @support WS ==> Could you please included those patches in the next standard version  ? (as it was promised there are long time ago now)  

    Thanks by adavance for your answers, help and proposals

    David Macé (ENGIE GBS FINANCE)\Thierry Daclin (ENGIE INEO)
    *******************************************************************
    Bonjour à la communauté,

    Nous nous interrogeons sur l'utilisation VBA avec Winshuttle

    Nous aimerions discuter avec la communauté pour avoir votre opinion à ce sujet et savoir si certaines personnes ont les mêmes réflexions à ce sujet.

    Dans notre version de Winshuttle (11.2), nous avons 2 patchs (seulement utilisables en version 64bits) :

    - le premier concerne les problèmes liés au séparateur décimal lorsqu'un fichier csv est créé directement depuis MS Excel (seul "," était possible (nous sommes en version française avec ";")
    - la deuxième porte sur les problèmes lorsque nous utilisons VBA pour lancer des scripts

    * Si vous n'utilisez pas ce patch, lorsque vous lancez un script, VBA n'attend pas la fin du script et continue le programme.
    == > Il ne sera pas possible d'utiliser les données générées par le script avec d'autres transactions VBA (format, etc)

    * Si vous utilisez ce patch, lorsque vous lancez un script, VBA attend la fin du script et il sera possible d'utiliser les données avec d'autres transactions VBA

    Malheureusement, le deuxième patch n'est pas disponible en version 11.4 (Nous ne savons pas pour le premier)

    Pour Information : c'était parfaitement fonctionel avec la version V10.7 sans patch


    Vous pouvez nous dire si vous avez rencontré ce problème de VBA et, si oui, comment vous l'avez géré ?

    pour nous, il est essentiel d'avoir cette fonctionnalité.

    Surtout quand RPA est utilisé combiné avec Winshuttle


    @support WS ==> Pourriez-vous inclure ces correctifs dans la prochaine version standard ? (comme promis il y a un moment maintenant)

    Merci par avance pour vos réponses, votre aide et vos propositions
    David Macé (ENGIE GBS FINANCE)\Thierry Daclin (ENGIE INEO)




    ------------------------------
    David Mace | CHEF DE PROJET MOE
    ENGIE Headquarters | [City] | 0141204877
    ------------------------------


  • 2.  RE: Winshuttle with VBA

    Posted 01-04-2019 11:24
    Hi,

    "- the first one is about trouble with decimal separator when a csv file is created directly from MS Excel (only "," was possible  (we are in french version with ";"))
    - the second one is about trouble when we used VBA to launch scripts"

    I don't really understand this.  Can you tell me more?  

    "* If you don't use this patch, when you launch a script, VBA don't wait the end of the script result and continue the program."

    I'm sure there's other ways to do this.  I'm certain some are better.  The recipe I settled on:  

    1)  Create your CLI instruction.  
    2)  Create a batch file with your instruction from 1.  
    3)  Run the batch file with Scriptshell.run, which will make VBA wait for the script to finish before proceeding to the next instruction.  
    4)  Delete the batch file.
    5)  Do whatever comes next.  

    Some advice:  
    You should really build some checks into this sort of thing.  For example, I delete result files at the end of a query run so if the query fails I can programatically inspect the result file and see if it doesn't exist or if it has 0 records or something, the code will pick it up and fail safe.  










    ------------------------------
    Daniel Lozanski | BT AM BA LOG
    Siemens Building Technologies, Inc. | [City] | (847) 941-6356
    ------------------------------

    Original Message


  • 3.  RE: Winshuttle with VBA

    Posted 01-16-2019 04:20
    Hi Everyone,

    Daniel, I'm not sure to undestand your solution, but I'm  not sure we can do what you do when using Winshuttle foundation (as we do).
    Because when you launch a script on SAP production server with foundation, your script has to be published on the foundation server (and executed from...).
    So I'm not sure that a shell solution could work in that case.

    That VBA problem is really an issue for us. 
    The patch we got from winshuttle support do perfectly the job. but it seems they are still not included in version 12...

    ------------------------------
    thierry.daclin@engie.com |
    ENGIE | [City] |
    ------------------------------

    Original Message


  • 4.  RE: Winshuttle with VBA

    Posted 01-16-2019 08:47
    ​We are launching scripts with VBA on Foundation without any issue.  I set strShuttleFile equal to the script on the foundation site and it runs just like it would have been on my local PC.

    strShuttleFile = "http://winshuttle.dfamilk.com/ShuttleFiles/MRO Processing Scripts/Create or Extend MRO From Form.Txr"
    StudioMacros.OpenScript (strShuttleFile)
    StudioMacros.RunScript

    ------------------------------
    Greg Papendick | Director, Master Data Management
    Dairy Farmers of America, Inc. | [City] | 8168016458
    ------------------------------

    Original Message


  • 5.  RE: Winshuttle with VBA

    Posted 01-16-2019 09:17
    Thanks Greg.

    In fact that's the way we are doing too, but I 'm not sure that was the solution Daniel was talking about.

    Note: the issue we have is that

    StudioMacros.RunScript

    doesn't wait for the script to be complete, to continue VBA execution.

    And we have other VBA code after the script to process data. But sadly the data is not yet available (until the script is finished).

    We thought about  other ways (some were also suggested in this forum) to delay or to test the script completion, but none are very satisfactory on our opinion. In fact we are using the patch developed by winshuttle support on our demand, as the support admitted it has to be corrected, and that it was illogical that the addin let VBA continue before the script completion.

    It was perfectly working in 10.7. And it is working with the patch that Winshuttle support supplied to us. But sadly this patch is still not included in v12. Hope it will soon.

    ------------------------------
    thierry.daclin@engie.com |
    ENGIE | [City] |
    ------------------------------

    Original Message


  • 6.  RE: Winshuttle with VBA

    Posted 01-04-2019 16:56
    In regards to VBA.  I was unaware of any patches that were available but I created a work around that has been serving us well for well over a year. 

    We have a file that is provided to us by the business users that politically could not be revised.  I needed to put the file into a more useable form for Winshuttle to use so used VBA to transform and structure the data.  The file allowed the users to have adds, changes, extensions, deletes, and undeletes in the same file.  A total of 6 scripts were used to peform these functions (deletes and undeletes needed MM06 to perform changes in the deletion flag, and MM02 to set up the data).  I ran into the same issue when executing the scripts from VBA.  The VBA would execute a script and then continue on in the program and would often start a second script before the first was completed.

    Since I was having to segregate the file into the different functions I knew where each started and ended.  I use 2 while statements to execute.  The first is used to not let any VBA execute until the previous script has completed, it simply watches the Log Field for something other than "RUN LOG" which is what gets populated right before a script first executes,  It will have the full run log data when it is completed.

    While Cells(1, 2) = "RUN LOG"
                    DoEvents
    Wend

    Then call the routine to initiate the script.

    Call Run_Adds_Extends

    Then wait until the last record has been processed.  I do a wait until the last Log Record cell that will be written by the script is no longer blank.  I do this because some of my VBA needs the data to have been loaded before proceeding.

    While IsEmpty(Cells(Last_NewExt_Row, 2))
                    DoEvents
    Wend

    I am not a programmer so this may be a crude way to do it but it has been working for us.  This one program has eliminated 4-6 hours per day of manual effort.




    ------------------------------
    Greg Papendick | Director, Master Data Management
    Dairy Farmers of America, Inc. | [City] | 8168016458
    ------------------------------

    Original Message