Creating a folder directory using VBA dependent on cell value

Question

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.

To create a folder in VBA using a cell value you can do this: Public Sub Test() MkDir "G:\To.Delete\Title" + Cells(1, 1).Value End Sub — Francisco Araujo
– Francisco Araujo, Commented Aug 19, 2019 at 11:58

claudius · Accepted Answer · 2019-08-20 12:55:36Z

1

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

edited Aug 20, 2019 at 12:55

answered Aug 19, 2019 at 15:21

claudius

1,0152 gold badges15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dagnabbit Over a year ago

Thank you both for your responses. Claudius, in your code do I simply replace all of the "ws_path" with my folder directory?

claudius Over a year ago

You just need put the workbook (with the code) in the directory do you want. The code: ws_path = ThisWorkbook.Path will return the folder way that you are inside. Or, if you want rigid codding, you can put the folder way directly.

Patrick Honorez · Accepted Answer · 2022-11-18 07:04:31Z

1

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

edited Nov 18, 2022 at 7:04

answered Aug 19, 2019 at 12:00

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Collectives™ on Stack Overflow

Creating a folder directory using VBA dependent on cell value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related