MrExcel Publishing
Your One Stop for Excel Tips & Solutions

create dir if doesen't exist


Posted by Paul on April 24, 2001 12:07 PM

I have be told the macro below. It is supposed to check the directory in a cell exists and if it doesn’t it will create it. But I can’t get it to work. Can anyone please help?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TypePath As String, Answer As Long

'Will only expect path names in column 1 (A). Change
'this value for a different column e.g. D=4
If Target.Column <> 4 Then Exit Sub

TypePath = Target.Value
If TypePath = "" Then Exit Sub
If FSObj.FolderExists(TypePath) = False Then
Answer = MsgBox("This folder does not exist. Would" & vbCrLf & _
"you like to create it?", vbYesNo, "Folder doesn't exist")
If Answer = vbYes Then
FSObj.CreateFolder TypePath
End If
End If

End Sub
Thanks


Posted by Dax on April 24, 2001 12:21 PM

Hi,

I take it you have inserted this code into the worksheet code module. If not, right click the worksheet tab, paste the code and paste this amended code:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim FSObj As Object
Dim TypePath As String, Answer As Long
Set FSObj = CreateObject("Scripting.FilesystemObject")

'Will only expect path names in column 1 (A). Change
'this value for a different column e.g. D=4
If Target.Column <> 4 Then Exit Sub

TypePath = Target.Value
If TypePath = "" Then Exit Sub
If FSObj.FolderExists(TypePath) = False Then
Answer = MsgBox("This folder does not exist. Would" & vbCrLf & _
"you like to create it?", vbYesNo, "Folder doesn't exist")
If Answer = vbYes Then
FSObj.CreateFolder TypePath
End If
End If

End Sub