Creating Folders based on cell values

Hi All,

Wondered if some bright spark can assist with an issue im having?

I have a spreadsheet which has product column on the left and to the right are various document names as column headers.

I have colour codes: Green = Received, Orange = Requested, Red = attention. Users enter short text in the cells to describe the present position of the document, and colour code accordingly.

Scenario
(assume directory is already created as : "Product Name/Document header/<date & cell value here>) - bolded is already created in the directory.

User clicks on a cell, and clicks on "create folder" button. The button should pull the Product Name from the spreadsheet, pull the relevant document column header from where the user has selected the cell and create a new folder as above based on the date and active cell text: "<date & cell value/text here>". The date should be in this format yyyy/mm/dd so that the folders flow down the directory in date order, so that the folders are not in random order.

I want the "create folder" button to also check if the folder already exists as to not overwrite any previously created folder with the same date and cell value/text.


The aim is that the spreadsheet will be used as the hub.

Look forward to any replies and suggestions,

Best wishes.

I didnt think it mattered what docs are going to be in the folders as the users will just be dragging and dropping the docs into the folders that are created by the Spreadsheet.. The main issue is what is outlined in the spreadsheet, would appreciate your help if you have an idea as to how to go about it.

The spreadsheet outlined a couple of the docs but there are many more, i just need an example (hence the spreadsheet) and i can do the rest..THe docs will be pdf's word docs etc, but i didnt think this mattered as mentioned. Thanks for your help,

THanks for your help,

'MAKE FOLDERS AND SUBFOLDERS
Here's a macro for creating all the folders and subfolders in path. There's a macro and a UDF version that other macros can use.

Make Folders and Subfolders

Based on that, I edited it down to your scenario. This will create the folders desired based on the ACTIVECELL, so after someone enters text anywhere in your key section, the cell highlighted is the one used when the macro is activated. Here is the result:

Please Login or Register  to view this content.

This code actually does a little more than you asked. It creates all the folders and subfolders from the top level down to the bottom. Of course, if your upper folders already exist it just moves on. This macro assumes C:\ drive is root.

bleeding heck, thanks a lot guys you are a helpful bunch!

I will try some out on the weekend and provide an update of course!

With regards to the file types, excuse my ignorance but i still dont get why this matters? The users create and open the folders from the spreadsheet and they drop relevant files in the folders. Maybe i left out that the users will have adobe/word etc but i thought that was presumed since they are the ones that are dropping the files in the created folders. Does this make sense or am i missing something? A side note is that the folders are created and opened within windows explorer, but again i presume that was realised from the example?

Hope this doesnt come across ungrateful, because i am very grateful for the help, thank you all for your help, i will come back shortly on this,,in the meantime, a pointer regarding my above ignorance would be welcome.

I don't understand the question posed earlier about what files you're going to put into these created folders, either. But the macro I provided will create folders based on the selected cell when you run it.

Hi Jerry, Thank you very much for the example, i have adapted it for my spreadhsheet as i realised i may need more dir levels and it works perfectly. Thanks very much for your kind help. Thank you also pierre for your help.

Was curious though, you know the code

Please Login or Register  to view this content.

Why does it say "row, 1" when in actual fact it is column 1 it pulls the product name from? It works fine was just curious? Similarly, column 3 when it is row 3 it pulls the sub dir from? Maybe i am reading it in the wrong context?

Do you think you could help with the remainder on my spreadsheet example? I will figure out the "open folder" bit, that cant be hard. It was the "Add note example" i was looking for next.

I would like it to basically pop up entry boxes for the user to input the data as shown on the spreadsheet (pop up entry boxes will be good to restrict their input in the comment), then to insert as comment on the active cell. If there is already a comment in a cell and the user hits add note button, it should start the process again but simply append to the existing one.



BW

Wouldn't this suffice ?

Please Login or Register  to view this content.

Originally Posted by vbwanab

Was curious though, you know the code

Please Login or Register  to view this content.

Why does it say "row, 1" when in actual fact it is column 1 it pulls the product name from? It works fine was just curious? Similarly, column 3 when it is row 3 it pulls the sub dir from? Maybe i am reading it in the wrong context?

The Cells() syntax is as follows:

Cell(RowReference, ColumnReference)

So there are two parameters being given values each time it is used. In the first example, Cells(.Row, 1)
...you see the ROW is taken from the row of the Activecell (notice the "with" reference above it?) and we've hardcoded the column to column 1.

The second Cells() formula for the end next part of the path is coming from row 3, the code is Cells(3, .Column)(
...so the row is hardcoded as 3, but the column is taken from the Activecell.

Is that clear how the CELLS() function works? Each parameter is a numeric value.

You don't need two separate buttons for the notes, you could build the capability in like so:

Please Login or Register  to view this content.

This will add the boilerplate text into a new comment box if none exists, or it will add a new "instance" of the boilerplate into an existing comment, incrementing the receipt #.

Of course, the answers still need to be added by the user, EDIT > COMMENT.

I just reread it and noticed the "popup and ask questions" idea. This will do that...much longer code, of course...

Please Login or Register  to view this content.

To edit an existing comment, just right-click the target cell end choose Edit Comment.

The two buttons for opening the activecell folder/top folder:

Please Login or Register  to view this content.

Hi Jerry, Any ideas on how to capture the batch number as alpha numeric type? I looked it up but there doesnt seem to be a type to cover this?


EDIT
Never mind i just copied the Further comment code

My apologies, you just needed to edit the Type:=1 to Type:=2. 1 is numeric, 2 is text.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.