Obtenha a lista de nomes de arquivo de uma pasta no Excel (com e sem VBA)

Em meu primeiro dia de trabalho em uma pequena empresa de consultoria, trabalhei em um projeto curto por três dias.

O trabalho era simples.

Havia muitas pastas na unidade de rede e cada pasta continha centenas de arquivos.

Tive que seguir estas três etapas:

1. Selecione o arquivo e copie seu nome.
2. Cole esse 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?

Foi - Simples e uma enorme perda de tempo.

O que me levou três dias poderia ter sido feito em poucos minutos se eu conhecesse as técnicas corretas.

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

Limitações dos métodos mostrados neste tutorial: Com as técnicas mostradas abaixo, você só poderá obter os nomes dos arquivos da pasta principal. Você não obterá os nomes dos arquivos nas subpastas da pasta principal. Esta é uma maneira de obter nomes de arquivos de pastas e subpastas usando o Power Query

Usando a função FILES para obter uma lista de nomes de arquivo de uma pasta

Ouvi falar de Função FILES antes da?

Não se preocupe se você não fez isso.

É desde a infância das planilhas do Excel (uma fórmula da versão 4).

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

Agora, suponha que você tenha uma pasta com o nome - ‘Pasta de Teste‘Na área de trabalho, e você deseja obter uma lista de nomes de arquivos para todos os arquivos nesta pasta.

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

1. Na célula A1, digite o endereço completo da pasta seguido por um asterisco (*)
   • Por exemplo, se a sua pasta na unidade C, o endereço seria
     C: \ Users \ Sumit \ Desktop \ Test Folder \ *
   • Se você não tiver certeza de como obter o endereço da pasta, use o seguinte método:
     • • Na pasta da qual deseja obter os nomes dos arquivos, crie uma nova pasta de trabalho do Excel ou abra uma pasta de trabalho existente na pasta e use a fórmula abaixo em qualquer célula. Esta fórmula fornecerá o endereço da pasta e adicionará um sinal de asterisco (*) no final. Agora você pode copiar e colar (colar como valor) este endereço em qualquer célula (A1 neste exemplo) na pasta de trabalho na qual deseja os nomes dos arquivos.

         = REPLACE (CELL ("filename"), FIND ("[", CELL ("filename")), LEN (CELL ("filename")), "*")

         [Se você criou uma nova pasta de trabalho na pasta para usar a fórmula acima e obter o endereço da pasta, você pode querer excluí-la para que não apareça na lista de arquivos dessa pasta]
2. Vá para a guia ‘Fórmulas’ e clique na opção ‘Definir nome’.
3. Na caixa de diálogo Novo nome, use os seguintes detalhes
   • Nome: FileNameList (fique à vontade para escolher o nome que desejar)
   • Escopo: Livro de exercícios
   • Refere-se a: = ARQUIVOS (Folha1! $ A $ 1)
4. Agora, para obter a lista de arquivos, usaremos o intervalo nomeado dentro de uma função INDEX. Vá para a célula A3 (ou qualquer célula onde você deseja que a lista de nomes comece) e insira a seguinte fórmula:

   = IFERROR (INDEX (FileNameList, ROW () - 2), "")

5. Arraste para baixo e você verá uma lista de todos os nomes de arquivos na pasta

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

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

Portanto, o endereço da pasta que você precisa usar seria C: \ Users \ Sumit \ Desktop \ Test Folder \ * 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 da extensão especificada na pasta especificada.

Na fórmula INDEX, fornecemos os nomes dos arquivos como o array e retornamos o primeiro, segundo, terceiro nomes de arquivo e assim por diante usando a função ROW.

Observe que eu usei ROW () - 2, quando começamos a partir da terceira linha. Portanto, ROW () - 2 seria 1 para a primeira instância, 2 para a segunda instância quando o número da linha fosse 4 e assim por diante.

Assistir ao vídeo - obter uma lista de nomes de arquivos de uma pasta no Excel

