Consulta a Dados Externos Excel com Parâmetros

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.

Baixe a planilha

Abraço

Marcos Rieper