Ir para o conteúdo principal

Como listar todas as pastas e subpastas no Excel?

Você já sofreu com esse problema de listar todas as pastas e subpastas de um diretório especificado em uma planilha? No Excel, não existe uma maneira rápida e prática de obter o nome de todas as pastas em um diretório específico de uma só vez. Para lidar com a tarefa, este artigo pode ajudá-lo.

Liste todas as pastas e subpastas com código VBA


seta azul bolha direita Liste todas as pastas e subpastas com código VBA

Se você deseja obter todos os nomes de pastas de um diretório especificado, o seguinte código VBA pode ajudá-lo, faça o seguinte:

1. Segure o ALT + F11 chaves, e abre o Janela Microsoft Visual Basic for Applications.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: lista todas as pastas e nomes de subpastas

Sub FolderNames()
'Update 20141027
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Choose the folder"
    .Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
    xRow = Range("A1").End(xlDown).Row + 1
    Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
Next SubFolder
For Each subfld In prntfld.SubFolders
    getSubFolder subfld
Next subfld
End Sub

3. Então aperte F5 chave para executar este código, e um Escolha a pasta janela aparecerá, então você precisa selecionar o diretório em que deseja listar os nomes da pasta e subpasta, consulte a captura de tela:

lista de documentos-nomes-de-pastas-1

4. Clique OK, e você obterá o caminho da pasta e subpastas, diretório, nome, data de criação e data da última modificação em uma nova pasta de trabalho, consulte a captura de tela:

lista de documentos-nomes-de-pastas-1


Artigo relacionado:

Como listar arquivos em um diretório para planilha no Excel?

Melhores ferramentas de produtividade de escritório

Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

guia kte 201905


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, I don't want or need it to show all that information nor the subfolders nor the filenames & I especially don't want it to create a new file altogether! What I need is simply just the top level folder names only in a non changing directory. Because unfortunately people at my work don't comprehend the need for uniformity in naming folders & also they move folders into an old job folder ruining any possible easy excel function links.
Example:
C:\temp\
>folder1
>folder2
>folder3
Etc. Etc. Etc...
Thats all I need.
The Way that apparently everyone on the internet wants; darn near crashes excel &/or my work PC trying to list every bit of over 15,000 instances of every single unwanted file & subfolder for this query.
There's only about 40 Give-Or-Take folders w/in the directory.
I already have a way of determining if files are w/in as example folder2 or not.
I am trying to automate this away from my current solution which was creating a folder w/ a text file for each, named the exact name of each folder. But if someone adds moves renames or deletes a folder this no longer fully works. AND I absolutely do not want to use command prompt.
I want & need either an excel function or vba option that I can use in conjunction w/ other excel functions i have &/or create w/in the same document (different sheets are fine).
This comment was minimized by the moderator on the site
Great work... It's very helpful for me, thanks very much
This comment was minimized by the moderator on the site
Thank you, this is exactly what we needed to record the folders for our clients.
This comment was minimized by the moderator on the site
Thank you very much for this usefull code. is that possible save result in same workbook not to new one ?
This comment was minimized by the moderator on the site
This worked and how... Thank you so much. Just one addition-- at Step 3, I had to click on Step Into and then only did the F5 key work for choosing the folder.
This comment was minimized by the moderator on the site
Thank you so much!, very useful.
This comment was minimized by the moderator on the site
Just what I needed, and perfectly clear instructions on how to make it work. Thanks a lot
This comment was minimized by the moderator on the site
Great Tool! After long research found this accurate toy :)
This comment was minimized by the moderator on the site
Hello. Can you please please help me on a code which I am struggling to find.

Below are the requirements for the code.



1. The VBA should go through all the folders and sub-folders
and check each and every type of file. The user should only give the path for
the top folder. The code should then check all the folders and sub folders
within the top folder.



2. After checking the files, the code should zip all files
which have not been accessed for more than 3 months. The accessed period is
something which I should be able to change in future if required. It should
allow me to change it to 1 month or 5 months if required.



3. After zipping the files, the code should delete the
original files which were zipped.



4. The zipped file should be saved in the same path as the
original file.
This comment was minimized by the moderator on the site
I modified it to add size:



Sub FolderNames()
'Update 20141027
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 6).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified","Size")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 6).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 6).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
xRow = Range("A1").End(xlDown).Row + 1
Cells(xRow, 1).Resize(1, 6).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified, SubFolder.Size)
Next SubFolder
For Each subfld In prntfld.SubFolders
getSubFolder subfld
Next subfld
End Sub
This comment was minimized by the moderator on the site
When you include the SubFolder.Size function the script no longer list all the subfolders, only the first level.
How can I include the size and get all subfolders listed?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations