PDA

View Full Version : Solved: Creating folder with drive selection.



aloy78
06-12-2012, 07:04 PM
Dear VBAers,

I've been browsing this forum and others for a way to create folder from excel.

I've tried other codes that were found here with no problems :)

However, what I am after is with a slight modification whereby I would want to be able to change the values based on the cell value on my worksheet rather than going into the module section to amend it.

I've uploaded a file for an overview of what I am talking about :)

Bob Phillips
06-13-2012, 12:48 AM
Put this in the worksheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target

If .Column = 7 Then

If .Value <> "" And .Offset(0, -1).Value <> "" And .Offset(0, -2).Value <> "" Then

On Error Resume Next
MkDir .Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value
MkDir .Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value & Application.PathSeparator & _
.Value
On Error GoTo 0
End If
ElseIf .Column = 8 Then

ChDrive .Offset(0, -3).Value & Application.PathSeparator & _
.Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value
ChDir .Offset(0, -3).Value & Application.PathSeparator & _
.Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value
End If
End With
End Sub

aloy78
06-13-2012, 07:15 PM
Wow... That was fast. Works like a charm. Thanks xld. :friends:

aloy78
06-13-2012, 08:41 PM
Hi xld,

Just wondering what does this code does at column 8?


ElseIf .Column = 8 Then

ChDrive .Offset(0, -3).Value & Application.PathSeparator & _
.Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value
ChDir .Offset(0, -3).Value & Application.PathSeparator & _
.Offset(0, -2).Value & Application.PathSeparator & _
.Offset(0, -1).Value
End If


Can't figure out what it does.

Bob Phillips
06-14-2012, 12:54 AM
If you select a cell in column H, your hyperlink column, it sets the current directory to the drive and directory in E, F, G. In your workbook you said that when you click that column you wanted to go to that folder. I was not sure what you meant by go to, so I took a punt.