#Folder structure diagram excel code
You can download my Excel test file with the code here. ' if the end is the start of another folder at the level Click OK, and you will get the folder and subfolders path, directory, name, created date and last. Then press F5 key to run this code, and a Choose the folder window will pop out, then you need to select the directory that you want to list the folder and subfolder names, see screenshot: 4. If levelAtRow = level And Trim$(LCase$(r.Cells(fileFolderColumn).Value)) = "folder" Then VBA code: List all folders and subfolder names. We began by entering information using the wizard following the prompts by clicking Next> and choosing Excel. You can choose information that you enter using the wizard.
Set rngGroup = Range(rStart, rEnd) ' close the group There are two ways to use the Organization Chart Wizard: You can choose to create from information that’s already stored in a file or database. If levelAtRow rEnd.Row Then ' this takes care of empty folders LevelAtRow = UBound(Split(CStr(r.Cells(indexColumn).Value), ".")) + 1 ' determine level of current row based on number of tokens in index: ' set these values based on your worksheet:ĭebug.Print "Processing level " & CStr(level) & "."įor i = startRow To + 1ĪtEndRow = (i = + 1) ' close off last groups at end row Always back up your documents before running any code. Modern Legal Support provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. It determines the level of the current record by the number of tokens in the index string, then applies logic based on the current record’s relation to what has come before it in the loop, grouping at each level in the tree. “1.20” in a data room index means the 20th item under 1, but if your cell is formatted as General, Excel will treat it as a number and drop the insignificant zero.Īlso be aware that Excel gives you a maximum of 8 grouping levels, so this solution won’t cover grouping deeper levels than that. To allow this, click the dialog launcher under Data > Outline and uncheck “Summary rows below detail.” This is a worksheet-level setting, so it will stick with your sheet.Īnother issue to be aware of: Your index column should be formatted as text, so that, for instance, “1.20” does not come into Excel as “1.2” etc.
#Folder structure diagram excel plus
In this application, we want the plus signs above the group. Excel by default places the summary rows (i.e., the plus signs) below the detail, or group.
I came up with the solution using VBA code and one helpful setting in Excel. What the attorney wanted was to have the grouping “plus signs” at each folder level, grouping the files and subfolders below that folder. “1.1” may be a subfolder or a file under folder 1, etc.: The data room, as is typical, had an index column that used a string concatenated from integers to represent file and folder location. An attorney had an Excel index from a data room, and wanted to use Excel’s grouping feature to represent the file/folder structure. The other day at the office, I had an interesting request. Note: This solution was tested in Excel 20.