Excel VBA Creates a Folder, Sub Folders and further sub folders

Question

I have a question that is very similar to some others i have seen on here, but they dont quite answer what i need, or when i have tried them it has caused an error that i dont know how to solve. Only being level 5 i can't comment to ask a question.

In excel i have a file that is used for the naming profile for quotation folders.

I have tried to use the answer for: Create a folder and sub folder in Excel VBA and tweaked it as per the below, but it errors when it gets to If Functions.FolderExists(path) Then it says

Run-time error '424': Object required.

I also need to to create the folder name as per sheet "Data Entry" cell "C44" and "C31", i then need to add subfolders to this which are not referenced in any cell including: 1. Customer RFQ This will have a further subfolder with the name base on "Data Entry" cell "C33"

Design Engineering
Drawings
Costings
Schedules
Quotation

Any help would be greatly appreciated. Thank you,

'requires reference to Microsoft Scripting Runtime
Sub MakeFolder()

Dim strFolder As String, strPath As String

strFolder = CleanName(Range("C31")) ' assumes folder name is in C31
strPath = Range("C44") ' assumes path name is in C44

If Not FolderExists(strPath) Then
'Path doesn't exist, so create full path
    FolderCreate strPath & "\" & strFolder
Else
'Path does exist, but no quote folder
    If Not FolderExists(strPath & "\" & strFolder) Then
        FolderCreate strPath & "\" & strFolder
    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 'This is the part that doesn't work
    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, "*", "")

End Function

Any help greatly appreciated. Thank you

@BigBen WOW! that was really quick! Thank you very much, just for interest, what is the difference between a function and fso? Obviously everyone else who used it knew to change it but being new to this i would have no idea to change it. — Steven Byrne, Apr 06 '20 at 13:04
I will point you to the [FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object) documentation for a thorough explanation. — BigBen, Apr 06 '20 at 13:05
@BigBen, really appreciate your help! Thank you very much, really greatful. — Steven Byrne, Apr 06 '20 at 13:06
If you change `Functions.FolderExists(path)` to `FolderExists(path)` it would work, too. You don't need to preface with `Functions`. However, that function really isn't needed, just use the solution from @BigBen. — Brian M Stafford, Apr 06 '20 at 13:09
@BrianMStafford The function `FolderExists` is used and needed for the `MakeFolder` procedure. So it should not be deleted. But in `FolderCreate` it is better to go with BigBen's solution as there is a `fso` created anyway. So `FolderExists` would create another one which is not necessary. — Pᴇʜ, Apr 06 '20 at 13:22
@Pᴇʜ Yes, it is currently used but could easily be replaced with `fso.FolderExists`. — Brian M Stafford, Apr 06 '20 at 13:24
@BrianMStafford yes, it could. But it would highly depend on how the rest of the code looks like. Using functions is not a bad thing and it might be used in some other code too. So better check twice. — Pᴇʜ, Apr 06 '20 at 13:26
@Pᴇʜ I love functions, in fact they are a great thing. But this function serves no purpose, it is only a wrapper for single line of fso code. So yes, check twice. If this was my code, I would immediately refactor to get rid of the function. — Brian M Stafford, Apr 06 '20 at 13:32

Steven Byrne · Accepted Answer · 2020-04-09T05:46:32.960

thanks to @BigBen, @BrianMStafford for your help. I managed to come up with this which works. This creates 10 subfolders within the master folder which is in a cell specified location. It then creates a further subfolder in folder 1.

For some reason my companies security has a issue opening the files created by the code where there name is not from a cell. So i plan on moving all the other folder names to a range of cells with hope this works.

After this when i work out how to do it, i plan on getting it to open the folder the user would use first. In my case this is the last created folder. Hope this helps someone :-)

'requires reference to Microsoft Scripting Runtime
Sub MakeFolder()

Dim strFolder As String, strPath As String

strFolder = CleanName(Range("C31")) ' assumes folder name is in C31
strPath = Range("C44") ' assumes path name is in C44

If Not FolderExists(strPath) Then
'Path doesn't exist, so create full path
    FolderCreate strPath & "\" & strFolder
Else
'Path does exist, but no quote folder
    If Not FolderExists(strPath & "\" & strFolder) Then
        FolderCreate strPath & "\" & strFolder
        FolderCreate strPath & "\" & strFolder & "\" & "01. Customer RFQ"
        FolderCreate strPath & "\" & strFolder & "\" & "02. Design Engineering"
        FolderCreate strPath & "\" & strFolder & "\" & "03. Drawings"
        FolderCreate strPath & "\" & strFolder & "\" & "04. Costings"
        FolderCreate strPath & "\" & strFolder & "\" & "05. Schedules"
        FolderCreate strPath & "\" & strFolder & "\" & "06. Quotation"
        FolderCreate strPath & "\" & strFolder & "\" & "07. Email"
        FolderCreate strPath & "\" & strFolder & "\" & "08. MOMs"
        FolderCreate strPath & "\" & strFolder & "\" & "09. Sales Excellence"
        FolderCreate strPath & "\" & strFolder & "\" & "10. Compliance"
        FolderCreate strPath & "\" & strFolder & "\" & "01. Customer RFQ" & "\" & Range("C33")
    End If
End If

End Sub

Function FolderCreate(ByVal path As String) As Boolean

FolderCreate = True
Dim fso As New FileSystemObject

If fso.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, "*", "")

End Function

If you name your folders "01...", "02...",, "10..." then they will sort in the correct order in Windows Explorer, instead of 1 and 10 sorting together. — Tim Williams, Apr 06 '20 at 15:28

Excel VBA Creates a Folder, Sub Folders and further sub folders

1 Answers1

Linked