production

Mudar pasta Power Query Excel Dinamicamente

Mudar pasta Power Query Excel Dinamicamente

Neste artigo você aprenderá como mudar a pasta de uma consulta do power query dinamicamente.

É comum termos pastas de trabalho no Excel que precisamos mudar o local da consulta.

Infelizmente este processo necessita de um certo conhecimento de Power Query que nosso cliente muitas vezes não domina.

Por isso neste artigo você irá aprender como mudar a pasta do power query sem ter que abri-lo, facilitando assim muito o processo.

Além disso, irá ver um código VBA para selecionar a planilha.

Consultar Todos os Arquivos de Uma Pasta com Power Query

Para consultar todos os arquivos de uma pasta de trabalho no Power Query no Excel siga o procedimento abaixo.

Clique na guia Dados->Obter Dados->De Arquivo->Da Pasta

No processo iremos utilizar pastas de trabalho do Excel, mas poderiam ser outros tipos de arquivos, como XML, JSON, PDF, CSV, TXT na pasta.

Mudar pasta ou mudar fonte de dados Power Query Excel 15

Na tela seguinte são exibidos os arquivos que estão na pasta.

Clique então no botão Transformar Dados, este botão irá levar aos processos do Power Query para que os dados possam ser trabalhados antes de importar.

Mudar pasta ou mudar fonte de dados Power Query Excel 16

Na tela seguinte serão exibidas as planilhas contidas no primeiro arquivo de amostra.

Clique na Pasta no topo para que sejam importadas todas as planilhas da pasta de trabalho, como na imagem abaixo.

Para importar apenas uma planilha de cada pasta de trabalho selecione ela na lista.

Mas no exemplo estamos juntando todas as planilhas de todas as pastas de trabalho do Excel, por isso selecione a pasta, no caso Parâmetro2[5] e clique em OK.

Mudar pasta ou mudar fonte de dados Power Query Excel 17

Ao clicar neste botão serão carregados os dados para o Power Query de todas as planilhas das pastas de trabalho da pasta selecionada.

Então pode trabalhar estes dados e expandir eles se quiser clicando no campo Data que está com dados do tipo Table e expandir clicando no botão com setinhas ao topo.

Mudar pasta ou mudar fonte de dados Power Query Excel 18

O processo segue com várias parametrizações que pode ver passo-a-passo no vídeo ao topo ou se preferir no artigo: https://www.guiadoexcel.com.br/como-juntar-planilhas-no-excel-em-uma-so-download/

O resultado final pode ser uma tabela, tabela dinâmica ou conexão de dados.

No exemplo do vídeo fizemos o retorno das informações em uma tabela dinâmica conforme abaixo:

Mudar pasta ou mudar fonte de dados Power Query Excel 3

Como Mudar a Pasta do Power Query Dinamicamente?

Para mudar a pasta do Power Query Dinamicamente você pode fazer carregando uma tabela com este caminho.

E dentro do Power Query usando este caminho em substituição ao caminho que está no Editor Avançado como irá ver passo-a-passo em eseguida.

Crie uma descrição conforme abaixo para o local aonde colocará a sua pasta.

Mudar pasta ou mudar fonte de dados Power Query Excel 1

No exemplo temos dois caminhos:

  1. Caminho para uma pasta
  2. Caminho para um arquivo

Isso porque a técnica funciona tanto para pastas como para arquivos, sem problemas.

  1. Selecione o cabeçalho desta informação e também o caminho da pasta.
  2. Pressione ALT+T+T+A para criar uma tabela ou senão vá em Inserir->Tabela. Os dados serão transformados em tabela.
  3. Clique na tabela criada e em Dados->De Tabela/Intervalo
    Mudar pasta ou mudar fonte de dados Power Query Excel 5
  4. Os dados serão carregados no Power Query conforme acima.
  5. Clique com o botão direito sobre o texto na linha 1 e selecione a opção Fazer Drill Down conforme na imagem.
    Mudar pasta ou mudar fonte de dados Power Query Excel 7
  6. Esta informação será transformada então em texto dentro do Power Query, permitindo que seja facilmente utilizada como fonte dinâmica dentro do Power Query.
    Mudar pasta ou mudar fonte de dados Power Query Excel 6
  7. Altere o nome da conexão para algo que seja fácil de identificar como LocalArquivo no nosso exemplo, basta clicar na conexão e pressionar F2 e alterar o nome.
  8. Clique sobre a outra conexão, a que tem a carga de dados de todas as pastas de trabalho de uma pasta do Windows.
  9. Clique em Página Inicial->Editor Avançado. Irá ser exibida uma tela como esta:
    Mudar pasta ou mudar fonte de dados Power Query Excel 8
  10. No campo marcado em vermelho temos a fonte de informações que está em Folder.Files, é este caminho que precisamos mudar.
  11. Para deixar dinâmico substitua este caminho em vermelho pelo nome da conexão do Power Query que criamos anteriormente, LocalArquivo, muito cuidado porque é Case Sensitive, ou seja, os maiúsculos e minúsculos tem que ser respeitados.
    Mudar pasta ou mudar fonte de dados Power Query Excel 9
  12. Na imagem acima está a substituição do caminho pela conexão dinâmica.
  13. Agora pasta clicar em Página Inicial->Atualizar Visualização.
  14. Se deu tudo certo então a sua conexão do Power Query está agora ligada com o caminho do arquivo que está na tabela que definimos para tal anteriormente.

