Executar stored procedures no Excel

Planilha de Cálculo de Horas Extras com Acesso a Banco de Dados Access - VBA
Planilha de Cálculo de Horas Extras com Acesso a Banco de Dados Access – VBA
22 de junho de 2011
Gráfico de Gantt no Excel - Gráfico de Barra Empilhada
Gráfico de Gantt no Excel – Gráfico de Barra Empilhada
30 de junho de 2011

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.

GUT PPTAbraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

12 Comentários

  1. Walter Bernal disse:

    Boa tarde!

    Marcos,
    Show de bola o tutorial. Bem explicativo, por acaso estava fazendo o download do SQL Server para aprender a trabalhar com ele. Vou testar essa funcionalidade junto ao banco.

    Abraço!

  2. Juliano disse:

    Valeu mesmos,
    Muito bom o tuto… funcionou corretamente.
    Show de bola…
    Valeu!

  3. Luis de Almeida disse:

    Gostei muito.
    Tenho 3 ligações no mesmo ficheiro.
    Estas ligações vão buscar 3 tabelas diferentes de uma base de dados MS SQL Server.
    Preciso saber como permitir a um operador alterar os dados da conexão sem ter de ir a dados –> ligação.

  4. Pedro disse:

    Me perdi no passo 10 onde fica essa guia Desenvolvedor? Alguem pode me ajudar?

  5. Agnalgo disse:

    o meu deu erro na conversao da data como faço pra transformar os parametros da procedure em variaveis do excel?

  6. Agnaldo disse:

    Agora deu certo valeu pelo artigo me ajudou muito !!!

  7. Michela disse:

    Não sei como fazer pro excel pedir a data

  8. Valei brothers muito bacana mesmo.... vou ver se compro o curso de VBA... Grato disse:

    Muito bom muito grato…!

  9. Raul Silva disse:

    era exactamente isso que procurava.
    mais estou a enfrentar um grande problema, toda vez que eu fecho o ficheiro excel que pode suportar a macro, quando voltar a abrir tenho que refazer a ligação.
    A minha questa é sera que isso é normal,(a ligação volta sempre para o a tabela e nao para SQL….

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.