PAD not interacting with UI element correctly.

[Anonymous]

Hey

I have PAD flow that i use as part of a PA flow that is suppose to update a audit trail with daily key figures.

It's nothing complicated, it simply launches an excel instance and press the update all bottom on the datatab. It's launched as an attended flow, and the machine is logged-in and functional.

The excel file is set up so it reference the relevant figures into a single row table as a placeholder, "placeholder table", and then copy that into a new row in the table used for the actual audit trail.

The issue is, it doesn't seem to actually update the "placeholder table", even though it'll launch the excel file, click the UI elements and shut down the excel instance, like it is supposed to. So now I still have to manually update the queries before the audit trail run which is exactly what I was trying to avoid.

[Srini007]

Hi @Anonymous 

So, the UI elements in the Excel software are very unreliable, It's better instead of using UI element, can you try using a macro or VBScript? This way everything will execute on backend only and no UI interaction is required

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Regards,

Srini

[Anonymous]

I have zero experience with VBScript, so I have no idea how one would go about.

However as this is an issue that I know I will have to deal with in multiple flow, i'm ready to give that a go.

[Rajesh_G]

Hello @Anonymous 

If I am not wrong you want click Refresh All in the Data Tab for the Power Query refresh.

Am I Right? If not can you elaborate your question further more?

Regards,
Rajesh G

[Anonymous]

That is correct, yeah.

[Rajesh_G]

Okay then, you don't need any UI action or Coding to achieve.

You can simply achieve through change of Query Setting Itself @Anonymous 

If you open the workbook and in the Data Ribbon in the refresh all you will see the Connection Properties.

Then there will be a pop-up dialogue box named  Query Properties 
    In that Refresh data when opening the file check box will be available. Select the Checkbox and Click  Okay.

Hereafter whenever you open the file the query will be automatically executed.

Please find attached screenshots.

Rajesh G 

LinkedIn: Rajesh G's LinkedIn

 Found my answer helpful? Please consider giving it a LIKE
If this reply answers your question or solves your issue, Please marking it as the SOLUTION!
Your appreciation keeps me motivated. Thank you!  

[Agnius · Accepted solution]

First of all, I would assume that it might not be done refreshing the data when you close your Excel file. That might be the reason.

Secondly, as @Srini007 mentioned, automating the UI of Excel is not very efficient and usually unstable.

You can try running this VBScript to refresh all connections on a file:

Set Excel = CreateObject("Excel.Application")
Set Workbook = Excel.Workbooks.Open("%FilePath%")
Workbook.RefreshAll
WScript.Sleep 300000
Workbook.Save
Workbook.Close
Excel.Quit

You would need to provide the path to your file in %FilePath% or set it directly in the script. This will refresh all connections and wait 300000 milliseconds (5 minutes) before saving and closing. You can reduce the waiting time by setting the Sleep counter to a lower number, if you don't need to wait that long for your data to refresh.

You can alternatively run this PowerShell script that we usually use for refreshing data connections in an Excel file:

# Excel input file
$xlsx = "%FilePath%"

# Create Excel COM object
$excel = New-Object -ComObject Excel.Application

# Make excel invisible
$excel.Visible = $false

# Disable excel alerts
$excel.DisplayAlerts = $false

# Open excel workbook
$wb = $excel.Workbooks.Open($xlsx)

# initiate refresh of the connections
$wb.RefreshAll()

# get all the connections in the work book
$connections = $wb.Connections

# for each connection wait while it is refreshing
for ($i = 1; $i -le $connections.Count; $i++)
{
    $connection = $connections[$i]
    while ($connection.ODBCConnection.Refreshing)
    {
        Start-Sleep -Seconds 3
    }
}

# Save
$wb.Save()

# Cleanup (quit, garbage collection)
$excel.Quit()
Remove-Variable -Name Excel
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

Again, you need to provide the path to the file. This one will actually dynamically wait for connections to refresh, so you don't need to hard-code a wait time.

Either option works. It's just a matter of preference.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Anonymous]

I have accepted this as a solution as the Powershellscript solution works - however i do have a slight issue in that, in my tests at least, the excel-workbook doesn't close correctly. 

Thanks a bunch.

[Agnius]

What do you mean it doesn't close correctly?

[Anonymous]

Basically if I try to open up the workbook after the flow have run, I get an error message that another program has the file open. If I open up the task manager I can see excel running as a background process, if I shut it down, I can open up the workbook again.

So I'm 99 % sure that what's happening is that the flow call up the workbook in the background, but doesn't close it down correctly, so it is running in the background.