My workaround is to record a Macro in the Excel file (so you have to use .xlsm file extension) (first you will need to go to Programs >> Windows Powershell and type at prompt
Set-ExecutionPolicy RemoteSigned to allow the script to run) :
' AutoUpdate Macro
' data refresh from MSQuery connection
Then I use a Powershell script that does the trick by : opening the Excel file, calling the macro, save and close the file.
In this example I make a copy of the original file for security/backup purpose, but of course you could save the original file itself.
$objectExcel = new-object -c excel.application
$objectExcel.displayAlerts = $false # don't prompt the user
#$objectExcel.visible = $True;
$Classeur = $objectExcel.workbooks.open("source_filepath", $null, $true)
# $objectExcel.run("RemoveODBC") # another custom macro for removing data connexion
#$objectExcel.visible = $False;
spps -n excel
So my Excel report is refreshed on a daily basis, without any manual intervention, by a Windows Planified task that called the above script.