Export Folder Structure to Excel (3 Suitable Ways)

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.

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

• Then, locate and open the folder to enlist.

• Afterward, click on Transform Data.

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

• Further, select the desired columns. Press OK.

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

• Finally, we will see the file name from the folder structure listed in the 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.

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

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

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

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

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

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

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

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.

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

Code:

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
Loop
'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
Sheets(MySheetName).Cells.Delete
F = True
Exit For
Else
F = False
End If
Next
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

• 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.

Code:

Sub Folder_PDF()
Application.ScreenUpdating = False
'ActiveSheet.Cells.Clear
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

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.

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.

Conclusion

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.

<< Go Back to Power Query Get Data from Folder | Power Query Excel | Learn Excel