Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to create multiple folders?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ***

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nimrod,

    It's Windows NT.

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

    RET79

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nimrod,

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

    RET79

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ret:
    Did the script work for you ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •