Creating folder structure from a template (Excel/VBA)

Posted by Albert Gareev on May 03, 2010 | Categories: Back-endFile System OperationsSource code

How to create a complex folder structure by using a template

In the previous post I provided code example that creates a folder and then applies security permissions on it. However, as per requirements, user folders often come with a predefined structure of folders and some files created by default. Sure, we can write script that creates folders one by one. But will it be a compact and maintainable solution?

Let’s try an alternative way.

We will use the following template as an example.

Root folder (will become user’s personal folder): “testuser”
Pre-defined user folders: “Application Data”, “Documents”
Files coming by default: “Application Data\readme.txt”, “Documents\readme.txt”

'Declare external function
'It will be used to pause execution during asynchronous calls
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim sTemplateFolder, sUserFolder
sTemplateFolder = "C:\Temp\Users\testuser"
sUserFolder = "C:\Temp\Users\DoeJohn"
'Create folder - through XCOPY
sCommandLine = "c:\windows\system32\XCOPY.EXE """ & sTemplateFolder & """ & " """ & sUserFolder & """ /E /I /H /K /O"
intRC = Shell(sCommandLine, vbHide)
'Shell is an asynchronous call - system needs time to process it
If boolRC Then
'code your recovery steps
End If


The Tool. XCopy is a Microsoft’s tool that comes with an operating system. Using the link provided you can learn more about it.

The Path. In the example provided the path is hard-coded. You can also make it configurable/customizable.

Command line. The following command line switches were used.

/E – Copy entire subdirectory structure, including empty ones.
/I – To specify that destination objects must be created (otherwise, the tool asks for a confirmation).
/H – Copy files with “hidden” and “system” attributes (the tool skips them by default).
/K – Retain “read-only” attribute on destination files if presented on the source files.
/O – Copy files/folders with security permissions as on the source files.


XCOPY_THINK_TIME must be defined considering the following factors: total size of a template structure, caching, network speed, and priority of your process. Worth to try copying a few times through command line manually to define a sufficient time for a delay.

If your template has a very complex structure or big in size you should check consistency of the created structure to ensure reliability of the automatic process.

Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported
This work by Albert Gareev is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported.