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

Question

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

Answer 1

My work-around 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" (no quotes) 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 do 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 who called the above script.

Answer 2

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.

Answer 3

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.