Automate

 View Only

  • 1.  Transaction Option for Excel Refresh Wait ?

    Posted 08-20-2019 18:01
    ​

    Hello from Maine USA,

     

    After a chain of Transaction scripts to download SP01 background spool requests and live data at runtime, I exit back into the calling Windows batch to strip headers off one download AND pre-open the next Excel data file to allow ~5 seconds for refresh from the downloads.  This has worked for quarter-hourly runs with various adjustments for IT security, version upgrades, and hardware migrations for 6+ years.

     

    However, our newest Excel version (MSO 365 ProPlus 2016) seems unable to share the pre-opened Excel data file with Transaction due to tables / XML maps, and staying in Transaction / Excel instead of exiting back to the Windows batch file would be preferred anyway.

    Is there a Transaction option to wait for the data imports to refresh per their Excel settings before starting transactions ?



    ------------------------------
    Thanks, Ray Bachman
    Procter & Gamble Tambrands Inc,
    Auburn Maine Production Execution Key User
    ------------------------------


  • 2.  RE: Transaction Option for Excel Refresh Wait ?

    Posted 08-21-2019 11:32
    Hi Ray,

    Try using the Delay SAP logon setting in the properties panel of the Map tab. The setting uses milliseconds so use 5000 for a 5 second delay. You'll notice when the script runs it will stay on a Validating status for 5 seconds before it executes the script. 


    Thanks,


    ------------------------------
    Maria Simpson | Solutions Engineer
    Winshuttle North America | 4255276647
    ------------------------------

    Original Message


  • 3.  RE: Transaction Option for Excel Refresh Wait ?

    Posted 03-01-2020 06:37

    Thanks Maria, but the Winshuttle delay had no effect, so I concluded it was a sharing / "collaboration" ~logic issue, not just timing.

     

    I went with calling this Powershell script before each Winshuttle run.   

     

    # Call with PowerShell -File G:\PE\ExcelRefresh.ps1 FullDrivePathFilename.xlsx  ($Args[0])

    # Example PowerShell -File G:\PE\ExcelRefresh.ps1 G:\PE\NonMPS\CNF-935.refresh-d.xlsx

    $XL=New-Object -ComObject "Excel.Application"

    $XL.Visible=$True

    $WB=$XL.Workbooks.Open($Args[0])

    $WB.RefreshAll()

    $WB.Close($True)

    $XL.Quit()

    Kill -Name EXCEL*

    Exit

     

    Seems to work, and avoids fixed timing delays which might or not match the timing required each run depending on system speed, etc.

     

    Thanks,

    Ray Bachman,

    P&G Tambrands Inc

    Auburn Maine USA




    Original Message