How to create multiple folders?

[RET79]

Hi,

I have 49 different excel files and my boss wants me to put them each into a different folder on windows e.g. Run_1.xls into folder Run_1 etc.

I don't really know how to create multiple folders automatically so right now I am creating new folders one by one manually changing Run number everytime - very laborious.

I apologise for this not being a pure excel question.

RET79

 

[Nimrod]

What OS are you using ie 98,NT4, 2000, ?

 

[Nimrod]

I'm going to give you a batch file that I just wrote for you using W2K. This will work in W2k and probably nt4 . However the "for" command works differently in win9x and will not work if win9x is your OS.

Do the following to make this work:
1- Copy the following code
2- Paste code to notepad
3- Save the code to the directory that has all the xls files you want copied
4- MAKE SURE you save this file with the "bat" extention and not "txt".
5- if saved as txt then rename to "bat" IE MK_CP.bat
6- run the batch file by double clicking on it


::*************** copy following code to bat file***********

@echo off

:: make the directories to match file names
FOR /F "eol=# delims=. tokens=1,2*" %%1 IN ('dir /b *.xls') DO mkdir %%1

:: copy files to new directories
FOR /F "eol=# delims=. tokens=1,2*" %%1 IN ('dir /b *.xls') DO copy %%1.%%2 %%1


:: **************** end copy process here ***

 

[RET79]

Nimrod,

It's Windows NT.

Thanks for your code, but I am not sure what it will do??

RET79

 

[Nimrod]

I'm almost sure the code will work on NT.
What will this code do ?
The first "for" statement will create a subdirectory for every xls file found in the same directory as the bat file.
For example if you put the bat file in a directory with num1.xls , num2.xls and num3.xls it will create 3 sub directories called num1,num2,num3.
The Second "for" statement will then copy Num1.xls to the Num1 directory... and do the same for num2.xls > num2 dir etc etc.

Do you need more help on how to take this code and save it to a bat file ?
Do you need more help on how to run a bat file ?

 

[RET79]

Nimrod,

Many thanks, now I see what it does, great work! I'll let you know if I have any problems...

RET79

 

[Nimrod]

The "For" function in batch files allows you to parse an entire list or lines of data from another command and perform something with each line parsed.
It's kind of like array formulas in excel.

Lets take apart the following for command:
FOR /F "eol=# delims=. tokens=1,2*" %%1 IN ('dir /b *.xls') DO mkdir %%1

The section in brackets is where the for gets it list or array information from.
This could be a list in a file or a list generated by another command.
In this case we are using a dir command to generate a list of xls files in the current directory 'dir /b *.xls')

The delims=. tells the "for" statement to use periods in the incoming data as the data delimiter. This means the files name becomes variable #1 and the extention becomes Variable #2.

The "Do" section is followed by the command you want to execute , which is in this case a make directory command. i.e. Do mkdir %%1 , would tranlate into Do Mkdir Num1 , if there was a file called Num1.xls in the directory.
This message was edited by Nimrod on 2002-05-05 14:21

 

[Nimrod]

Hi Ret:
Did the script work for you ?