Usando o VBA Obtenha uma lista de todos os nomes de arquivo de uma pasta

Agora, devo dizer que o método acima é um pouco complexo (com uma série de etapas).

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

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

A vantagem de usar um vocêSer Dafinado Função (UDF) é que você pode salvar a função em uma pasta de trabalho macro pessoal e reutilizá-la facilmente, sem repetir as etapas novamente e novamente. Você também pode criar um suplemento e compartilhar essa função com outras pessoas.

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

Função GetFileNames (ByVal FolderPath As String) As Variant Dim Result 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) Set MyFiles = MyFile.Files ReDim Result (1 para MyFiles.Count) i = 1 para cada MyFile em MyFiles Result (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Resultado Função final

O código acima irá criar uma função GetFileNames que pode ser usada nas planilhas (assim como funções regulares).

Onde colocar esse código?

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

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

Como usar esta função?

Abaixo estão as etapas para usar esta função em uma planilha:

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

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

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

Observe que eu inseri o local da pasta em uma célula e usei essa célula no GetFileNames Fórmula. Você também pode codificar o endereço da pasta na fórmula, conforme mostrado abaixo:

= IFERROR (INDEX (GetFileNames ("C: \ Users \ Sumit \ Desktop \ Test Folder"), ROW () - 2), "")

Na fórmula acima, usamos ROW () - 2 e começamos a partir da terceira linha. Isso garantiu que, à medida que eu copiava a fórmula nas células abaixo, ela aumentaria em 1. Caso você esteja inserindo a fórmula na primeira linha de uma coluna, pode simplesmente usar ROW ().

Como essa fórmula funciona?

A fórmula GetFileNames retorna uma matriz que contém os nomes de todos os arquivos na pasta.

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

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

Usando VBA Obtenha uma lista de todos os nomes de arquivo com uma extensão específica

A fórmula acima funciona muito bem quando você deseja obter uma lista de todos os nomes de arquivo de uma pasta no Excel.

Mas e se você quiser obter os nomes apenas dos arquivos de vídeo, ou apenas dos arquivos do Excel, ou apenas dos nomes dos arquivos que contêm uma palavra-chave específica.

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

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

Função GetFileNamesbyExt (ByVal FolderPath As String, FileExt As String) As Variant Dim Result 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) Set 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 ‘GetFileNamesbyExt'Que pode ser usado nas planilhas (assim como funções regulares).

Esta função leva dois argumentos - o local da pasta e a palavra-chave de extensão. Ele retorna uma matriz de nomes de arquivo que correspondem à extensão fornecida. Se nenhuma extensão ou palavra-chave for especificada, ele retornará todos os nomes de arquivo na pasta especificada.

Sintaxe: = GetFileNamesbyExt (“Local 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 Desenvolvedor.
• Clique no botão Visual Basic. Isso abrirá o Editor VB.
• No Editor VB, clique com o botão direito em qualquer um dos objetos da pasta de trabalho em que você está trabalhando, vá para Inserir e clique em Módulo. Se você não vir o Project Explorer, use o atalho de teclado Control + R (segure a tecla Control e pressione a tecla ‘R’).
• Clique duas vezes no objeto Módulo e copie e cole o código acima na janela de código do módulo.

Como usar esta função?

Abaixo estão as etapas para usar esta função em uma planilha:

• Em qualquer célula, digite o endereço da pasta da qual deseja listar os nomes dos arquivos. Eu inseri isso na célula A1.
• Em uma célula, insira a extensão (ou a palavra-chave) para a qual deseja todos os nomes de arquivo. Eu coloquei isso na célula B1.
• Na célula onde deseja a lista, insira a seguinte fórmula (estou inserindo na célula A3):

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

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

E quanto a você? Quaisquer truques do Excel que você usar para tornar sua vida mais fácil. Eu adoraria aprender com você. Compartilhe na seção de comentários!