Batch File Creation

[HangMan]

I have a list of around 3,000 codes in the format XZ0123. I need to create a batch file (.bat) for each code to allow me to create a main folder each with a number of sub folders (for Windows 7). The final file path, number of sub folders in each folder and folder names have yet to be determined.

The main folder will be called by the code name, e.g.

Main File
XZ0123

Sub Folders
Folder 1
Folder 2
Folder 3
Folder 4
Folder 5

The aim is to give me this for each of the 3,000 codes

batch.jpg

I'm trying to find a way to set this up but am struggling. I'm working on the basis of somehow being able to set up a file with several columns which I can then concatenate to give me the above.

The challenge is how to set this up for every code in the list because each code wil be repeated on several rows. Some columns are consistent e.g. column A, B, C, E, and G, others are not e.g. column F though it does follow a consistent pattern, (see the attached file). For each code on the list (on the 'Codes' tab), I need to be able to create the format shown on the 'Batch File' tab. Note column A and D are the only entries when setting up the folder (i.e. the first row for each code occurance) and all columns are needed to create the sub folders.

Once set up I can simply use Find and Replace when the file path and folder names have been defined.

The key here is that the number of sub folders could either increase or decrease, so I need a formula (or code) which can easily be adjusted that considers this.

Many thanks

[JohnTopley]

VBA code ......

Please Login or Register  to view this content.

[HangMan]

John,

That is truly outstanding and works like a charm. That is seriously going to save many many hours, thank you so much...

I would add 5 stars if the forum let me...

Many thanks once again, I never cease to be amazed...

[JohnTopley]

Thank you for the feedback.

Could you please mark thread as SOLVED ("Thread Tools" at top of post).

[HangMan]

Have done, I'm having endless problems with the forum this weekend, it is down every few minutes over here, so I can only access it intermittently...

[HangMan]

Hi John,

I don't know if it is possible to add a small additional functionality to the code. The 'Codes' all have a 5 character format, the first letter will always be 'X', the second character will be the letters A through to Z and the remaining three characters will be alphanumeric (e.g. XA020, XB02A and so on), so the 'key' is the second character, the letters A to Z.

It has been decided to split all the codes into four regions as follows:

XA to XI Codes = North
XJ to XQ Codes = East
XR to XS Codes = South
XT to XZ codes = West

So instead of creating a batch file that puts everything into the same folder, I now need it to create four folders named, North, South, East and West and then create all the the folders and sub folders with the code ranges above in the appropriate folders?

Can this be done?

Many thanks

[JohnTopley]

So what should the file path be?

md "C:\Client Jobs\Test\North\Folder1" ???

md "C:\Client Jobs\Test\North\XA0123\Folder1" ???

[HangMan]

Okay, for the sake of argument...

All the

XA to XI Codes would be

md "C:\Client Jobs\Test\North\"

resulting in

C:\Client Jobs\Test\North\XA0123\
C:\Client Jobs\Test\North\XB0234\
...
C:\Client Jobs\Test\North\XI546\

and so on..


XJ to XQ Codes would be

md "C:\Client Jobs\Test\South\"

resulting in

C:\Client Jobs\Test\South\XJ0123\
C:\Client Jobs\Test\South\XK0234\
...
C:\Client Jobs\Test\South\XQ546\


XR to XS Codes would be

md "C:\Client Jobs\Test\East\"

resulting in

C:\Client Jobs\Test\East\XR0123\
C:\Client Jobs\Test\East\XS0234\


and so on..


XT to XZ Codes would be

md "C:\Client Jobs\Test\West\"

resulting in

C:\Client Jobs\Test\West\XT0123\
C:\Client Jobs\Test\West\XU0234\
...
C:\Client Jobs\Test\West\XZ546\


I hope that helps...

[JohnTopley]

No folder1 to folder5?

Codes given in last response are not same as those given initially! which are correct?


XA to XI Codes = North
XJ to XQ Codes = East
XR to XS Codes = South
XT to XZ codes = West

[HangMan]

Sorry, yes, folders 1 to 5 are needed as before... with the option to change the number of folders as you can do currently.

md "C:\Client Jobs\Test\North\XA0123\Folder1"
md "C:\Client Jobs\Test\North\XA0123\Folder2"
md "C:\Client Jobs\Test\North\XA0123\Folder3"
md "C:\Client Jobs\Test\North\XA0123\Folder4"
md "C:\Client Jobs\Test\North\XA0123\Folder5"

[JohnTopley]

New code: need to check which code fit which region as you gave 2 conflicting sets of results.

Please Login or Register  to view this content.

[HangMan]

Apologies for any confusion, let me test and I'll come back to you...

Many thanks for such a quick reply...

[HangMan]

Okay, I'm guessing this is down to me not making things so clear (apologies for that). Running the code is creating the same folders both in the root folder and also in the regional folders, I only need the folders to appear in the regional folders.

So this is what it is currently doing:

Bad.jpg

and it only is needs to be doing this...

Good.jpg

Do I need to comment out certain parts of the code?

[JohnTopley]

I don't understand how got the first example: it doesn't do that for me. i.e East/North and South/West are not "combined". My output is as you have in your example 2.

XA0123 does not appear in North AND West.

This is what is produced ... so if it is wrong what do you want

md XA0123
md "C:\Client Jobs\Test\North\XA0123\Folder1"
md "C:\Client Jobs\Test\North\XA0123\Folder2"
md "C:\Client Jobs\Test\North\XA0123\Folder3"
md "C:\Client Jobs\Test\North\XA0123\Folder4"
md "C:\Client Jobs\Test\North\XA0123\Folder5"

md XA0124
md "C:\Client Jobs\Test\North\XA0124\Folder1"
md "C:\Client Jobs\Test\North\XA0124\Folder2"
md "C:\Client Jobs\Test\North\XA0124\Folder3"
md "C:\Client Jobs\Test\North\XA0124\Folder4"
md "C:\Client Jobs\Test\North\XA0124\Folder5"

md XR0140
md "C:\Client Jobs\Test\South\XR0140\Folder1"
md "C:\Client Jobs\Test\South\XR0140\Folder2"
md "C:\Client Jobs\Test\South\XR0140\Folder3"
md "C:\Client Jobs\Test\South\XR0140\Folder4"
md "C:\Client Jobs\Test\South\XR0140\Folder5"

[HangMan]

Hi John,

That is exactly what the code is producing, but if I save it as a .bat (batch) file and run it, it creates four folders, North, South, East and West within the 'C:\Client Jobs\Test\' folder and inside each folder it creates the correct subfolders but in addition to the North, South, East and West folders it is also duplicating the subfolders at the 'C:\Client Jobs\Test\' root level as well.

So this just shows one code per region and the result...

sample.jpg

so you can see it creates the four folders, each with the correct subfolders, but in addition it creates each subfolder at the root level as well... So for North it creates both

C:\Client Jobs\Test\North\XA0131\Folder1" and C:\Client Jobs\Test\XA0131\Folder1"

so I was unsure if I'm missing something in the code that needs to be commented out or something as you said 'check which code fit which region as you gave 2 conflicting sets of results'

I hope that makes sense...

[HangMan]

Okay, I figured it out, I just commented out this line and all is working as expected now:

Please Login or Register  to view this content.

Many thanks once again for you help and super quick reply...

[JohnTopley]

Glad you managed to get it sorted as I have no experience of using BAT files.

Hopefully you will be able to re-use/modify the macro should the needs change: it is fairly straightforward.

And thanks for the feedback.