Obtenha uma lista de nomes de arquivos de uma pasta usando o Excel (com e sem VBA)

No meu primeiro dia em uma pequena empresa de consultoria, fui designado para um projeto de curto prazo de três dias.O trabalho é simples.

Existem muitas pastas em uma unidade de rede, cada uma com centenas de arquivos.

Tive que seguir estes três passos:

1. Selecione o arquivo e copie seu nome.
2. Cole o nome em uma célula no Excel e pressione Enter.
3. Vá para o próximo arquivo e repita as etapas 1 e 2.

Parece simples, certo?

É simples e muito demorado.

O que fiz em três dias pode ser feito em minutos se eu souber a técnica certa.

Neste tutorial, mostrarei diferentes maneiras de tornar todo o processo super rápido e super fácil (com e sem VBA).

Limitações dos métodos mostrados neste tutorial:Usando a técnica mostrada abaixo, você só poderá obter os nomes dos arquivos em sua pasta pessoal.Você não obtém os nomes dos arquivos em subpastas na pasta principal.Aqui está uma maneira de obter nomes de arquivos de pastas e subpastas usando o Power Query

Use a função FILES para obter uma lista de nomes de arquivos de uma pasta

Ouviu falar sobre issofunção ARQUIVOS?

Se você não fizer isso, não se preocupe.

É de uma planilha do Excel desde a infância (fórmulas da versão 4).

Embora essa fórmula não funcione com células de planilha, ela ainda funciona com intervalos nomeados.Usaremos esse fato para obter uma lista de nomes de arquivos da pasta especificada.

Agora, suponha que você tenha um arquivo em sua área de trabalho chamado " Pasta de Teste ” e você deseja obter uma lista de nomes de arquivos para todos os arquivos nessa pasta.

Aqui estão as etapas que fornecerão os nomes dos arquivos nesta pasta:

1. Na célula A1, insira o endereço completo da pasta seguido por um asterisco (*)
   • Por exemplo, se sua pasta estivesse na unidade C, o endereço seria algo como
     C:\Usuários\SEU NOME\Desktop\Pasta de Teste\*
   • Se você não tiver certeza de como obter o endereço da pasta, use o seguinte método:
     • • Na pasta da qual você deseja obter o nome do arquivo, crie uma nova pasta de trabalho do Excel ou abra uma pasta de trabalho existente na pasta e use a seguinte fórmula em qualquer célula.Esta fórmula lhe dará o endereço da pasta com um asterisco (*) no final.Agora você pode copiar e colar (colar como valor) esse endereço em qualquer célula da pasta de trabalho (A1 neste exemplo) onde deseja o nome do arquivo.

         =REPLACE(CELL("nome do arquivo"),FIND("[",CELL("nome do arquivo")),LEN(CELL("nome do arquivo")),"*")

         [Se você criou uma nova pasta de trabalho em uma pasta para usar a fórmula acima e obter o endereço da pasta, pode ser necessário excluí-la para que não fique na lista de arquivos dessa pasta]

2. Vá para a guia "Fórmulas" e clique na opção "Definir nomes".
3. Na caixa de diálogo Novo nome, use os seguintes detalhes
   • Nome: FileNameList (sinta-se à vontade para escolher qualquer nome que desejar)
   • Escopo: livro de exercícios
   • Refere-se a: =ARQUIVOS(Folha1!$A$1)
4. Agora, para obter uma lista de arquivos, usaremos um intervalo nomeado na função INDEX.Vá para a célula A3 (ou qualquer célula com a qual você deseja que a lista de nomes comece) e insira a seguinte fórmula:

   =SEERRO(INDEX(ListaNomeArquivo,ROW()-2),"")

5. Arraste-o para baixo e ele lhe dará uma lista de todos os nomes de arquivos na pasta

Quer extrair arquivos com uma extensão específica? ?

Se você deseja obter todos os arquivos com uma extensão específica, basta alterar o asterisco com essa extensão de arquivo.Por exemplo, se você deseja apenas o arquivo Excel, pode usar *xls* em vez de *

Portanto, o endereço da pasta que você precisa usar éC:UsersSumitDesktopPasta de teste*xls*

Da mesma forma, para arquivos de documentos do Word, use *doc*

Como é que isso funciona?

A fórmula FILES recupera os nomes de todos os arquivos com a extensão especificada na pasta especificada.

Na fórmula INDEX, damos os nomes dos arquivos como um array e usamos a função ROW para retornar o primeiro, segundo, terceiro, etc.

Note que eu useiLINHA()-2, já que começamos na terceira linha.Portanto, quando o número da linha for 4, ROW()-2 será 1 para a primeira instância, 2 para a segunda e assim por diante.

Obtenha uma lista de todos os nomes de arquivos de uma pasta usando o VBA

Agora, devo dizer que o método acima é um pouco complicado (há muitas etapas).

No entanto, é muito melhor do que fazer isso manualmente.

No entanto, se você estiver confortável usando o VBA (ou se for bom em seguir as etapas exatas que listarei abaixo), você pode criar uma função personalizada (UDF) que pode obter facilmente os nomes de todos os arquivos.

