Automatizando uma Paginação de Dados em Excel com VBA
Introdução
Em situações críticas, principalmente de pequenas empresas com limitações de hardware e/ou software ou em locais onde o acesso à Internet é limitado, à necessidade de reduzir o conteúdo de tráfego de rede passa ser fundamental para consultar informações de um banco de dados.
Uma excelente alternativa(desde que não seja uma informação que exija segurança), é manter os dados na máquina do usuário através de uma interface de usuário (UI) fácil de ser manipulada e conhecida por todos. Este é o caso do MS Excel.
Vamos realizar uma consulta em um banco de dados SQL Server e disponibilizar os dados em uma planilha Excel, utilizando o recurso de paginação para reduzir a quantidade de dados que será apresentada ao usuário e apresentar apenas as informações necessárias para seu uso.
Este artigo faz parte da série Paginação de Dados.
Construindo o ambiente para Teste
Para que possamos reproduzir a utilização da paginação de dados dentro de uma planilha Excel, destacando suas vantagens para todo ambiente onde os dados são trafegados, vamos criar uma planilha Excel e criar um VBA no evento "SelectionChange" do objeto "WorkSheet".
Volto a lembrar que o script T-SQL para consultar os dados usando à paginação pode ser encontrado no outro artigo desta série, que complementa este conteúdo.
O objetivo deste artigo é mostrar como acessar os dados e automatizar o processo de paginação, deste modo não entraremos em detalhes sobre como utilizar a IDE do VBA para o MS Office.
Requisitos do Ambiente
É importante lembrar que para simular corretamente como efetuar à paginação de dados dentro do MS Excel, como indicado neste artigo é necessário:
- Microsoft Excel 2003 ou superior;
- Possuir acesso ao servidor SQL Server onde o seu banco de dados está disponibilizado;
- Possuir instalado no PC o MDAC 2.1 ou superior;
Automatizando através de VBA
É importante lembrar que para simular corretamente como efetuar à paginação de dados dentro do MS Excel, como indicado neste artigo é necessário selecionar o componente para manipulação de dados do SQL Server pelo VBA, e neste exemplo utilizamos o Microsoft Data Access Component (MDAC) 2.1.
Para isso, selecione o menu "Ferramentas" e clique em "Referências...".
Veja o Menu de "Referências" para selecionar o MDAC no seu projeto VBA
Assim que o você clicar no menu "Referências..." uma nova janela será exibida com uma lista de DLLs Win32 (não .Net Framework) disponíveis em seu PC, basta selecionar uma versão do MDAC (2.1 ou superior) para que seu projeto VBA tenha possibilidade de manipular a tabela desejada.
Veja a janela que exibe às Referências do projeto abaixo (clique na imagem para aumentar)
Com o MDAC carregado em seu projeto, começamos então à codificar o comportamento da planilha dentro do evento "SelectionChange". Desta forma, sempre que o usuário alterar o foco de uma célula da planilha este evento será disparado.
Em um projeto VBA é importante manter seus objetos declarados de modo que fique claro onde estão sendo construídos para uso no método, eu recomendo que todos os objetos e variáveis criadas estejam no início do método, assim é possível gerenciar facilmente objetos e variáveis, incluíndo novas e removendo às que não são utilizadas.
A declaração para encontrar os objetos em uma determinada planilha depende do nome desta planilha no projeto e não do nome indicado para exibição e controle do usuário. Neste exemplo, o nome da planilha no Projeto VBA é "Plan1" e o nome de exibição para os usuários é "Paging".
Para que os dados sejam carregados na linha correta da planilha, sem sobrescrever os campos formatados para configuração e paginação, definimos no Projeto VBA a variável "iRowGrid" para iniciar o carregamento de dados para a planilha e também para ajudar na indicação do Loop para cada célula à ser preenchida.
Veja o script VBA com as referências para a planilha Excel abaixo (clique na imagem para aumentar)
Veja este script VBA abaixo
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim iRowGrid As Integer
If Target.Row <= 2 And Target.Column = 2 Then
If Plan1.Cells(Target.Row, Target.Column) = "" Then
MsgBox "Digit a number !"
Plan1.Cells(Target.Row, Target.Column).Select
Exit Sub
End If
Conn.Open "Provider=SQLNCLI11;Data Source=127.0.0.1\PROD;Initial Catalog=WI_INFRA;User Id=YourSQLUser;Password=YouPws;"
RS.Open "EXEC PR_PAGING " & CStr(Plan1.Cells(1, 2)) & _
"," & CStr(Plan1.Cells(2, 2)), Conn, adOpenForwardOnly, adLockReadOnly
'Start at "row 5" on Excel Spreadsheet
iRowGrid = 5
Do While Not RS.EOF
Plan1.Cells(iRowGrid, 1) = RS("ID_EXAMPLE")
Plan1.Cells(iRowGrid, 2) = RS("NM_EXAMPLE")
Plan1.Cells(iRowGrid, 3) = CStr(RS("DT_CREATE"))
iRowGrid = iRowGrid + 1
RS.MoveNext
Loop
RS.Close
End If
Set RS = Nothing
Set Conn = Nothing
End Sub
Antes de obter os dados e realizar a paginação, definimos uma estrutura base de linhas e colunas para configurar a paginação chamada "Page Number" (A1) e seu valor (A2), além da quantidade de linhas à serem obtidas para cada página de dados, chamada "Rows per Page"(B1) e seu respectivo valor (B2). Por este motivo setamos à variável "iRowGrid" com o valor 5, assim o conteúdo da tabela será preenchido na planilha "Plan1" sempre à partir da linha "5".
O script VBA utiliza a stored procedure "PR_PAGING" para obter os dados e carrega todo seu conteúdo no objeto ADO RecordSet que declaramos como "RS".
A conexão com a instância SQL e o respectivo banco de dados é definido no objeto ADO Connection declarado como "Conn".
Para obter maiores detalhes sobre a stored procedure "PR_PAGING" veja o artigo Paginando uma Consulta com SQL Server.
Veja a Planilha Excel consultando a página "1" com "10" linhas/página na imagem abaixo
Abaixo podemos ver a mudança de conteúdo quando modificamos o valor do "Page Number" (B1) e o valor de "Rows per page" (B2).
**Veja a Planilha Excel consultando a página "2" com "5" linhas/página **na imagem abaixo
O vídeo abaixo apenas serve para facilitar à compreensão de como funciona esta automação de coleta e paginação de dados.
Veja o vídeo com a demo destas consultas (clique duas vezes sobre o vídeo para "Tela Cheia")
View
Conclusão
Possibilitar que seus usuários acessem diversas informações somente para consulta, de forma rápida e através de uma interface conhecida como é o caso do MS Excel, se torna uma enorme vantagem para manter toda uma equipe com dados atualizados e desconectados da Web.
Isso permite que seus usuários não estejam dependentes de um link com a web ou dentro de sua intranet para consultar as informações de seu bancos de dados.