I have an Excel file that want to update and save automatically with out having to open it or manually interact with. Manually, I open the file up and hit data refresh which goes and does a SQL query and then hit F9 for the formulas to update and then I just close/save.

(I then would mail the file out to people using a perl script or use SAS JMP to run some numbers/charts and also mail them out. Basically I need to script some things which require the XLS file to be updated.)


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

    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("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.

  • This helped me alot thank you ! This macro didn't work very well so I modified it to : Sub AutoUpdate() ActiveWorkbook.RefreshAll End Sub. I also added a Start-Sleep -s 10 after $objectExcel.run("AutoUpdate") because the macro had no sufficient time to execute before the $Classeur.saveas instruction. Thanks. – Michael Alves Oct 2 '15 at 8:44
  • @ifZenElse - Can you read my post on SO... stackoverflow.com/questions/54537847/…? I am using powershell to refresh an Excel File. It works when run manually. But the refresh does not seem to work when scheduled (SQL Agent, Windows Task Scheduler). – SherlockSpreadsheets Feb 7 at 17:08

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.