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.
