VBA code to create & name an empty folder

[stuo81]

Hi

I need some VBA code to give the following effect if anyone can help.

I want macro code so I can have a button which when pressed will create an empty folder on the desktop of the current computer which the sheet is on. (this needs to be able to work on any computer the sheet is used on or forwarded to etc).

The original sheet with the button on described above will have 3 text boxes and a date cell. If possible I would like the newly created folder to be automatically renamed with the content of the 3 text boxes and the date in the cell. EG ‘textbox1words-textbox2words-textbox3words-22/11/13’. I can change the format the date is shown in the cell too as I’m sure the / wont work.

Any help is much appreciated.

Thanks

 

[stuo81]

Anyone out there think this is doable?

Please I'm really stuck!

 

[Andrew Poulsom]

Where are your TextBoxes from?

 

[stuo81]

Hi they are from the control toolbox menu.

 

[Snakehips]

stuo81,

Perhaps something like this......

Sub New_Folder()
MyPath = ThisWorkbook.Path
MyUser = Environ("Username")
On Error GoTo Oops
Oops:
MsgBox "What the hell went wrong?"
On Error GoTo 0
Exit Sub
With ActiveSheet
MyFolder = .TextBox1.Value & "-" & .TextBox2.Value & "-" & .TextBox3.Value & "-" & Format(Range("A4"), "dd-mm-yy")
'edit range A4 above, to suit cell that holds your date
End With
MyPath = Left(MyPath, InStr(1, MyPath, MyUser) + Len(MyUser)) & "DESKTOP\" & MyFolder
If Len(Dir(MyPath, vbDirectory)) = 0 Then
MkDir MyPath
Else
MsgBox "Folder already exists"
End If
On Error GoTo 0
End Sub

Hopefully, it's good for Windows environment but I believe that the use of ENVIRON is not supported on Mac.

Hope that helps in some way.

 

[Andrew Poulsom]

To assign the path to the user's Desktop to a variable in Windows:

Dim Desktop As String
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")

 

[stuo81]

Cheers gents.

Andrew is your code stand alone or to fit into Snakehips' suggestion somewhere (sorry I'm a VBA novice trying to pick up what I can from this site).

Also I'm currently on my MAC which may be causing the issues (or it's most likely me being a bit dense) but I will have a play on my PC in couple of days when I can get into the office and let you know how i get on.

Thanks again.

 

[Snakehips]

stuo81,

Andrews line of code is a more direct and maybe,a more reliable means of getting the path to the desktop that was beyond my knowledge.
I would guess from Andrews post that it is still only of value within the Windows environment?

You will still require supporting code in order to create your folder......

Sub New_Folder()
On Error GoTo Oops
GoTo Create
Oops:
MsgBox "What the hell went wrong?"
On Error GoTo 0
Exit Sub
Create:
With ActiveSheet
MyFolder = .TextBox1.Value & "-" & .TextBox2.Value & "-" & .TextBox3.Value & "-" & Format(Range("A4"), "dd-mm-yy")
'edit range A4 above, to suit cell that holds your date
End With
MyPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & MyFolder
If Len(Dir(MyPath, vbDirectory)) = 0 Then
MkDir MyPath
Else
MsgBox "Folder already exists"
End If
On Error GoTo 0
End Sub

(FYI the careless way that I put the error handler in my first code would have it terminating at the error message every time!! )

 

[stuo81]

Hi again

thanks for the update. I've had a go with the last 1 you kindly suggested but am getting -Compile error: Syntax Error box popping up and taking me to the VBA page with the Textbox1.value to the date line highlighted.

Any ideas?

 

[Snakehips]

Your original post spoke of having 3 text boxes 1, 2, and 3 the text of which would go to make up the folder name.

I have assumed these to be actives text boxes TextBox1, 2 & 3 I did omit to comment that you will need to edit the those text box names if those in your sheet are different.