I am trying to pause between 2 actions with "Pause Until Conditions are Met". The first action is an applescript that causes Excel to refresh the table. This refresh sometimes takes a short time, sometimes a little longer. I would therefore like to set the pause accordingly.
For me, the logical setting would be
Pause until conditions are met
Pause until <> all of the following conditions are met:
This application:
Microsoft Excel
is running
But it doesn't work, only when I switch off Excel itself.
Have a look to see if any usually-available Excel menus are disabled during the refresh -- favourites are "Cut", "Copy" and "Paste". You can then pause until the menu item is enabled:
It checks the menus of the frontmost program, so if that isn't Excel -- you've pulled a browser window to the front to grab the data that's going to be put into Excel, for example -- that will be a problem.
It really depends on what you mean by "refresh". It's a specific action within Excel, updating data from an external connection, but you might mean something else. Could you describe the workflow in a bit more detail?
Excel imports the data from several csv files into an existing table.
The AppleScript in KM triggers an Excel macro, and this Excel macro imports 5 CVS files. In Excel, it is called "Update". In my German Version "Aktualisieren".
In your AppleScript that triggers the Excel table refresh, can you set a KM variable as the last step and use pause until that variable contains the particular value you set? I haven’t tried this myself and won’t be able to until later, but I want to throw it out there.
Now I have tried it out with variables. The same reaction: When I start the AppleScript, I get the message that should only appear after the table has been updated. I check the result with Notification and System Beep.
I assume that the variable at the end signals that the AppleScript has finished and not that the Excel process has finished.
In your AppleScript that tells Excel to refresh the table, I assume you use the refresh query table command. Have you tried that with the optional background query parameter set to false? That is supposed to keep Excel fro returning until the refresh is done.
That seems to do the trick for me, but I'm hampered by not having any available queries that take a long enough time to make it obvious.
Unfortunately, that doesn't help either...
Originally I did it with "ActiveWorkbook.RefreshAll".
After your suggestion I changed the VBA macro: Run in a loop all sheets with the parameter "background query = False" in the background.
It just doesn't work for me...
As well as @roosterboy's suggestion, note that Excel's AS dictionary has
refreshing (boolean, r/o) : Returns true if there's a background query in progress for the specified query table.
It sounds like you are triggering a VBA from an AppleScript -- if your AppleScripter adds in a
repeat while refreshing
delay 1
end while
...or similar, targeting the query table(s) that are being refreshed, then the AppleScript will wait for the refresh to complete. You may be able to add similar to the VBA macro instead, so it waits until all refreshes are complete before returning a value to AS and allowing the AppleScript to continue.
It wasn't clear from your original post that you were actually running the refresh in VBA instead of AS. I figured you were doing something like this in your AS:
tell application "Microsoft Excel"
tell active workbook
repeat with idx from 1 to (count of worksheets)
tell worksheet idx
repeat with qt from 1 to (count of query tables)
refresh query table (query table qt) without background query
end repeat
end tell
end repeat
end tell
end tell
My concern would be if there are dependencies between your query tables that would require them to be refreshed in a particular order.
There's lots of suggestions on the web on how to approach this from the VBA side -- try a search on "pause vba macro while refresh". But the simplest solution (if it works!) is to disable "background refresh" on all your external data connections -- you'll find those in the "Queries & Connections" pane. That way Excel should wait before continuing, which hopefully means the VBA macro will wait, which means the AppleScript will wait, which means your macro will wait. That may also let you leverage the "is menu item enabled" trick on eg the "Save" menu item.
If you want to build a pause into the AppleScript, then something like this could work -- it accounts for pivot tables as well, just in case, and should be inserted directly after the run VB macro macroName line:
set isRefreshing to true
repeat until isRefreshing is false
delay 1
set isRefreshing to false
repeat with eachSheet in (get every worksheet)
repeat with eachPivot in (get every pivot table of eachSheet)
if refreshing of eachPivot then set isRefreshing to true
end repeat
repeat with eachQuery in (get every query table of eachSheet)
if refreshing of eachQuery then set isRefreshing to true
end repeat
end repeat
end repeat
I have set up a complete query for Excel with Power Query for 5 tables. As I am not an expert, this was the simplest solution for me. I only had to trigger this query with a single command (run VB macro). Strangely, this update takes different lengths of time. That's why I wanted to wait for the end of the update and only then continue with the next KM macro.
As I said, I'm not an expert, I just thought it wouldn't be that complicated. In my younger years I conducted operas, and I find the orchestral scores much easier than the programming. So unfortunately I have to give up. Thanks for the tips, but I can't get it to work.
