Export Folder Structure to Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

In today’s digital age, businesses are increasingly relying on technology to streamline their operations. One important aspect of this is the organization and management of digital files, particularly when it comes to exporting and sharing data. In this article, we will explore the benefits of exporting folder structure to Excel and provide a step-by-step guide on how to export effectively.

overview of export folder structure to excel

After execution, you will see the folder structure enlisted in Excel like in the image.

Benefits of Exporting Folder Structure to Excel

There are several benefits of exporting folder structures to Excel, including:

  • Better organization and management of digital files
  • Easier tracking and analysis of file properties and attributes
  • Increased efficiency and productivity in file management tasks
  • Improved collaboration and communication among team members
  • Enhanced security and backup of important files and data
  • Greater flexibility and customization in data visualization and reporting

Export Folder Structure to Excel: 3 Suitable Methods

In this segment, we will discuss three different methods to export folder structure to Excel. We will use Power Query and Excel VBA to do the task. Without going to and fro, let’s move on to the procedures.

1. Export Folder Structure Using Power Query

To extract the folder structure, using Power Query is pretty straightforward and requires no formulas or code.

  • Firstly, select the Data tab > Get Data > From File > From Folder

using data tab for exporting folder structure

  • Then, locate and open the folder to enlist.

opening file location

  • Afterward, click on Transform Data.

transforming data to power query

  • Now, select Choose Columns > Choose Columns to enlist the desired data.

choosing columns

  • Further, select the desired columns. Press OK.

choosing selected columns

  • Lastly, select Close & Load > Close & Load.

loading data to worksheet

  • Finally, we will see the file name from the folder structure listed in the worksheet.

exported folder structure to worksheet

Read More: Export Folder and Subfolder List to Excel

2. Export Folder Structure from Doc File to Excel

This time, we go through a different process. We will list the file names of a folder in a doc file. Then, we will include the list in the Excel worksheet. Again, we will use Power Query.

  • Primarily, go to the desired folder location. On the address bar, type “cmd” and press Enter.

cmd in desired adress bar

  • Then, type “tree /a /f > output.doc” in the command prompt that appears. Press Enter.

command in cmd prompt

  • Now, we will see a doc file named doc in the folder.

output of command execution

  • Afterward, go to the Data tab > From Text.

importing data from text file

  • Later on, select the doc file from the All Files section. Open it.

loading text file

  • Further, right-click on the file and select Text.

open as text file

  • Lastly, select Close & Load > Close & Load.

close and load

  • Finally, you will see the folder structure exported in the worksheet.

exported folder structure from doc file

3. Applying VBA to Export Folder Structure

As we all know, almost every task in Excel can be done with VBA. We will enlist the folder structure using VBA now. Let’s do it.

  • Firstly, open the VBA window by pressing Alt + F11.
  • Then, insert a new module by selecting Insert > Module.

inserting new module

  • Afterward, write the following code in the module and run it by pressing F5.


