CODEWITHSUNDEEP
excelvba
dagnabbit
dagnabbit

Reputation: 13

Creating a folder directory using VBA dependent on cell value

I have an Excel spreadsheet which I use as a job tracker. I would like to write a code in VBA which creates a directory (with sub folders) dependent on some of the values that have been entered for each record.

Column A: PIN B: Team C: Title

In an already established directory in Windows I have a folder which contains folders of all of the teams we do work for. Basically, I want to have the code so that it looks at the team value in my tracker and creates a new folder (with sub folders) within that team area.

eg: "C:\Teams\Team 1\new folder goes here"

I would like the new folder created to use the format of the PIN + Title from columns A and C. Plus, inside each new created folder, I would like these sub-folders: "1_Comms", "2_Input", "3_Working", "4_Output".

eg: "C:\Teams\Team 1\PIN value + Title" with all of the aforementioned sub folders contained in this new directory.

Any assistance on this would be massively appreciated as I am a total noob at VBA and very much still learning.

Haven't tried anything so far other than totally embarrassing myself.

Upvotes: 1

Views: 3546

Answers (2)

iDevlop
iDevlop

Reputation: 25252

You can use this recursive function, wich takes care of the missing folders:

Function SmartCreateFolder(byVal sFolder as String) As Boolean
'inspired by: https://stackoverflow.com/a/54280512/78522
'Works with drive letters but also with UNC paths
    Static oFSO As Object

    If oFSO Is Nothing Then Set oFSO = CreateObject("Scripting.FileSystemObject")
    On Error GoTo hell  'mostly to handle forbidden characters
    With oFSO
        If Not .FolderExists(sFolder) Then
            If SmartCreateFolder(.GetParentFolderName(sFolder)) Then
                .CreateFolder sFolder
            Else
                GoTo hell
            End If
        End If
    End With
    SmartCreateFolder = True
hell:
End Function

Upvotes: 1

claudius
claudius

Reputation: 1005

To do that you first create the main folder, like in your example "C:\Teams\Team 1\PIN value + Title". So, after that you create the subfolders.

Sub CreateFolder(ByVal pin_value As String, ByVal title As String)

Dim wb_path As String
Dim folder_1_path, folder_2_path, folder_3_path, folder_4_path As String

    wb_path = ThisWorkbook.Path

    ' create main folder
    wb_path = wb_path & "\" & pin_value & "_" & title

    MkDir (wb_path)

    ' create subfolder  
    folder_1_path = wb_path & "\1_Comms"
    folder_2_path = wb_path & "\2_Input"
    folder_3_path = wb_path & "\3_Working"
    folder_4_path = wb_path & "\4_Output"

    MkDir (folder_1_path)
    MkDir (folder_2_path)
    MkDir (folder_3_path)
    MkDir (folder_4_path)
End Sub

If your PIN number is an integer, you will need convert it to string. Like in this test below:

Sub test()

    pin_value = 777
    title = "myFolder"

    pin_value = CStr(pin_value)

    Call CreateFolder(pin_value, title)

End Sub

Upvotes: 1

Related Questions