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.
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.
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.
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.
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:
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.
No exemplo temos dois caminhos:
- Caminho para uma pasta
- Caminho para um arquivo
Isso porque a técnica funciona tanto para pastas como para arquivos, sem problemas.
- Selecione o cabeçalho desta informação e também o caminho da pasta.
- Pressione ALT+T+T+A para criar uma tabela ou senão vá em Inserir->Tabela. Os dados serão transformados em tabela.
- Clique na tabela criada e em Dados->De Tabela/Intervalo
- Os dados serão carregados no Power Query conforme acima.
- Clique com o botão direito sobre o texto na linha 1 e selecione a opção Fazer Drill Down conforme na imagem.
- 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.
- 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.
- Clique sobre a outra conexão, a que tem a carga de dados de todas as pastas de trabalho de uma pasta do Windows.
- Clique em Página Inicial->Editor Avançado. Irá ser exibida uma tela como esta:
- No campo marcado em vermelho temos a fonte de informações que está em Folder.Files, é este caminho que precisamos mudar.
- 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.
- Na imagem acima está a substituição do caminho pela conexão dinâmica.
- Agora pasta clicar em Página Inicial->Atualizar Visualização.
- 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.
- Selecione a célula aonde está o caminho da pasta e clique na guia Fórmulas->Definir nome e coloque o nome de local.
- Para isso pressione ALT+F11, para abrir o VBA.
- Em seguida clique em Inserir->Módulo.
- 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.
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.
- Selecione a célula aonde está o caminho da pasta e clique na guia Fórmulas->Definir nome e coloque o nome de LocalArquivo.
- Para isso pressione ALT+F11, para abrir o VBA.
- Em seguida clique em Inserir->Módulo.
- 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 planilhaCurso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
