Is there a way to create a script that will create a folder and sub folder if none exist?

**Cyberpawz** · 05-29-2012, 02:07 PM

I have a sheet with three columns, Company, Job #, and Part Number.

What I have going on is that when a job is created I need a folder for said company to be created, and then a sub-folder created based off of said Part Number. So if you go down the path it would look like this:

C:\Images\Company Name\Part Number\

And continue down till the last row has been read and finished.

Now if either company name or Part number exists don't create, or overwrite the old one. Just go to next step. So if both folders exist nothing happens, if one or both don't exist create as required.

Does this make sense?

If someone can help me with understanding how this works and how to make it work it would be greatly appreciated. Thanks again.

Another question if it's not too much is there a way to make it so it works on Macs and PCs the same?

**Mordred** · 05-29-2012, 02:19 PM

Perhaps here would be a good start?

**Cyberpawz** · 05-29-2012, 02:23 PM

Originally Posted by Mordred

Perhaps here would be a good start?

except 2 problems. 1 It expects you'll be on a PC. 2, I have no clue it if it will take an excel sheet like I want and make it work. I am asking because I don't know how to do it. Otherwise I wouldn't be asking.

**Mordred** · 05-29-2012, 02:24 PM

And I was only suggesting.

**Cyberpawz** · 05-29-2012, 02:37 PM

Originally Posted by Mordred

And I was only suggesting.

I know, but I can't figure out where to start to make this work, hence why I am asking... most I have seen show a standard format like mkdir: C:Images\xxx\xxx-xxx. I need to have it where the company name although may stay the same, it places the part number under the correct company, etc... and those two fields are always every changing. We may have 100 entries, with 24 different companies, with 100 different parts, so that means there are going to be entries with the same company, but parts are going to be different, I don't need 100 folders with Company 1, Company 1(1), Company 1(2), etc and have one folder in each... or have it overwrite each time a new entry shows up...

I hope you can understand my frustration.

**Chippy** · 05-29-2012, 05:17 PM

Have a look (i.e. search, etc.) at code based on the FileSystemObject's FolderExists and CreateFolder methods - http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx.

**Cyberpawz** · 05-29-2012, 05:30 PM

Originally Posted by Chippy

Have a look (i.e. search, etc.) at code based on the FileSystemObject's FolderExists and CreateFolder methods - http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx.

I knew about this, but doesn't that only work with files, not folders?

**Chippy** · 05-29-2012, 05:34 PM

No, FSO works with folders too. Actually, it's probably simpler to use the VBA functions Dir (to check whether a folder exists) and MkDir (to create a folder) than FSO.

EDIT: Try this code.

Please Login or Register  to view this content.

The code doesn't check for characters in the company name nor part number which are invalid in a Windows folder name.

**Cyberpawz** · 05-29-2012, 05:44 PM

Originally Posted by Chippy

No, FSO works with folders too. Actually, it's probably simpler to use the VBA functions Dir (to check whether a folder exists) and MkDir (to create a folder) than FSO.

EDIT: Try this code.

Please Login or Register  to view this content.

The code doesn't check for characters in the company name nor part number which are invalid in a Windows folder name.

The problem comes in, and this is where I am having problems is where to take this script and make it work with a Mac and PC alike with no extra code... also it would be from column C and D, from C3 to end, and D3 to end, C would be parent folder, D would be sub-folder.

And what I mean from extra code is that code X works for Mac, code Y works for PC, I want it where code Z works for Mac and PC alike. because

Please Login or Register  to view this content.

only works for PC... not mac.

**Chippy** · 05-29-2012, 05:53 PM

It easy enough to change the code to look in the appropriate columns (change the letter 'A' to 'C' for example) and the Offset column value and starting at row 3 - I'm sure you can do this. Similarly, determining the last populated row in a column is standard VBA code - have a search.

Sorry, I don't know anything about VBA for Excel Mac so I can't really help you further but I'm sure you won't be able to make it work on Mac and PC alike without extra code. See if http://www.rondebruin.nl/mac.htm helps.

**Cyberpawz** · 05-29-2012, 10:01 PM

Originally Posted by Chippy

It easy enough to change the code to look in the appropriate columns (change the letter 'A' to 'C' for example) and the Offset column value and starting at row 3 - I'm sure you can do this. Similarly, determining the last populated row in a column is standard VBA code - have a search.

Sorry, I don't know anything about VBA for Excel Mac so I can't really help you further but I'm sure you won't be able to make it work on Mac and PC alike without extra code. See if http://www.rondebruin.nl/mac.htm helps.

And that's the problem, I know there is a way to do it, but I don't know how to use it correctly. The way is with Application.PathSeparator...

**Cyberpawz** · 06-04-2012, 08:11 AM

Originally Posted by Chippy

No, FSO works with folders too. Actually, it's probably simpler to use the VBA functions Dir (to check whether a folder exists) and MkDir (to create a folder) than FSO.

EDIT: Try this code.

Please Login or Register  to view this content.

The code doesn't check for characters in the company name nor part number which are invalid in a Windows folder name.

Ok, solved the issue with the Mac issue which will be shown on the list tab in the workbook attached... now two questions/points.

1. Will this script overwrite and folder that is already created by the script?
2. The the workbook is below.... in each row there will be two cells that will be required, a company and part number. So it will need to do this: If company doesn't exist create company folder. Next go to part, if part folder inside company folder doesn't exist create it, if it does don't overwrite.

Does this make any sense?

I hope so because I can't figure out how to make it so it looks in both places and writes the correct folders accordingly.

Sorry about the file, if anyone downloaded it, the new one has only one circular error, which is fixed on the final sheet, but for all intended purposes it will work here.

**Cyberpawz** · 06-04-2012, 09:35 AM

Ok, just attempted to run the code. and I am getting a `Run-Time error '76': Path not found on the MkDir command. is there a reason for that? The path exists, I know it does because I created the folder myself.

The entire code is as thus:

Please Login or Register  to view this content.

The Lists!$G$1 = C:\Documents and Settings\mridge\Desktop\test\

**Chippy** · 06-04-2012, 02:18 PM

Originally Posted by Cyberpawz

`Run-Time error '76': Path not found on the MkDir command. is there a reason for that? The path exists, I know it does because I created the folder myself.

Which MkDir command? There are two. Just put MsgBox ">" & newFolder & "<" before the line and ensure the folder displayed is valid.

Is there a way to create a script that will create a folder and sub folder if none exist?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a way to create a script that will create a folder and sub folder if none exist?

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Re: Is there a way to create a script that will create a folder and sub folder if none exi

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions