Create a folder and sub folder in Excel VBA

Question

I have a pull down menu of companies that is populated by a list on another sheet. Three columns, Company, Job #, and Part Number.

When a job is created I need a folder for said company and a sub-folder for said Part Number.

If you go down the path it would look like:

C:\Images\Company Name\Part Number\

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.

Another question is there a way to make it so it works on Macs and PCs the same?

Solution 1

One sub and two functions. The sub builds your path and use the functions to check if the path exists and create if not. If the full path exists already, it will just pass on by. This will work on PC, but you will have to check what needs to be modified to work on Mac as well.

'requires reference to Microsoft Scripting Runtime
Sub MakeFolder()

Dim strComp As String, strPart As String, strPath As String

strComp = Range("A1") ' assumes company name in A1
strPart = CleanName(Range("C1")) ' assumes part in C1
strPath = "C:\Images\"

If Not FolderExists(strPath & strComp) Then 
'company doesn't exist, so create full path
    FolderCreate strPath & strComp & "\" & strPart
Else
'company does exist, but does part folder
    If Not FolderExists(strPath & strComp & "\" & strPart) Then
        FolderCreate strPath & strComp & "\" & strPart
    End If
End If

End Sub

Function FolderCreate(ByVal path As String) As Boolean

FolderCreate = True
Dim fso As New FileSystemObject

If Functions.FolderExists(path) Then
    Exit Function
Else
    On Error GoTo DeadInTheWater
    fso.CreateFolder path ' could there be any error with this, like if the path is really screwed up?
    Exit Function
End If

DeadInTheWater:
    MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
    FolderCreate = False
    Exit Function

End Function

Function FolderExists(ByVal path As String) As Boolean

FolderExists = False
Dim fso As New FileSystemObject

If fso.FolderExists(path) Then FolderExists = True

End Function

Function CleanName(strName as String) as String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters

    CleanName = Replace(strName, "/","")
    CleanName = Replace(CleanName, "*","")
    etc...

End Function

Solution 2

Another simple version working on PC:

Sub CreateDir(strPath As String)
    Dim elm As Variant
    Dim strCheckPath As String

    strCheckPath = ""
    For Each elm In Split(strPath, "\")
        strCheckPath = strCheckPath & elm & "\"
        If Len(Dir(strCheckPath, vbDirectory)) = 0 Then MkDir strCheckPath
    Next
End Sub

Solution 3

I found a much better way of doing the same, less code, much more efficient. Note that the """" is to quote the path in case it contains blanks in a folder name. Command line mkdir creates any intermediary folder if necessary to make the whole path exist.

If Dir(YourPath, vbDirectory) = "" Then
    Shell ("cmd /c mkdir """ & YourPath & """")
End If

Solution 4

Private Sub CommandButton1_Click()
    Dim fso As Object
    Dim fldrname As String
    Dim fldrpath As String

    Set fso = CreateObject("scripting.filesystemobject")
    fldrname = Format(Now(), "dd-mm-yyyy")
    fldrpath = "C:\Temp\" & fldrname
    If Not fso.FolderExists(fldrpath) Then
        fso.createfolder (fldrpath)
    End If
End Sub

Solution 5

Function MkDir(ByVal strDir As String)
    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(strDir) Then
        ' create parent folder if not exist (recursive)
        MkDir (fso.GetParentFolderName(strDir))
        ' doesn't exist, so create the folder
        fso.CreateFolder strDir
    End If
End Function

View more solutions

Comments

[Matt Ridge]

Is there a way to make Application.PathSeparator work with this code so it can work universally with Mac and PC?

[Scott Holtzman]

yes, replace every instance of "\" (usually PC path separator) with Application.PathSeparator - so "C:\Images\" becomes "C:" & Application.PathSeparator & "Images" & Application.PathSeparator ... or you may be able to set Application.PathSepator as a variable and just use the variable name throughout. Probably cleaner that way :)

[Matt Ridge]

I've been trying to get this to work, the only problem I am running into is that with the code with the Application.PathSeparator entered into it, is that on the Mac there is no C drive, it's \Volumes\Drive Name\Path\...

[Scott Holtzman]

see this website rondebruin.nl/mac.htm ... Ron is pretty good. You can first test to see if you are on a Mac or PC, then set your path variable accordingly. Like IF MAC Then strPath = \\Volumes\Drive\Name\Path ELSE strPath = "C:\..." END IF. If you need help getting that set up, please post another question.

[Matt Ridge]

He is, but he uses a Mac specific script when it comes to the Mac Side, there has to be another way. I have this script to create a log, and it works on a Mac or PC... stackoverflow.com/questions/10403517/… But the thing is that it uses the path of the document as the parent, not a different directory entirely on a different drive.

[Scott Holtzman]

Right, but you have a way to test if it's Mac / PC. Then once you know, you know how to create the snytax to get at the drive you want on both, no?

[Matt Ridge]

Yes, but then that means writing the code twice... not exactly fast code concept if you get my drift. This is what I mean. Lets say that the folder where everything is going is going to be on S:/Images/ for windows. On the Mac it is /Volumes/Images/. I can't imagine that a volume letter is the one thing keeping it all from coming together?

[Scott Holtzman]

Its not writing the code twice, its simply using an If statement using Instr(1,Application.OperatingSystem,"MAC") to test if you are on MAC or PC and setting the strPath variable accordingly. You then pass the strPath to the rest of your code, or at least most of it. See my comment from above, 2 comments ago. It may not be the only thing left to get it from coming together, but you have to work in increments and see you can make each element work... in this way you build your code step by step, refactoring it along the way as you need to. Just try to solve one problem at a time.

[Matt Ridge]

This really bothers me, but I guess I'll have to live with this. I really don't like how coding differs between the Mac and PC even though Excel was designed for the Mac first.

[Scott Holtzman]

I hear yeah... but after all is said and done, you'll be a way better coder! If its any consolation.

[Matt Ridge]

Ok, solved the Mac and PC issue, rather smartly if I say so myself. I only have one question with the code above now. I have a range of cells to go through, I will need to do something like A3: A & lastrow. I believe it will work in the script you provided above, but I want to verify before I go forward with it. If I change the range for part in C1 to C3:C lastrow, will it still function the same way? Because the way you have it for an individual row, not multiple if I am reading it correctly. Thanks again.

[Matt Ridge]

Updated original post with full text, a copy of worksheet I'm on, and with a new problem...with my old code, and your code combined.

[Scott Holtzman]

try wrapping "(" around strPath & strComp & "\" & strPart in FolderCreate strPath & strComp & "\" & strPart. Also, this may just be the way it was pasting into the comment box, but place a space between the ")" and "T" in strComp)Then.

[Scott Holtzman]

Matt. Your original question has been answered already. Please post a new question, so that the structure of how Q&A works remains intact. With your edits, the accepted answer no longer matches the original question. If you leave this Q as is, and start a new one, others can track A LOT easier. I am happy to help you with your issue, but I won't offer any more answers in this Q. And please revert this question back to it's original state.

[waternova]

This works great for just creating the folder, but it doesn't wait for the command to end. So if you try to copy a file to your new folder just after this, it will fail.

[Seb]

underrated solution

[Martin Dreher]

thanks for this! works like a charm with minor changes, since there's a function StrComp in Excel 2010.

[jramm]

Just be careful strPath doesn't include a filename after the final "\" or this code will create a folder with that name.

[Leandro Jacques]

Just put another command to check while it doesn't exist and don't copy anything in there.

[Dragonthoughts]

Please explain why this answer is better than any of the other 12, given 7 years ago.

[help-info.de]

Welcome to Stack Overflow! Here is a guide on How to Answer. Code-only answers are considered low quality: make sure to provide an explanation what your code does and how it solves the problem.

[John Shaw]

This Solution is cleaner. It relies more on Windows file scripting host functions rather than 3 clunky vba modules. It gets my vote.

[ZygD]

This will fail if more subfolder levels are needed. Although it may work if only one folder needs to be created.

[ZygD]

If accidentally YourPath is "//" or "\\" - it will hang. If something like "::" - it will proceed, will fail, and you will not know it failed. If just a string (not a path), e.g. "ABBA" - the folder will be created in your CurDir (a different location than the file's filder). A proper method for checking folder existence is FileSystemObject.FolderExists(YourPath)

[rayzinnz]

@waternova I got around this by using WScript object: Set wsh = CreateObject("WScript.Shell"); wsh.Run "cmd /c mkdir """ & YourPath & """", 0, True This will wait until the cmd is finished

[danieltakeshi]

As stated by @MartinDreher, now there is a function strComp. So you should change the name of the variable strComb

[phrebh]

Your own link points out that mkdir can't create parent directories.

[Braide]

fantastic solution. A slight improvement is to use Dir(strCheckPath, vbDirectory) = "" instead of calling Len.

[brinkdinges]

This does not work with UNC paths that start with \\. I added two checks but that feels like a hack: If strCheckPath <> "\" And strCheckPath <> "\\" Then

[Geoff]

Doesn't work - MkDir cannot create parent directories

[Aaron C]

This is a good answer as well stackoverflow.com/a/50818079/3991654