Benefícios de usar Funções Definidas pelo Usuário (UDFs)是Você可以將funçãoSalve em pastas de trabalho de macro pessoais e reutilize facilmente sem precisar repetir essas etapas várias vezes.Você também pode criar complementos e compartilhar essa funcionalidade com outras pessoas.

Agora, deixe-me primeiro fornecer o código VBA que criará uma função para obter uma lista de todos os nomes de arquivos de uma pasta no Excel.

Função GetFileNames(ByVal FolderPath As String) Como Variant Dim Resultado Como Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Definir MeusArquivos = MinhaPasta.Arquivos ReDim Resultado(1 Para MeusArquivos.Contagem) i = 1 Para Cada MeuArquivo em MeusArquivos Resultado(i) = MeuArquivo.Nome i = i + 1 Próximo MeuArquivo GetFileNames = Resultado End Function

O código acima criará uma função GetFileNames que pode ser usada na planilha (assim como uma função normal).

Onde colocar esse código?

Siga as etapas abaixo para copiar este código no editor VB.

Como usar esta função?

Aqui estão as etapas para usar esse recurso em uma planilha:

• Em qualquer célula, digite o endereço da pasta da qual você deseja listar os nomes dos arquivos.
• Na célula onde você deseja a lista, digite a seguinte fórmula (eu a digitei na célula A3):

  =SEERRO(INDEX(GetFileNames($A$1),ROW()-2),"")

• Copie e cole a fórmula na célula abaixo para obter uma lista de todos os arquivos.

Observe que digitei o local da pasta em uma célula e depois emObter nomes de arquivosEsta célula é usada em uma fórmula.Você também pode codificar o endereço da pasta na fórmula assim:

=SEERRO(INDEX(GetFileNames("C:\Users\SEU NOME\Desktop\Test Folder"),ROW()-2),"")

Na fórmula acima, usamos ROW()-2 e começamos com a terceira linha.Isso garante que quando eu copiar a fórmula na célula abaixo ela será incrementada em 1.Se você inserir a fórmula na primeira linha da coluna, poderá simplesmente usar ROW().

Como funciona essa fórmula?

A fórmula GetFileNames retorna uma matriz contendo os nomes de todos os arquivos na pasta.

A função INDEX é usada para listar um nome de arquivo por célula, começando com o primeiro.

A função SEERRO é usada para retornar em branco em vez de #REF!Erro exibido quando uma fórmula é copiada em uma célula, mas não há mais nomes de arquivo para listar.

Obtenha uma lista de todos os nomes de arquivos com uma extensão específica usando o VBA

A fórmula acima é útil quando você deseja obter uma lista de todos os nomes de arquivos de uma pasta no Excel.

No entanto, e se você quiser obter apenas o nome de um arquivo de vídeo, ou apenas um arquivo do Excel, ou apenas um arquivo que contenha uma determinada palavra-chave.

Neste caso, você pode usar uma função ligeiramente diferente.

O código abaixo permitirá que você obtenha todos os nomes de arquivos que tenham uma palavra-chave específica (ou uma extensão específica) neles.

Função GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Resultado As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Definir MyFiles = MyFolder.Files ReDim Result(1 para MyFiles.Count) i = 1 para cada MyFile em MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

O código acima irá criar uma função que pode ser usada na planilha" GetFileNamesbyExt " (assim como funções regulares).

Esta função recebe dois parâmetros - a localização da pasta e a palavra-chave da extensão.Ele retorna uma matriz de nomes de arquivos correspondentes à extensão fornecida.Se nenhuma extensão ou palavra-chave for especificada, ele retornará todos os nomes de arquivo na pasta especificada.

Sintaxe: =GetFileNamesbyExt("Localização da pasta","Extensão")

Onde colocar esse código?

Siga as etapas abaixo para copiar este código no editor VB.

• Vá para a guia do desenvolvedor.
• Clique no botão Visual Basic.Isso abrirá o editor VB.
• No editor VB, clique com o botão direito do mouse em qualquer objeto da pasta de trabalho em que você está trabalhando, vá em Inserir e clique em Módulo.Se você não vir o Project Explorer, use o atalho de teclado Control + R (segure o controle e pressione "R").
• Clique duas vezes no objeto do módulo e copie e cole o código acima na janela de código do módulo.

Como usar esta função?

Aqui estão as etapas para usar esse recurso em uma planilha:

• Em qualquer célula, digite o endereço da pasta da qual você deseja listar os nomes dos arquivos.Eu digitei isso na célula A1.
• Na célula, digite a extensão (ou palavra-chave) que você deseja que todos os nomes de arquivo sejam.Eu digitei isso na célula B1.
• Na célula onde você deseja a lista, digite a seguinte fórmula (eu a digitei na célula A3):

  =SEERRO(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")

• Copie e cole a fórmula na célula abaixo para obter uma lista de todos os arquivos.

E se vocêQuaisquer truques do Excel que você usa para simplificar sua vida.Eu adoraria aprender com você.Compartilhe na seção de comentários!