Para realizar o processo de alterar o caminho do arquivo no Power Query o processo é exatamente o mesmo, substitua apenas o caminho completo do arquivo ao invés da pasta somente neste outro caso.

Como Selecionar o Caminho de uma Pasta com VBA

Você pode incrementar a sua planilha deixando ela mais segura para que seja preenchida a informação do caminho da pasta de maneira correta no Excel.

  1. Selecione a célula aonde está o caminho da pasta e clique na guia Fórmulas->Definir nome e coloque o nome de local.
  2. Para isso pressione ALT+F11, para abrir o VBA.
  3. Em seguida clique em Inserir->Módulo.
  4. Cole o código VBA abaixo, ao ser acionado ele permite que seja selecionada uma pasta do Windows usando a sua API.
'Procedimento para selecionar arquivos
Sub lsSelecionarPasta()
    
    Dim fDlg As FileDialog
    Dim lArquivo As String

    'Chama o objeto passando os parâmetros
    Set fDlg = Application.FileDialog(FileDialogType:=msoFileDialogFolderPicker)
    With fDlg
        'Alterar esta propriedade para True permitirá a seleção de vários arquivos
        .AllowMultiSelect = False

        'Determina a forma de visualização dos aruqivos
        .InitialView = msoFileDialogViewDetails

        'Filtro de arquivos, pode ser colocado mais do que um filtro separando com ; por exemplo: "*.xls;*.xlsm"
        '.Filters.Add "Access", "*.accdb", 1

        'Determina qual o drive inicial
        .InitialFileName = Configuracao.Range("local").Value
    End With

    'Retorna o arquivo selecionado
    If fDlg.Show = -1 Then
        lArquivo = fDlg.SelectedItems(1)
        Configuracao.Range("local").Value = lArquivo
    Else
        MsgBox "Não foi selecionado nenhum arquivo"
    End If

End Sub
  • Agora ao lado da tabela Excel aonde está o local do arquivo insira uma imagem ou um botão.
  • Clique com o botão direito sobre o objeto criado, imagem ou botão.
  • E selecione a opção Atribuir macro e selecione a macro lsSelecionarArquivo.

Pronto, agora ao clicar no objeto a macro que seleciona o arquivo será acionada e ao selecionar a pasta o intervalo nomeado local será atualizado com o caminho completo do arquivo.

Mudar pasta ou mudar fonte de dados Power Query Excel 19

Como Selecionar o Caminho de um Arquivo com VBA

Assim como fizemos para selecionar a pasta do Windows utilizando VBA do Excel você pode fazer também para selecionar um arquivo.

  1. Selecione a célula aonde está o caminho da pasta e clique na guia Fórmulas->Definir nome e coloque o nome de LocalArquivo.
  2. Para isso pressione ALT+F11, para abrir o VBA.
  3. Em seguida clique em Inserir->Módulo.
  4. Cole o código VBA abaixo, ao ser acionado ele permite que seja selecionado um arquivo usando a API do Windows. Na configuração abaixo está para selecionar um arquivo Excel, do tipo XLSX, mas pode mudar para outros tipos de arquivo, basta mudar a linha Filter.add, conforme é explicado no vídeo no topo deste artigo.
'Procedimento para selecionar arquivos
Sub lsSelecionarArquivo()
    
    Dim fDlg As FileDialog
    Dim lArquivo As String

    'Chama o objeto passando os parâmetros
    Set fDlg = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
    With fDlg
        'Alterar esta propriedade para True permitirá a seleção de vários arquivos
        .AllowMultiSelect = False

        'Determina a forma de visualização dos aruqivos
        .InitialView = msoFileDialogViewDetails

        'Filtro de arquivos, pode ser colocado mais do que um filtro separando com ; por exemplo: "*.xls;*.xlsm"
        .Filters.Add "Excel", "*.xlsx", 1

        'Determina qual o drive inicial
        .InitialFileName = Configuracao.Range("Arquivo").Value
    End With

    'Retorna o arquivo selecionado
    If fDlg.Show = -1 Then
        lArquivo = fDlg.SelectedItems(1)
        Configuracao.Range("Arquivo").Value = lArquivo
    Else
        MsgBox "Não foi selecionado nenhum arquivo"
    End If

End Sub

Download

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha