Recently we were trying to automate a reporting workflow using RPA express. One of the task nodes required us to update a query that fetched data from an Oracle DB to spreadsheet using an excel add-in.
Needless to say, the fly in the ointment was the query execution time; as it can vary widely depending on a number of variables, including (but not limited to), output size, # of concurrent users, (perhaps) connection strength etc.
Therefore, the challenge for us was to create a workflow that would work dynamically, i.e. we did not want to double guess the expected run time of the query; instead we were looking for a loop that “waits” for the exact duration that the query would take to execute under any (and all) scenario(s).
We have used the following loop to achieve this
i) Fire the query (i.e. hit the refresh/update button)
ii) var_nm (recording variable) set to fixed variable value ab
iii) create a while loop which executes so long as the var_nm value does not equal Sheet2
iv) Add a new sheet to the query file using keyboard action (Shift+F11)…(assuming that the query file has only one tab and that’s Sheet1, the newly added one would be Sheet2)
v) keyboard actions Alt+O, H, R will select the new tab name (Sheet2)
vi) Keyboard Action Ctrl+C (to copy the tab name)
vii) assign the same to the variable var_nm (when the query is executed, the query tab ceases to lock the window allowing steps (iv) onwards to be excuted as a part of the while loop. which changes the value of Var_nm to Sheet2; this in turn invalidates the while loop condition and allows the procedure to move forward
The step (ii) comes handy if you put the aforesaid in a loop that requires you to update several queries in sequence. Though the present application is specific to excel add-ins any dynamic output query can potentially be managed using similar looping.
Statutory Warning: I am not a techno functional expert by any measure of imagination. I have recently started exploring RPA as a way to supplement our existing VBA driven automated workflows and this is an output of such grandiose endeavor. I am sure there would be easier and more elegant ways to achieve the aforesaid :). I was kicked to have found this workaround…