Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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.

share|improve this answer

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 at 8:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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