Generating folder structures

I have a folder name generator which creates a list of file paths. For example:

The spreadsheet is saved in the "Live" folder. I now need a macro that can generate the following folders and sub-folders without overwriting the folder if it already exists.

All suggestions gratefully received. Many thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nadav SolomonCommented:
I don't understand why Excel and vbscript but there you go:
xlsFile = "C:\Scripts\New_users.xls"
row2Start = 1

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(xlsFile)
Set fso = WScript.CreateObject("Scripting.Filesystemobject")

Do Until objExcel.Cells(row2Start,1).Value = ""
	If Not fso.FolderExists(objExcel.Cells(row2Start, 1).Value) Then
		fso.CreateFolder(objExcel.Cells(row2Start, 1).Value)
	End If 
    row2Start = row2Start + 1


Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
The problem with doing this in VBS or VBA is that it won't create any missing parent folders automatically.  So if you try to create "C:\A\B\C" and "C:\A\B" doesn't already exist it fails.

On the other hand, in a BAT script, the MD command will create all needed parent folders, making it a bit more useful.

If this is a one time need then I would suggest just exporting the column of folderpaths to a text file, and then we can easily do a BAT script that reads it and creates each folder.  Something like:

@echo off
for /f "usebackq tokens=*" %%A in ("folderlist.txt") do md "%%~A"

Open in new window

If this is something you need to use over and over again, then there are VBS and VBA routines that can explode the path and create any needed parent folders along the way to the lowest directory, but that requires a bit more code.  If you think you need that I can dig it up for you.

x5225642Author Commented:
Thank you Bill. Yes if you wouldn't mind could you have a look for that code for me please.

This is something that will need to be run on a regular basis due to the nature of my work. It may seem excessive but it is vital that I keep everyone's file and folder names standardised for my other macros in the system to function correctly.

Thank you again for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
Here is a prior question solution that used a subroutine for this in VBA.'s-date.html#a40924603

Do you want the VB in Excel VBA or stand alone VBS script?

Can you share a little more about how you see this all working?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.