4 de agosto de 2009

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.

Um comentário :

Jean Wetter disse...

Quero fazer isso com o Delhpi, algúem pode me ajudar? Porque preciso atualizar as planilhas e depois enviá-la por e-mail.

Valew...

Jean Wetter
desenvolvimento2@chavestecidos.com.br

Postar um comentário

OBS: Os comentários enviados a este Blog são submetidos a moderação. Por isso, eles serão publicados somente após aprovação.

Observação: somente um membro deste blog pode postar um comentário.