Create folder with VBA

[cagni]

Hi All

New to this forum and VBA macros, so bare over with me =)

I need a macro that can create a folder in the same destination as the excel file it self. This location can change so the it can´t be a specific location as it will change?

Plus is it possible to name the folder after a few cells value?

I have a button that opens a userform to enter data to a list. This folder should be created at the same time as the new data is entered to the list.

This is the macro that runs for the "Add Project", which is a mess and I´m as said really green into macro =)

Let me know if it is possible


"
Private Sub addProject_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Ark1")

Sheets("Ark1").Range("A8").Select
ActiveCell.EntireRow.Insert shift:=xlDown

Sheets("Ark1").Range("B8:I8").Select
Selection.Borders.Weight = xlThin

Sheets("Ark1").Range("B8:I8").Select
Selection.Font.Size = 11

Sheets("Ark1").Range("B8:I8").Select
Selection.Font.Bold = False

Sheets("Ark1").Range("B8").Select
ActiveCell.Value = Range("B9") + 1

Sheets("Ark1").Range("E8").Select
ActiveCell.Value = Date

With ws
Sheets("Ark1").Range("C8").Select
ActiveCell.Value = Me.TextBox1.Value

Sheets("Ark1").Range("D8").Select
ActiveCell.Value = Me.TextBox2.Value

Sheets("Ark1").Range("F8").Select
ActiveCell.Value = Me.TextBox3.Value

Sheets("Ark1").Range("G8").Select
ActiveCell.Value = Me.TextBox4.Value

Sheets("Ark1").Range("H8").Select
ActiveCell.Value = Me.TextBox5.Value

Sheets("Ark1").Range("I8").Select
ActiveCell.Value = Me.TextBox6.Value
End With

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox1.SetFocus

Unload Me

End Sub

Private Sub closeButton_Click()

Unload Me

End Sub

Private Sub UserForm_Click()

End Sub

"

 

[davesexcel]

In this example, the code combines A1 and B1 as the new folder name.
There are a couple line samples showing how not to use selects.

Sub MakeFolder()
    Dim wb As Workbook, ws As Worksheet
    Dim p As String, NwPath As String
    Dim c1 As String, c2 As String
    Dim folder As String

    Set wb = ThisWorkbook
    p = wb.Path & "\"
    Set ws = wb.Sheets("Ark1")
    
    With ws
        .Range("A8").EntireRow.Insert shift:=xlDown
        .Range("B8:I8").Borders.Weight = xlThin
        .Range("B8:I8").Font.Size = 11
        'etc
        c1 = .Range("A1").Value
        c2 = .Range("B1").Value
    End With
    
    NwPath = p & c1 & c2
    'check if folder exists, if not then create folder
    folder = Dir(NwPath, vbDirectory)
    If folder = vbNullString Then
        VBA.FileSystem.MkDir (NwPath)
    End If
End Sub

 

[cagni]

Is it possible to make dot between c1 and c2 in the folder name?

Thanks for the code it worked

 

[johnnyL]

Is it possible to make dot between c1 and c2 in the folder name?

Thanks for the code it worked

Change:

    NwPath = p & c1 & c2

to:

    NwPath = p & c1 & "." & c2

 

[cagni]

Would it be possible to get the current year into the folder name as well?

 

[davesexcel]

If you wanted to add additional text like the year.

NwPath = p & c1 & c2 & format(date,"yyyy")