Objetivo: Demonstrar como executar stored procedures do SQL Server no Excel 2007 ou 2010. É necessário conhecimento de SQL.
Imagine a seguinte situação, você tem um relatório de vendas que é atualizado o tempo todo, pois o tempo todo são realizados faturamentos. E você quer demonstrar esta informação através de gráficos ou resumos, etc.
Como fazer?
Esta informação necessariamente estará em um banco de dados, caso a empresa tenha um sistema informatizado, e neste caso, pode-se fazer uso desta base em conjunto com o Excel para extrair estas informações de forma sempre atualizada.
Neste exemplo demonstrarei como usar os próprios recursos do Excel e um pouco de VBA para realizarmos a tarefa de extração desta base a partir de um banco de dados do SQL Server utilizando stored procedures.
Passo 1: com oExcel aberto selecione a aba Dados->De outras fontes->Do SQL Server.
Passo 2: Com o assistente de conexão aberto, digite o nome do servidor, conforme a primeira seleção, na segunda você deve selecionar para usar autenticação windows ou usar usuário e senha, dependendo da forma como sua empresa configurou o banco de dados.
Passo 3: Selecione o banco de dados que você irá utilizar, desmarque a opção conectar a uma tabela específica, dado que utilizaremos uma consulta.
Passo 4: Digite no campo nome do arquivo como quer chamar a sua conexão, na Descrição uma breve descrição, e em nome amigável um nome que será demonstrado nas conexões.
Passo 5: Aqui o Excel dá uma despirocada na minha opinião, dado que você tinha acabado de dizer que não queria utilizar uma tabela específica, mas tudo bem. Selecione qualquer tabela e clique em OK.
Passo 6: Selecione o botão propriedades, pois vamos alterar a consulta do banco.
Passo 7: Aqui você deve desmarcar o botão Habilitar atualização em segundo plano, pois senão, ele irá atualizar enquanto estão sendo executados comandos no VBA, gerando problemas.
Passo 8: Aqui você deve alterar o campo Tipo de comando para SQL, e no campo Texto do comando, alterar para o nome da sto que você estiver usando, inclusive passando os parâmetros necessários. Se possuir senha marque a opção Salvar senha.
Passo 9: Esta mensagem está informando que você mudou a conexão, pode clicar em Sim conformando a alteração.
Passo 10: Clique na guia Desenvolvedor e depois em Gravar Macro, clique na guia Dados->Conexões, selecione a conexão que você criou, clique em Propriedades, clique na aba Definição e clique em OK. Retorne na guia Desenvolvedor e clique em Parar Gravação.
Passo 11: O Excel terá gravado os passos necessários para alterar a consulta, apenas sendo necessária algumas alterações, no caso você deve apagar as linhas que estão selecionadas abaixo:
Passo 12: Você pode alterar o código fonte para passar os parâmetros que quiser, como no exemplo da planilha aonde são passadas as datas nas células da aba de configuração, e ao clicar no botão Consultar é chamada a stored procedure que passa os parâmetros alterando a consulta e atualizando os dados.
Abaixo o código fonte utilizado:
Sub lsAtualizaConexao()
Dim lDataIni As String
Dim lDataFim As String
'Converte as datas para o formato da consulta
lDataIni = "'" & Year(Range("B1").Value) & "-" & Month(Range("B1").Value) & "-" & Day(Range("B1").Value) & " 00:00:00'"
lDataFim = "'" & Year(Range("B2").Value) & "-" & Month(Range("B2").Value) & "-" & Day(Range("B2").Value) & " 23:59:59'"
'Abaixo as variáveis são passadas junto com o nome da stored procedure para realizar a consulta
'Os outros parâmetros já foram gravados pela ação de gravar macro
Sheets("Consulta").Select
Range("A1").Select
With ActiveWorkbook.Connections("Vendas").OLEDBConnection
.BackgroundQuery = False
.CommandText = Array( _
"stoVendas " & lDataIni & ", " & lDataFim)
.CommandType = xlCmdSql
.Connection = Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=RIEPER\SQLEXPRESS;" _
, _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RIEPER;Use Encryption for Data=False;Tag with co" _
, "lumn collation when possible=False")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Vendas")
.Name = "Vendas"
.Description = "Consulta as vendas realizadas em determinado período."
End With
ActiveWorkbook.RefreshAll
Sheets("Parâmetros").Select
ActiveSheet.PivotTables("Tabela dinâmica3").PivotCache.Refresh
Columns("E:E").Select
Selection.Style = "Comma"
Range("B1").Select
MsgBox "Consulta realizada com sucesso!"
End Sub
Para estes testes foi utilizado o SQL Server versão gratuita que você pode baixar da internet, bem como o banco de dados Northwind também gratuito da Microsoft.
Abraço
Marcos Rieper
