Do not use the UI to refresh the connections in your file. It's unstable. Instead, use the following PowerShell script. We use it all the time for projects where we need to refresh the connections in a file:
# Excel input file
$xlsx = "%InputFilePath%"
# 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()
This will launch Excel, open the document, refresh all connections, wait for the refresh to complete, save the file and close Excel.
You just need to pass the path to the file as %InputFilePath%. Since it's a static file, it should be simple enough.
Then, if you want it open, you can open it again via the Launch Excel action. Or, you can modify the PowerShell script to make it not close Excel at the end (delete the part that goes after $wb.Save() ). And then use Attach to running Excel in PAD to create an Excel instance variable that you can use later to close this Master File.
Launching the other file is simple. You can use Get files in folder with a name filter and then apply sorting to get the latest file. Alternatively, if, for example, you always run your flow on the date that is in the Sync File name (or the day after), you can calculate it by using Get current date and time then Add to date time (if needed to deduct 1 day for example) and then Convert text to date time to get it formatted as MMdd.
Then, finally, there is an action to Run Excel macro. So, when you have your files open, you can use this action to run your macros on the Sync file. Finally, use Save Excel and Close Excel to finish.
Make sure you close both files, so none of them stays open. You might want to initially keep the Excel instances visible, so you can see it and close it manually in case something breaks. But when you're done, you can definitely run the entire flow in the background with both instances being invisible. No need to use any UI at all.
In case something breaks and your file gets locked because it is open in another app, simply terminate the Excel process via the Task manager. This can happen if you launch an invisible instance of Excel and forget to close it.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.
If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/
