Consulta a Dados Externos Excel com Parâmetros

Relatório Pronto
Como Gravar Macro no Excel
28 de julho de 2010
ContSe cor da célula
SOMASE COR DA CÉLULA EXCEL
29 de julho de 2010

Botão

Objetivo: Consultar dados externos com parâmetros no Excel.

Hoje eu li na comunidade Office Excel 2007 um tópico sobre o union de tabelas, realmente interessante que trabalhava com importação de dados externos.

Isso me fez lembrar de um problema apresentado pelo meu amigo Ivan, então este artigo é dedicado a ele.

Com base nisso, resolvi criar uma consulta a dados externos utilizando parâmetros dentro da planilha, e que ficou com um resultado legal.

Criando a Importação de dados:

  • Baixe o arquivo Dados.xlsx antes de iniciar o trabalho, os dados deste arquivo serão importados.
  • Clique na aba Dados, botão De Outras Fontes -> Do Microsoft Query.
  • Na tela selecione Excel Files*.
Escolher fonte de dados

Escolher fonte de dados

  • Clique em OK, e selecione o arquivo que foi baixado no seu computador.
  • Selecione Dados na próxima tela e clique na seta para a direita.
Dados

Dados

  • Clique em Avançar até que apareça o botão Concluir, determine o local como a célula A1 e clique em OK.
Tabela de dados importados

Tabela de dados importados

Criando o código fonte:

  • Clique na guia Desenvolvedor e no botão Gravar Macro, iremos criar uma macro com o código pronto de uma alteração e atualização da importação, neste passo você pode ter mais informações sobre gravação de macros no artigo Como Gravar Macro no Excel.
  • Clique na guia Dados e em seguida em Conexões, selecione a consulta criada e clique no botão Propriedades.
  • Clique na guia Definição apague seu conteúdo e digite no campo Texto de Comando: SELECT * FROM DADOS.
Propriedades da Conexão

Propriedades da Conexão

  • Clique em Ok para fechar a janela.
  • Na janela que continuou aberta clique no botão Atualizar->Atualizar Tudo.
  • Clique em Fechar.
  • Vá para a aba Desenvolvedor e clique em Parar Gravação, você terá criado o código fonte base para a passagem dos parâmetros.

Criando os parâmetros:

  • Prepare os campos conforme abaixo, estes servirão para a passagem dos parâmetros:
Tabela de parâmetros

Parâmetros

  • Na guia Desenvolvedor clique no botão Visual Basic e no módulo da planilha localize o código gerado pela gravação.
  • Neste código digite no topo:

 

Dim lSql As String
If Range(“J1”).Value = “” Then
lSql = “SELECT * FROM DADOS WHERE ” & _
“Data BETWEEN #” & Range(“J2”).Value & “# AND #” & Range(“J3”).Value & “#”
Else
lSql = “SELECT * FROM DADOS WHERE Vendedor = ” & “‘” & Range(“J1”).Value & _
“‘ And Data BETWEEN #” & Range(“J2”).Value & “# AND #” & Range(“J3”).Value & “#”
End If

Este código fonte acima em SQL está selecionando os dados quando for um determinado vendedor e quando a data estiver entre a data inicial e a final, inclusive. Depois você deve passar a variável lSql para o lugar .CommandText = Array(…. que você encontrará no código fonte abaixo. O código ficará conforme abaixo:

Sub Atualiza()

    Dim lSql As String
    
    If Range("J1").Value = "" Then
        lSql = "SELECT * FROM DADOS WHERE " & _
               "Data BETWEEN #" & Range("J2").Value & "# AND #" & Range("J3").Value & "#"
    Else
        lSql = "SELECT * FROM DADOS WHERE Vendedor = " & "'" & Range("J1").Value & _
               "' And Data BETWEEN #" & Range("J2").Value & "# AND #" & Range("J3").Value & "#"
    End If

    With ActiveWorkbook.Connections("Consulta de Excel Files").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array(lSql)
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\Excel\Blog Rieper Excel\Importação de Dados com Parâmetros\Dados.xlsx;DefaultDir=C:\Excel\Blog Rieper Ex" _
        ), Array( _
        "cel\Importação de Dados com Parâmetros;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Consulta de Excel Files")
        .Name = "Consulta de Excel Files"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Consulta de Excel Files").Refresh
    ActiveWorkbook.RefreshAll
