How do you update an Excel file (Data Refresh and update formulas) WITHOUT opening the file?

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) :

Sub AutoUpdate()
'
' AutoUpdate Macro
' data refresh from MSQuery connection

'
    Sheets("Feuill1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

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("AutoUpdate")
# $objectExcel.run("RemoveODBC") # another custom macro for removing data connexion
$Classeur.saveas("destination_filepath")
$Classeur.close($false)
#$objectExcel.visible = $False;
$objectExcel.quit()
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.

I ended up finding Win32::Excel::Refresh and it fits the bill. It's a perl mod but it also comes with a windows executable that you can run if you don't know/want to deal with Perl.

You can manipulate Excel files with AutoIt, here is a library for doing so. You might even be able to use AutoIt to automate the SQL query and pass the data to the excel file (depending on the database engine you use). AutoIt can even do the email as well.