Option Explicit
Sub Folder_Structure_VBA()
Dim PathSpec As String
PathSpec = "C:\ExcelDemy\"   'Specify a folder
If (PathSpec = "") Then PathSpec = SelectSingleFolder 'Browse Folder to select a folder
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")    'Late Binding
If (fso.FolderExists(PathSpec) = False) Then Exit Sub   'folder exist or not?
Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
Dim MySheetName As String
MySheetName = "Folder_Structure_VBA"   'Add a Sheet with name "Files"
AddSheet (MySheetName)
Dim FileType As String
FileType = "*"   '*:all, or pdf, PDF, XLSX...
FileType = UCase(FileType)
Dim queue As Collection, oFolder As Object, oSubfolder As Object, oFile As Object
Dim LastBlankCell As Long, FileExtension As String
Set queue = New Collection
queue.Add fso.GetFolder(PathSpec) 'enqueue
Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
For Each oSubfolder In oFolder.SubFolders   'loop all sub-folders
queue.Add oSubfolder 'enqueue
'...insert any folder processing code here...
Next oSubfolder
LastBlankCell = ThisWorkbook.Sheets(MySheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1 'get the last blank cell of column A
For Each oFile In oFolder.Files 'loop all files
FileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT
If (FileType = "*" Or FileExtension = FileType) Then
With ThisWorkbook.Sheets(MySheetName)
.Cells(LastBlankCell, 1) = oFile 'Path
.Cells(LastBlankCell, 2) = oFolder 'Folder
.Cells(LastBlankCell, 3) = oFile.Name 'File Name
End With
LastBlankCell = LastBlankCell + 1
End If
Next oFile
'Cells.EntireColumn.AutoFit  'Autofit columns width
Application.ScreenUpdating = True
End Sub
Function SelectSingleFolder()
'Select a Folder Path
Dim FolderPicker As FileDialog
Dim myFolder As String
'Select Folder with Dialog Box
Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPicker
.Title = "Select A Single Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
SelectSingleFolder = .SelectedItems(1)
End With
End Function
Function AddSheet(MySheetName As String)
'Add a worksheet with custom name
Dim Mysheet As Worksheet, F As Boolean
For Each Mysheet In ThisWorkbook.Worksheets
If Mysheet.Name = MySheetName Then
F = True
Exit For
F = False
End If
If Not F Then Sheets.Add.Name = MySheetName
'Add table header
With Sheets(MySheetName)
.Cells(4, 1) = "Path"
.Cells(4, 2) = "Folder"
.Cells(4, 3) = "File Name"
End With
End Function

code to export folder structure

Code Explanation:

  • In the code, we created a sub-procedure that used ObjectFileSystem to open the file directory.
  • We applied the For Loop and Do While Loop to get the subfolder names and respective file names.
  • We used a custom VBA function to open the folder and get its file names.
  • Also, we used another custom function to add a new worksheet and paste the file names on it.

exported folder structure with VBA

  • Finally, you will see the file names from the folder enlisted in a new worksheet, like in the image.

How to Export Specific Files (e.g. PDF) from Folder Using Excel VBA

So far, we have enlisted files of all types from the selected folder. Now, let’s see how to list PDF file names from a folder. We will do it with the help of VBA.

  • Primarily, open the VBA window by pressing Alt + F11.
  • Open a new module.
  • Then, use the following code in the module and run it by pressing F5.


Sub Folder_PDF()
Application.ScreenUpdating = False
Call GetFiles("C:\ExcelDemy\")      'end string with path separator ( \ )
End Sub
Private Sub GetFiles(ByVal path As String)
Dim fso As Object, Fldr As Object, subF As Object, file As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fldr = fso.GetFolder(path)
Dim i As Variant
i = 4
'Getting files from folder
For Each subF In Fldr.SubFolders
GetFiles (subF.path)
Next subF
'Checking file extension and putting them on worksheet
For Each file In Fldr.Files
If LCase(Right(file.path, 4)) = ".pdf" Then
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(i, 1).Resize(, 2) = Array(file.Name, Replace(file.path, file.Name, ""))
End If
i = i + 0.5
Next file
Set fso = Nothing
Set Fldr = Nothing
Set subF = Nothing
Set file = Nothing
End Sub

code to export pdf from folder

In the code, we used ObjectFileSystem to access the file from the folder. Then, we checked if the file extension was PDF; if that was true, we listed the file names in the worksheet.

exported pdf from folder

Finally, we will see the PDF file names from the selected folder listed in the worksheet.

Frequently Asked Questions

1. What is the purpose of exporting folder structures to Excel?

Ans: Exporting folder structures to Excel can help you better organize and manage your digital files. It allows you to view and analyze the hierarchy of your folders and subfolders, as well as their properties and attributes, in a more structured and customizable format. This can be useful for various purposes, such as inventory management, project planning, and data analysis.

2. How to export folder structures to Excel?

Ans: The process of exporting folder structures to Excel may vary depending on your operating system and file management software. However, the basic steps usually involve selecting the folder you want to export, choosing the export format and options, and specifying the destination file and location. The article provides a detailed guide on how to do this for Windows and macOS systems using various tools and methods.

3. Can we customize the exported folder structure in Excel?

Ans: Yes, once you have exported the folder structure to Excel, you can modify and format it as you wish. You can use Excel’s built-in tools and features, such as filters, sorting, and formulas, to manipulate the data and create different views and analyses. You can also add or remove columns, rows, and cells, and apply styles, colors, and formatting to make the structure more readable and visually appealing.

Things to Remember

  • Don’t forget to save the file as an xlsm file before running the code.
  • Be careful about the folder name. Change it in the code according to your needs.
  • We did a little editing on the worksheet. So after execution, your result will look a bit different.

Download Practice Workbook

You can download the practice workbook from here.


Exporting folder structures to Excel is a valuable tool for managing digital files, providing a clear and customizable overview of your hierarchy and properties. The benefits are significant for businesses dealing with large amounts of data. Follow the steps in this article to optimize your file management system and streamline your workflow. For any queries or suggestions feel free to comment.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF