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