Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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.

share|improve this answer
    
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

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.

share|improve this answer
up vote 1 down vote accepted

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.

share|improve this answer
    
I rewrote the Refresh in C# so that it works with Office2013 a bit better. github.com/alapolloni/ExcelRefresh – Alex May 1 '14 at 8:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .