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*.

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.

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

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.

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:

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.

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