Como Listar Arquivos de uma Pasta no Excel com VBA Automaticamente

Neste artigo, iremos tratar uma dúvida enviada por um dos nossos leitores.

O problema em questão é o seguinte, tem uma pasta cheia de outras pastas, aonde o nome dessas pastas é composto de um código identificador, mais o nome de uma pessoa.

O nosso leitor, precisa salvar em uma planilha cada nome da pasta, onde o código identificador irá em uma célula, e o nome irá em outra célula.

E ele tem pelo menos 10 mil pastas.

A ideia deste artigo é detalhar uma solução que possa resolver a demanda do nosso leitor.

Em resumo, a nossa solução será feita utilizando o VBA, e o código irá listar todos os arquivos e pastas presentes em um diretório.

E para cada nome de arquivo ou pasta, o script em VBA irá procurar pelo “_”, e o que estiver a esquerda, será considerado como o código, e o que está a direita será o nome.

Vídeo aula para listar arquivos e pastas no Excel

Assista ao vídeo abaixo, caso prefira aprender como fazer para listar arquivos e pastas em um diretório, e salvar a listagem em uma planilha utilizando VBA assistindo a um vídeo.

Não se esqueça de se INSCREVER no nosso canal. E coloque o seu melhor e-mail abaixo para receber todas as nossas novidades em primeira mão.

O que é FileSystemObject?

Antes de começarmos a colocar a mão na massa, precisamos explicar sobre o FileSystemObject, que será o principal responsável por permitir listar o que está dentro de um diretório.

O FileSystemObject permite acessar o sistema de arquivos do seu computador através do VBA, permitindo assim o VBA ir além do Excel.

Com ele que é possível acessar e alterar arquivos, pastas e diretórios no seu computador.

Segue abaixo alguns exemplos do que se pode fazer com o FileSystemObject:

• Verificar se um arquivo existe
• Verificar se uma pasta existe
• Listar todos os arquivos dentro de uma pasta
• Listar todas as pastas dentro de uma pasta
• Mover um arquivo de um local para outro
• Mover uma pasta de um local para outro

Agora, vamos começar a implementar a nossa solução.

Criar pastas e arquivos

Vamos criar 3 pastas, 1 arquivo de texto e 1 planilha. As 3 pastas irão se chamar:

• 1_ROGER
• 2_RODRIGO
• 3_ANA

O arquivo de texto irá se chamar 4_ADRIANO.txt e a planilha 5_FELIPE.xlsx.

Abrir o ambiente do VBA

Para podermos utilizar o VBA, primeiramente precisamos abrir o ambiente do VBA no Excel.

Para isso, vá na aba “Desenvolvedor” e clique em “Visual Basic”.

Ao clicar em “Visual Basic” uma nova janela irá se abrir. Essa nova janela é o ambiente do VBA.

Para podermos começar a escrever o código que será utilizado precisamos criar um “Módulo”.

1. Clique com o botão direito em “VBAProject (Pasta1)”. Uma nova janela irá aparecer.
2. Clique em “Inserir”
3. Clique em “Módulo”

Ao criar um “Módulo”(1), o fundo cinza que estava na parte direita da tela, será substituído por uma janela(2), e é nessa janela que iremos escrever o nosso código.

Versão 1 – Listar arquivos e pastas

Nessa primeira parte da solução iremos somente listar os arquivos e pastas de um diretório.

