Executar stored procedures no Excel

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