Atualizando Pivot Tables do Excel com scripts

Na semana passada me colocaram uma questão prática envolvendo Excel. Foram incluídas numa planilha diversas Pivot Tables conectadas a tabelas no banco de dados e essas Pivot Tables deveriam ser atualizadas automaticamente num determinado dia/hora, uma vez por semana.

As Pivot Tables são um recurso encontrado no Excel (e em outros programas de manipulação de dados, como o Lotus 1-2-3 e o Calc do Open Office) que permitem a criação de visões multidimensionais dos dados através de operações do tipo arrasta-e-solta nas colunas de valores. O resultado é uma poderosa ferramenta gerencial de análise que permite fazer o cruzamento dos dados das diversas colunas, sumarizando-os. O conceito é mais ou menos como os cubos de informações dos data-minings. Há uma matéria sobre o uso de Pivot Tables neste link.

Como escrevi uma série de posts mostrando como automatizar operações no Excel através de scripts VB, me pareceu que a solução estava bem próxima. Os scripts podem ser considerados como programas, de modo que é possível agendá-los sem problemas no Windows, resolvendo a questão do dia/hora especifico para atualização. Restava, então, desvendar a atualização dos dados em si.

Uma das características das Pivot Tables do Excel é que elas podem ser configuradas para reter as informações usadas para conectar com a fonte de dados, isto é, o nome do servidor e o usuário/senha para acessar o banco de dados. Você pode saber se isso já está configurado abrindo a planilha, selecionando a Pivot Table e pedindo para dar o Refresh nos dados manualmente. Se nenhuma informação sobre a conexão for solicitada pelo Excel, a configuração já está pronta para ser usada pelo script. Considerando que a planilha em questão tenha sido criada com as Pivot Tables associadas a uma fonte de dados externa (uma query num banco de dados, por exemplo), o problema fica restrito a criar um script VB que carregue a planilha, localize as Pivot Tables existentes, atualize os dados de cada uma delas e então salve a planilha com os novos valores.

Estudando o Modelo de Objetos do Excel, encontrei o método PivotTables do WorkSheet, que dá acesso à lista de todas as Pivot Tables existentes numa folha de trabalho. Também encontrei o método que faz a atualização dos dados (RefreshTable). A sintaxe para atualizar a primeira Pivot Table de uma folha de trabalho é como segue.

folha.PivotTables(1).RefreshTable

Como pode ser que não haja Pivot Tables numa determinada folha, é conveniente verificar a existência antes. PivotTables retorna uma lista e a propriedade folha.PivotTables.Count indica quantos elementos há nessa lista. A solução será mais efetiva se usar esse Count para dar Refresh em todas as PivotTables que encontrar.

For I = 1 To objWorkBook.Sheets.Count
Set folha = objWorkBook.Sheets.Item(I)
For j = 1 to folha.PivotTables.Count
folha.PivotTables(j).RefreshTable
Next
Next

objWorkBook.Save
objWorkBook.Close

No exemplo acima, todas as folhas da planilha aberta são percorridas e, para cada folha, todas as Pivot Tables são atualizadas. A instrução Save salva as alterações feitas de volta na mesma planilha que foi aberta. Se quiser salvar numa planilha diferente, use SaveAs - isso permitirá também salvar a planilha num formato diferente, preservando aquela que foi originalmente aberta.

Para ver como abrir uma planilha já existente e como obter a lista de folhas de trabalho dela, veja o post Automatizando a leitura de planilhas Excel.

Para fazer download do script de exemplo, clique aqui.

Mais Informações
Posts sobre automatização do Excel, Referência do Modelo de Objetos do Excel, download do script de exemplo.