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 doesnt it will create it. But I cant 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
