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.)
Best Answer
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) :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.
So my Excel report is refreshed on a daily basis, without any manual intervention, by a Windows Planified task that called the above script.