+ Reply to Thread
Results 1 to 15 of 15

macro to create folder and subfolder and file inside subfolder each one with specific name

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    macro to create folder and subfolder and file inside subfolder each one with specific name

    Dear Sir
    i would like to create macro that will do the following action:
    1- macro to create folder with specific name (this name will be present in Column A for Example ) and if this folder is present give me message that the folder is present .

    this example as the follow

    Column A1 have product name : XYT
    the macro will create folder with name XYT


    another macro that make same action but with subfolder creation and file
    2- macro to create folder with specific name (this name will be present in Column A for Example ) when i enter data in Column A and if this folder is present give me message that the folder is present then create subfolder inside this folder created before (which related to product ) with specific name Present in Column E for example when i enter any data in Column E then will copy one sheet (specific name for example Sheet1) and put in the subfolder


    This example as the follow

    column A1 have product name : ZSG and Column E1 have B#152214
    this will create folder with name ZSG then Subfolder with name B#152214 and if in any other Cell in column A have the same product but in E contain other number For Example B#152615 will not create ZSG folder but will create subfolder with B#152615 name

    then copy sheet1 an put in this subfolder

    hope my explanation is clear

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Create Folders and Sub Folders from Worksheet Columns list. EP mazan Dik Collection

    Good Day, mazan

    There are, I believe, many ways to check for and Create Folders and Sub Folders.


    This may help get you started: ( The code is not too efficient, as there are lots of extra and unnecessary steps and variable usage, but this should aid you to understand better what is going on as well as aid in later modification to meet your exact requirements )

    1- macro

    This will work , for example, on this data_....

    Using Excel 2007 32 bit
    Row\Col
    A
    1
    XYT
    2
    XTC

    _....in the First Worksheet of the File in which the Code is, and it will make you two Folders with the names XYT and XTC in the same Folder in which The File in which the code is in, or it will evoke a message box to tell you if the Folders already exist





    1-macro:

    Note
    Line 80 This sets the string of the Full Path up to your main Folders. You will need to adjust this to suit where you want to store the main Folders

    Please Login or Register  to view this content.


    _...........................................

    2-macro is in discussed in next post.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    EP. You “Shook” Me All Night Long. 2-macro

    2-macro
    It is not totally clear to me what you want.

    I will try to guess what you want, and as before give full ' EP 'explanations so that you may modify to your exact requirements. Again there will be many unnecessary and duplicated sections.

    The actual code is given here:
    http://www.excelforum.com/showthread...29#post4475000
    and here:
    http://www.excelforum.com/showthread...t=#post4475062
    Note: it is all one Code ( I had to split it due to Forum Post size limits ) The second part of the code should be copied directly under the first part in the same code module.


    I tested it on this : ( while _ ........ listening to this
    https://www.youtube.com/watch?v=Lo2qQmj0_h4 _...all FuKing Night Long
    ..)
    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    1
    XYT B#152214
    2
    ACDC You Shagged Me All Night Long
    3
    XYT
    4
    XYT B#152615
    Tabelle1
    _.............................

    What the code does:


    After running the code, based on the above data, you end up with two main Folders named
    XYT
    and
    ACDC

    In XYT you end up with two Sub Folders name
    B#152214
    And
    B#152615

    In ACDC you end up with one Sub Folder named
    You Shagged Me All Night Long

    At all points the existence of the Folder is first checked. If it exist you are told that in a message box. If it does not then it is made.
    _.....................

    Here is a brief description of the code:

    10 Rem 1 ) Workbooks info and worksheets and date referencing
    Line 80 This sets the string of the Full Path up to your main Folders. You will need to adjust this to suit where you want to store the main folders

    Rem 2) Uses the Keys of a Microsoft Scripting Runtime Dictionary to produce finally a 1 dimensional Array_...
    760 arrKeys()
    _....which contains a list of unique values from Column A

    780 Rem 3) This is a basic repeat of 1-macro making the main Folders

    890 Rem 4) Sub Folders

    900 ' 4a) A Collection is made of a concatenation of values in columns A and E ( ignoring rows with any empty Cell in column A or column E). In the tested example this will be
    XYT B#152214
    AC/DC You Shagged Me All Night Long
    XYT B#152615


    980 ' 4b) This produces a 1 dimensional Array of concatenated Strings _...
    1020 arrConcat()
    -... from that list. In the example we have
    XYT B#152214 , ACDC You Shagged Me All Night Long , XYT B#152615


    1050 ' 4c) An Outer loop_...
    1080
    _.... goes loops through for every Unique value from Column A, using the arrKeys()

    1120 Produces a 1 Dimensional Array from arrConcat() _..
    arrSubFoldersInFolder()
    _.....containing only those entries that include the current Unique value

    1130 An Inner loop_....
    1140
    _.... goes through for every value in the arrSubFoldersInFolder() and the name for the Sub Folder is produced by_...
    1160
    _..... removing the current Unique value from the concatenated String

    Lines 1180 – 1240 complete the inner loop by Producing the Sub Folder if it does not exist.

    1250 Repeat Outer loop for a new product Name, ( obtained from Unique Product Name List made from Column A )


    Alan




    https://www.youtube.com/watch?v=Lo2qQmj0_h4
    http://www.snb-vba.eu/VBA_Collection_en.html
    http://www.snb-vba.eu/VBA_Dictionary_en.html




    WillyWonks.JayPeaGee( mazan Diks )
    WillyWonks.JPG
    https://www.youtube.com/watch?v=Lo2qQmj0_h4
    https://www.youtube.com/watch?v=ivFYVAntpw0
    https://www.youtube.com/watch?v=Q8fZeaUHsjw
    Last edited by Doc.AElstein; 09-18-2016 at 05:41 AM.

  4. #4
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    Re: EP. You “Shook” Me All Night Long. 2-macro

    dear Doc.AElstein
    would you please upload example for each

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Give it on a Plate ... and ... get the Spoon Ready ;)

    Quote Originally Posted by mazan2010 View Post
    dear Doc.AElstein
    would you please upload example for each
    Hi Mazan,
    Codes are in Worksheet Codes module for Worksheet "Tabelle1" in uploaded File
    Alan
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    it not working for me i didnt know why

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Here we go ... again. .. Got the Spoon and me Dik Out

    Hello Mazan

    Quote Originally Posted by mazan2010 View Post
    it not working for me i didnt know why

    I not know what your problem is. It do Wonk here good

    I do guess try maybe.

    Here File give first do. 1-macro only is
    Sub mazan1_macro()

    Try this you now

    _1 ) You Open File ( “MazanDikCollectionWonk.xlsm”) ( -- Enable macros -- Yes please I do say – OK -- )

    _2 ) Run Code ( Only one there is )
    Sub mazan1_macro()

    _3 ) Now tell me what error you get

    Alan
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    now its working but
    i would like to when i put any data in column A create folder automatic not every time must goto run macro

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    Quote Originally Posted by mazan2010 View Post
    now its working but
    i would like to when i put any data in column A create folder automatic not every time must goto run macro
    Then you need a Worksheets change code..

    Code must go in a Worksheets Change Code in a Worksheet Code Module.

    You try first.
    For example:
    http://www.globaliconnect.com/excel/...=79&Itemid=475

    Then I help later or tomorrow if you cannot do it

    Alan

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    Hi Mazan

    The modification to make the codes “automatic” is fairly simple

    As example , consider the first code I gave you
    Sub mazan1_macro()

    Currently ..
    _.. you add values in column A , and then run the code. .. “..every time must goto run macro..
    but
    Quote Originally Posted by mazan2010 View Post
    ...
    I would like to when I put any data in column A create folder automatic not every time must goto run macro
    Then we want do code to run is when goto put any data in column A

    VBA do know when you put data goto is in column A, by type data in + carriage return ( ENTER is carriage return )

    VBA do know when you put data goto is in column A because value in a cell in column A do change , don’t it ?

    VBA do have “Worksheet_Change” code is Worksheet_Change Event, Excel VBA,
    http://www.globaliconnect.com/excel/...=79&Itemid=475
    and intersect is typically used with it
    http://www.ozgrid.com/VBA/vba-intersect.htm

    _......

    File “MazanDikCollectionWonk.xlsm” you do have, ( Post #7 I you give did ) do have normal code module, Modul1 with Sub mazan1_macro() in it en it .

    MazDikCodModPodWillyWonk.JPG
    MazDikCodModPodWillyWonk.JPG

    _....._____________________________

    VBA do have, we can use , “Worksheet_Change” code,. That is for a Worksheet. It has those codes available in Worksheet Code module

    I can see it by, for example,
    Right mouse click on Worksheet Tab --- Select Code

    RightMouseClickOnWorksheetTab.JPG
    RightMouseClickOnWorksheetTab.JPG

    _...._.....

    Then I can select

    Worksheet --- __________________________ --- Change

    MazDikWorksheetCodPodWillyWonk.jpg
    MazDikWorksheetCodPodWillyWonk.JPG

    _......
    _________________________________________


    What that all do mean is I do explain now and it do all as well is given final code me have you for. ( Bottom under is referenced )


    Quote Originally Posted by Doc.AElstein View Post
    ....Code must go in a Worksheets Change Code in a Worksheet Code Module....
    _.. I this do now you for am I can.. wonk..


    Please Login or Register  to view this content.
    _.......________________________________________________

    But
    I do not want all Worksheet. Mazan do want just column A

    Then , so , Alan do a bit more work....wonk


    Please Login or Register  to view this content.

    _.__________________________________________________________

    _ Like this

    Please Login or Register  to view this content.
    ____________________________________________



    I do full code ( 1-macro ) here:
    http://www.excelforum.com/showthread...29#post4477913

    _

    You must put the code in a Worksheets Code module, as I explained above...


    Alan
    _........................................
    ____________________________________________
    Last edited by Doc.AElstein; 09-11-2016 at 08:20 PM.

  11. #11
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    Dear Doc.AElstein

    its work good for me but i have one comment
    why every time i enter new data in column A give me message for already product i enter before
    for example
    if i have product in A1 with name XYT and in column ACDC in A2 and when i enter another product in A3 it give me message about already product i enter before
    why it look for another data i already enter before
    i want to look only to current data i enter now and if it available or the folder is present before

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Tell me the next bit.. Then later tell me all the rest.. Do it all for me ..

    Hello mazan

    Quote Originally Posted by mazan2010 View Post
    ....
    why every time i enter new data in column A give me message for already product i enter before
    .....
    why it look for another data i already enter before
    i want to look only to current data i enter now and if it available or the folder is present before
    I do do ( that did ), because I not too sure was what exactly you did want.
    I try to make code flexible and explain all so you can try to modify to make it exactly as you want.


    Try change it...

    How about this....

    Idea I do think.. not too good idea.. but it work.. ( if you try to understand the codes you should be able to think of other ways: )

    So
    Change


    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    _...

    I explain you now how the new Code works:

    Only now on IF last Folder name we are at ( Last indices is UBound(arrFolderNames()) – in this example it is 4 ) Then do Message Box
    ( _.....
    Cnt is done For 1 , 2 , 3 , 4 ( Lr1 = 4 )
    Array arrFolderNames() is { XYT , ACDC ,XYT , XYT } .... )



    _......................................


    Alan
    Last edited by Doc.AElstein; 09-13-2016 at 11:20 AM.

  13. #13
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    959

    Re: Give it on a Plate ... and ... get the Spoon Ready ;)

    what about macro for subfolder if i want to add to this code

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: macro to create folder and subfolder and file inside subfolder each one with specific

    Hi Mazan
    What about, ... try to understand and learn from the codes I have done for you.

    Then try to modify my 2-macro to do what you finally want.

    Then if you have difficulty, Post back and say where / with what you are having difficulty and we will try to help further.

    Alan

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Addressing Member Ranging ... Property thereof in Do it automatic

    Hi Mazan
    Any progress or feedback from you ?

    _......

    Here another code Idea.

    For 2-macro

    This time, 2-macro is put in a Normal Code Module
    NormalCodeModule2_2.JPG
    http://imgur.com/juAGXGD
    NormalCodeModule.JPG
    http://imgur.com/c0ggc7x

    Main Code is still the same. - ( All just one code but in two parts because of Post size Limitations ) Put in Normal Code Module.
    http://www.excelforum.com/showthread...29#post4483094
    http://www.excelforum.com/showthread...=2#post4483101


    _..................................................................

    Worksheet Change Event Code in Worksheets “2-macro” Worksheets Code Module

    Worksheets2-macroWorksheetsCodeModule.jpg
    http://imgur.com/ox1l70p


    I do not change yet what the 2-macro does. I just show you another way to make a code “Automatic”
    From here:
    http://www.globaliconnect.com/excel/...=79&Itemid=475

    This code will start only when you in type an entry in the next free cell in Column A

    Code:

    Please Login or Register  to view this content.


    If you are finished with this Thread and want no more help, then please
    _ mark Thread as Solved.
    ( You must be logged in to do this: )
    http://imgur.com/8Hebnrn
    Or
    Reply with comments Please

    Alan
    Attached Files Attached Files
    Last edited by Doc.AElstein; 09-18-2016 at 08:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Macro to Open last file in last folder, subfolder
    By MayH in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-18-2016, 04:29 AM
  2. Replies: 1
    Last Post: 12-08-2015, 06:37 AM
  3. Replies: 0
    Last Post: 09-18-2015, 03:20 AM
  4. Create new folder and subfolder and save file with the names from cell values
    By eccordeiro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2015, 10:03 AM
  5. [SOLVED] Macro to open up a .csv file inside each subfolder in a master folder
    By hutch94 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-05-2014, 07:24 PM
  6. [SOLVED] Code to create folder and inside subfolder
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-07-2014, 03:03 PM
  7. [SOLVED] Macro to create subfolder in Oultook & directory folder
    By twitch73 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-26-2012, 09:41 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1