Sub listar_arquivos_e_pastas() 
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim linha As Integer    
    linha = 1    
    Set fso = CreateObject("Scripting.FileSystemObject")   
    Set folder = fso.GetFolder("C:\vba_listar_arquivos\")  
    For Each file In folder.subfolders    
        Cells(linha, 1) = file.Name    
        linha = linha + 1    
    Next file    
    For Each file In folder.Files    
        Cells(linha, 1) = file.Name     
        linha = linha + 1
    Next file
End Sub

1. Dá linha 2 a 5 estamos declarando as variáveis que serão utilizadas.
2. Na linha 6, a variável “linha” será atribuída o valor 1. Essa variável que será utilizada para informar ao VBA qual a linha do Excel que deverá ser utilizada para armazenar a informação.
3. A linha 7 e 8 são as mais importantes. São elas que vão permitir listar os arquivos e pastas dentro de um diretório. Na linha 7 o FileSystemObject será armazenado na variável “fso”. E na linha 8, a variável “fso” será utilizada para acessar as informações de uma pasta específica, no nosso caso o C:\vba_listar_arquivos\, utilizando a função GetFolder e irá armazenar o resultado na variável “folder”.
4. Da linha 9 a 12, a variável “folder” irá utilizar a função “subfolders” e listará as pastas dentro do diretório C:\vba_listar_arquivos\. O loop For Each será utilizado para acessar cada uma das pastas e irá salvar o nome da pasta na célula do Excel que está na coluna 1 e na linha referente a variável “linha”.
5. Da linha 13 a 16, o processo será o mesmo realizado da linha 9 a 12, só que ao invés de listar pastas dentro do diretório, serão listados os arquivos dentro do diretório.

Rode o script acima. A sua planilha deverá estar da seguinte forma:

Concluímos a primeira versão, aonde todos os arquivos e pastas de um diretório foram listados e salvos em uma planilha.

Agora vamos para a segunda parte, aonde iremos quebrar o nome da pasta ou arquivo em duas partes.

O que estiver antes do “_” será o código identificador e o que estiver depois do “_” será o nome da pessoa.

Versão 2 – Listar arquivos e pastas, e manipular os seus nomes

Nesta parte do artigo iremos mostrar duas versões.

A primeira irá incluir, tudo que estiver a direita do “_”, incluindo a extensão do arquivo.

Diferente da primeira versão, a segunda versão não irá incluir a extensão do arquivo.

Para ambas as versões, será necessário utilizar duas funções do VBA, o InStr e o Mid.

A função InStr irá buscar em uma cadéia de caracteres pela primeira posição que se encontra uma outra cadeia de caracteres.

Caso a cadeia de caracteres desejada não seja encontrada, a função irá retornar o valor zero(0).

Já a função MID irá extrair de dentro de uma cadeia de caracteres, uma quantidade X de caracteres que comecem na posição Y.

Caso a quantidade de caracteres para serem extraidos não seja informado, a função irá pegar todos os caracteres, a partir da posição Y.

Em outras linguagens de programação, a função MID é muito parecida com a função SUBSTRING.

Vamos agora aos scripts:

Sub listar_arquivos_e_pastas()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim linha As Integer
    Dim posicao_inical As Integer
    linha = 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\vba_listar_arquivos\")
    For Each file In folder.subfolders
        posicao_inical = InStr(file.Name, "_")
        If posicao_inical > 0 Then
            Cells(linha, 1) = Mid(file.Name, 1, posicao_inical - 1)
            Cells(linha, 2) = fso.GetBaseName(Mid(file.Name, posicao_inical + 1))
        Else
            Cells(linha, 1) = file.Name
        End If
        linha = linha + 1
    Next file
    For Each file In folder.Files
        posicao_inical = InStr(file.Name, "_")
        If InStr(file.Name, "_") > 0 Then
            Cells(linha, 1) = Mid(file.Name, 1, posicao_inical - 1)
            Cells(linha, 2) = fso.GetBaseName(Mid(file.Name, posicao_inical + 1))
        Else
            Cells(linha, 1) = file.Name
        End If
        linha = linha + 1
    Next file
End Sub

Foram feitas alterações nas linhas 6, 11 a 17, e 21 a 27.

• Na linha 6, adicionamos mais uma variável ao nosso script.
• Na linha 11, o script irá buscar pelo “_” usando a função InStr, e irá atribuir a variável posicao_inical a posição que se encontra o “_” ou o valor zero, caso não seja encontrado.
• Na linha 12 será verificado se o valor atribuido a variável posicao_inicial é maior que zero. Caso positivo, o script irá entrar dentro do IF, caso contrário irá para o Else, que está na linha 16.
• Na linha 13 será utilizado a função Mid, aonde irá buscar no nome do arquivo, a partir da posição 1, por (posicao_inicial – 1) caracteres, que dará por tudo que estiver antes do “_” e salvará na célula do Excel que está na coluna 1 e na linha referente a variável “linha”.
• Já na linha 14, a função Mid será utilizada novamente, porém de uma forma diferente da linha 13, a função irá extrair todos os caracteres a partir da posicao (posicao_inicial + 1). Ou seja, tudo que estiver a direita do “_” será extraído, e será salvo na célula do Excel que está na coluna 2 e na linha referente a variável “linha”.

Ao rodar o script acima, a sua planilha deverá apresentar os seguintes dados:

Agora vamos para a segunda versão do script acima, aonde os nomes serão salvos sem a extensão do arquivo. E para isso, iremos utilizar o script abaixo:

Sub listar_arquivos_e_pastas()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim linha As Integer
    Dim posicao_inical As Integer
    linha = 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\vba_listar_arquivos\")
    For Each file In folder.subfolders
        posicao_inical = InStr(file.Name, "_")
        If posicao_inical > 0 Then
            Cells(linha, 1) = Mid(file.Name, 1, posicao_inical - 1)
            Cells(linha, 2) = fso.GetBaseName(Mid(file.Name, posicao_inical + 1))
        Else
            Cells(linha, 1) = file.Name
        End If
        linha = linha + 1
    Next file
    For Each file In folder.Files
        posicao_inical = InStr(file.Name, "_")
        If InStr(file.Name, "_") > 0 Then
            Cells(linha, 1) = Mid(file.Name, 1, posicao_inical - 1)
            Cells(linha, 2) = fso.GetBaseName(Mid(file.Name, posicao_inical + 1))
        Else
            Cells(linha, 1) = file.Name
        End If
        linha = linha + 1
    Next file
End Sub

Nessa versão do script, as alterações foram feitas nas linhas 14 e 24.

Nessas linhas foi acrescetado a função “GetBaseName” do objeto “fso”.

Essa função irá retornar o nome da pasta ou arquivo, sem a extensão.

Ao rodar o script acima, a sua planilha deverá apresentar os seguintes dados:

Compartilhe, Comente e SE INSCREVA

Chegamos ao final do artigo.

Se você curtiu esse tutorial, se inscreva para receber o nosso curso gratuito de VBA, assim como as nossas novidades.

E se tiver alguma dúvida, não esqueça de responder o nosso e-mail que você receberá ao confirmar a sua inscrição.