Como você atualiza um arquivo do Excel (atualização de dados e atualização de fórmulas) sem abrir o arquivo?

Question

Eu tenho um arquivo do Excel que deseja atualizar e salvar automaticamente sem precisar abri-lo ou interagir manualmente. Manualmente, eu abro o arquivo e clico em refresh de dados, que faz uma consulta SQL e, em seguida, clico em F9 para as fórmulas atualizarem e, em seguida, apenas fecho / salvo.

(Eu então enviava o arquivo para as pessoas usando um script perl ou usava o SAS JMP para executar alguns números / gráficos e também enviá-los para fora. Basicamente, preciso rotear algumas coisas que exigem que o arquivo XLS seja atualizado.)

    

Answer 1

Acabei encontrando Win32 :: Excel :: Refresh e cabe a conta. É um mod perl, mas também vem com um executável do windows que você pode executar se você não sabe / quer lidar com o Perl.

    

Answer 2

Minha solução é gravar uma Macro no arquivo do Excel (assim você tem que usar a extensão de arquivo .xlsm) (primeiro você precisará ir para Programas > > Windows Powershell e digitar no prompt Set-ExecutionPolicy RemoteSigned para permitir script a ser executado):

Sub AutoUpdate()
'
' AutoUpdate Macro
' data refresh from MSQuery connection

'
    Sheets("Feuill1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Então eu uso um script Powershell que faz o seguinte: abrindo o arquivo do Excel, chamando a macro, salve e feche o arquivo. Neste exemplo, faço uma cópia do arquivo original para fins de segurança / backup, mas é claro que você pode salvar o arquivo original em si.

$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

Portanto, meu relatório do Excel é atualizado diariamente, sem nenhuma intervenção manual, por uma tarefa do Windows Planified que chamou o script acima.

    

Answer 3

Você pode manipular arquivos do Excel com AutoIt , aqui é uma biblioteca para fazer isso. Você pode até mesmo usar o AutoIt para automatizar a consulta SQL e passar os dados para o arquivo excel (dependendo do mecanismo de banco de dados usado). AutoIt pode até mesmo fazer o e-mail também.