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

[mazan2010]

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

[Doc.AElstein]

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.

[Doc.AElstein]

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

[mazan2010]

dear Doc.AElstein
would you please upload example for each

[Doc.AElstein]

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

[mazan2010]

it not working for me i didnt know why

[Doc.AElstein]

Hello Mazan

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

[mazan2010]

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

[Doc.AElstein]

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

[Doc.AElstein]

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

...
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 )

....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
_........................................
____________________________________________

[mazan2010]

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

[Doc.AElstein]

Hello mazan

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

[mazan2010]

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

[Doc.AElstein]

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

[Doc.AElstein]

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