End Sub

 

Finalizando:

  • Salve o código alterado e retorne para a planilha.
  • Crie uma forma ao lado dos parâmetros como o nome de Aplicar.
Botão

Botão

  • Clique com o botão direito sobre a forma criada e na opção Atribuir Macro.
  • Selecione a macro criada e clique em OK.

Pronto!, agora você deve digitar o nome do vendedor na célula I1, e o período inicial e final que devem ser retornados na importação de dados, e você terá estas informações filtradas pelos parâmetros.

Então é isso, muito obrigado pela leitura, mandem sugestões, dicas e dúvidas para o blog, ficarei feliz em ajudar.

Abraço

Marcos Rieper

27 Comments

  1. Aldir disse:

    Bom dia Rieper,

    Gostaria de saber se existe um meio de usar um procv, mas ao invés de usar o parâmetro Matriz_Tabela, possa usar uma referência à ela. Ex. Em vez de procv(a3;a4:c10;2;0) usaria procv(a3;a1;2;0) sendo que na célula a1 estaria a referência a4:c10. Com isso bastaria mudar o valor da célula a1 para d4:f10, que o procv iria fazer a busca em outra Matriz_Tabela.
    Abraços.

  2. Aldir disse:

    Boa tarde, Rieper,

    Funcionou muito bem, agora será que funciona também se o valor da referência for de outro arquivo?

  3. Aldir disse:

    Bom dia Rieper,

    Não funcionou, dá o erro #REF!.

  4. Aldir disse:

    Caro Amigo Rieper,

    Funcionou beleza, acho que meu exemplo não funcionava por conta do nome do arquivo tem tinha um hífen “-“. Agradeço sua ajuda e desejo a você, familiares e amigos um 2011 repleto de realizações.

    Abraços!

  5. Geraldo disse:

    Pessoal , importei uma tabela excel em uma Planilha, e estou fazendo filtros através de Query.
    Uso a seguinte Query
    SELECT Contas, sum(Valor) FROM `C:\INTEL\SBGEO_2010.xlsx`.Tabela_03 Tabela_03 Where Contas > 40000 and Contas < 50000 group by Contas

    Gostaria que esta função retornasse somente um número de linhas, por exemplo 15 linhas.

    Estou tentando usar o Limit 15… mas dá erro.

    Como fazer…

    Abraços

  6. Marcos disse:

    Segui todo o procedimento, e aparentemente funcionou, com exceção do Range de Datas, executei diversos testes e não consegui resolver, será que pode me ajudar?

  7. Marcos disse:

    Não filtra pelo range de data inicial e final

  8. Marcos disse:

    Se possivel lhe envio os arquivos para conferir, pode divulgar seu email?

  9. Thiago disse:

    Olá Marcos,

    Gostaria de ajuda para um problema que tenho.

    Pense em um banco de dados qualquer, uso query para executar as consultas SQL e retornar dados em uma tabela do excel.
    Até aí tudo bem. Imagine que eu retorne todos as colunas de uma tabela do banco no excel e INCLUA MANUALMENTE MAIS COLUNAS. Se estas colunas contiver formulas, sem problemas elas irão “andar” com as linhas quando eu mandar atualizar a query… Porem se forem dados a coisa desanda, quando a consulta for executada as linhas que “saíram” do critério SQL desaparecerão e se realinharão as linhas das colunas que inclui manualmente irão se embaralhar no meio das modificações feitas pela consulta… Entendo que o query não esta errado, mas esta me atrapalhando muito…

    Acredito que só um código VBA vai solucionar o “problema”, porem não sou programador, nem tenho formação na área.

  10. Thiago disse:

    Olá Marcos;

    Talvez não tenha entendido sua colocação ou não expliquei adequadamente. Vou tentar citar um exemplo:

    Imagine hipoteticamente um tabela de cliente onde só exista o nome (sem sobrenome), eu executo um SELECT para trazer o nome e tal. Imagine que agora no excel manualmente eu inclua a coluna sobrenome. No momento que atualizar esta consulta e o criterio eliminar ou adicionar linhas na tabela do excel os sobrenomes irão se misturar com os nomes, “furando” toda a montagem manual. Me corrija se eu estiver errado mas não consigo ver algo dinamico com o PROCV.

    Obrigado.

  11. Ruberval disse:

    Bom dia, eu tenho uma planilha que tem Estado e alguns municípios, e o que eu queria era uma maneira onde eu digite o nome do estado e me apareça todos os municípios relacionados aquele estado de minha planilha. tem como?

  12. Rodrigo disse:

    Porque sua planilha só puxa os dez primeiros registros, mesmo que eu coloque mais dados na planilha dados?

  13. Anselmo disse:

    Olá, gente!
    Desculpem-me se a pergunta que faço abaixo parece pergunta idiota, mas é que só agora é que decidi trabalhar com leitor de código de barras nos meus programas.

    Gostaria de saber se o leitor de código de barras é capaz de converter o código de barras em caracteres de texto, e como é que devo fazer para esse texto ser inserido numa célula do Excel.

    Grato a quem puder ajudar-me nessa informação.

    • Marcos Rieper disse:

      Boa tarde Anselmo,

      O leitor de código de barras lê as barras e converte em número o texto.

      Para utilizá-la no Excel ou outro programa você só precisa instalá-lo, ao ler um código de barras este número será inserido automaticamente.

      Abraço

      Marcos Rieper

  14. Olá!

    Marcos, fiz uma Query (extensa) que busca os dados em BD SQL Server 2008, e estou tentando usar o princípio do seu exemplo, porém está dando erro de Array devido ao tamanho do SQL, existe alguma forma de resolver isso?

    Obrigado.

  15. Gustavo disse:

    Bom dia Marcos!
    Estou com o seguinte problema…
    tenho 4 arquivos… um chamado “Cadastro Equipamento.xlsm”, outro “Cadastro Transporte.xlsm”,outro chamado “Cadastro Nota Fiscal.xlsm” e outro “Pesquisa.xlsm”

    No “pesquisa.xlsm”, fiz uma conexão com as outras 3 planilhas..
    Quando eu abro ela atualiza automaticamente e pega os dados das outras planilhas.. até ai beleza…
    só que quando eu quero alterar o valor em qualquer uma das 3 planilhas e tento abrir.. ela fala que só pode abrir no modo leitura, pois o arquivo já está sendo usado…
    preciso de uma macro que pare com a conexão para que eu possa abrir os outros arquivos normalmente..

    Para atualizar, o comando que eu tenho é: ActiveWorkbook.RefreshAll

    Uma das conexões é a seguinte:

    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\SEVEN-PC\Users\SEVEN\Desktop\Nova pasta\minhaempresa\Excel\Cadastro Clientes.xlsm;Mode=Share Deny Write;Extended Properties=”Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Bypass UserInfo Validation=False;HDR=YES;”;Jet OLEDB:System database=””;Jet OLEDB:Registry Path=””;Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=””;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

    Tem alguma sugestão?

  16. Robert disse:

    Como usaria esse exemplo buscando os dados de uma base firebird?

  17. Leandro Fribel disse:

    boa noite,
    preciso coletar dados do site
    dados;
    nome / telefone

    de uma cidade

    para callcenter

    como faço essa macro no excel/

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.