Excel Tool : Folder Structure Creator

.

Dear readers,
In my previous article I posted an article about how to create folder in windows by Excel VBA code. That triggers me to build an Excel tool to create a hierarchical or Tree folder structure in any directory which is defined by you in excel sheet. Before I built this tool, I was thinking what is TOP 3 problem is it going to solve of my readers and here are the answers:

1. Creating folder structure in No time

No matter how complex and how deep folder structure is, it will get created in seconds. To create folder structure, click on the “Create Now>>” button to do so.

Replicate any existing folder structure

Suppose you want to replicate an existing folder structure in your PC. So to do this, you can even copy the root folder and paste it – but there is a major disadvantage of this method. You are copying the files as well with the folder structure. You need to delete them all manually – which is a very time consuming and boring task. Using this tool you can simply read any existing folder structure and then by modifying the first root folder name, you can click on “Create Now>>” and folder structure is replicated without copying any content of the folder structure.

Define your own Folder structure templates

In big organizations when folder structure is left to individual choice then it becomes really messy as every one will try to organize the structure in a way they like. At the end each one of us are forced to browse each and every folder to find your document, which is not efficient. Therefore, it is a good practice to maintain a common folder structure across organization for each projects.
In such case all you can define your folder structure according to your need and save them as a template. Here on whenever there is new project, you can select that template and create the same folder structure for the new project.

Rules to Define Folder Structure here in Excel

Mainly there are 3 rules:
1. Your first Root Node should start from Range B10 as highlighted in the above picture as point no 9
2. Any of the child node can not be stated in the same row in next column.
3. Any of the row should not be blank.

How to use the tool

1 and 2. Main Folder Path and Browse button

This is path where you want to create your folder structure. This path can be typed manually or it can be selected by clicking on Browse button.

3 and 4. Existing Templates drop down and Load template

As explained above, in this tool you can define your own template. As soon as you have defined your template, you can see the name your new template in this drop down. After selecting the drop down, you need to click on Load Template button in order to display your defined structure.

5. Validate button

Validate button checks if all these 3 rules are satisfied in your defined structure or not. Unless these rules are met, it will not allow you to create your folder structure.

6. List existing Structure

By clicking on this button you can get the folder structure which is there at your given main folder path. Once your structure is loaded here in this sheet, now you can provide a different folder path and click on Create Now >> to create / replicate the same folder structure.

7. Create Now >>

As the name suggests, this is button which does the magic of creation of the defined folder structure.

8. Messages

In this field you will see all the messages/errors which occurs during any of the above operations.

9 and 10 – Labels header and First Node

Row 9 is defined to have the level headers and Cell B10 (name range= startRange) is the starting point where your first node should be specified.

How to define a new template

Steps are defined and explained in the Excel workbook itself in the template sheet. Kindly read them before adding any new template.

Folder Structure Template

Folder Structure Template

About the VBA code used behind each buttons

I will try to explain about the VBA code which I have used to create this tool in my next article. Meanwhile you can read my previous article where I had explained how to list all the folders and sub folders from a given path. In fact I am calling the same function on clicking on List existing Structure button. see you till my next article and don’t forget to download this workbook 🙂

FREE Download

Here is your FREE copy of the Folder Structure creator excel tool. Download this, use it and do not forget to provide me your thoughts on it by typing your comment here or sending en email or you can twit me You can also share it with your friends colleagues and most importantly your Boss 😀

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

15 Comments

  1. Adrian D

    This is wonderful! Thank you so much!

    Reply
  2. JangBoo

    This is a fantastic tool, Vishwamitra!

    I made one change to your tool for my personal use by adding:

    Application.CutCopyMode = False

    right before the error hnadler in the loadTemplate sub.

    This is such a useful tool, and extremely well thought-out.
    Thank you for sharing!

    Reply
  3. Andy

    Vishwamitra Mishra, this tool is great and i thank you so much.

    I have one question though: is there any easy way for me to format my folders from an existing database query resultset? the results come out as a long list of full paths:
    c:\temp\folder1\
    c:\temp\folder1\ABC
    c:\temp\folder1\DEF
    c:\temp\folder2\abc
    c:\temp\folder2\def
    c:\temp\folder2\ghi

    is there any easy way to format these lines to suit your tool template structure?
    I can use text to cells but then i’m still left with all of the parent folders and there are thousands in the result set…

    some clues?

    Reply
  4. Jason

    I am getting an “Out of stack space” error.

    It validated my structure prior to the error…

    So it stops at about 107 folder

    Reply
  5. Rahmatullah Barkat

    It works! You should make a youtube explanation dude. I was watching several youtube before i found your link this on some forums. This is wonderful and easy. Thank you!

    Reply
  6. Darren

    It worked perectly! Many thanks 🙂

    Reply
  7. Thomas

    Great tool, thank you for sharing. Saved a lot of time. 🙂

    Reply
  8. LALITA BAPU VIBHANDIK

    Its good . But if my rootfolder is common and only sub folder is differant then it shows error

    Reply
  9. Sandeep Singh

    Hi, Hope you can help me, In my case, it stops after creating two folder structure. The error I get is ** Error Occured at: \\\ Lease. can you help, please?

    Reply
    • Sandeep Singh

      ** Error Description: Path not found

      Reply
  10. Steve

    Brilliant, thank you so much Vishwamitra. Over an hour of research and trying to get to grips with VBA which I’ve never used before – then found your page and after 10 minutes of making my file fit your validation rules, 5 seconds later and 150 folders are created!

    Reply
  11. Anis

    So wonderful….

    Reply
  12. Ian

    Thank you so much for this tool! I had a large deep folder structure to create, found a limit of the macro at ~ record 250ish so had to create 2 folders and put the 400+ folders together afterwards. Brilliant work and thanks for sharing this. Much appreciated.

    Reply
    • Vishwamitra Mishra

      Thanks Ian, you can share it with the world, it might help others too 🙂

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest