I'd like to test for a folder path (Z:\FolderName) and if it doesn't exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

Hi Barb

You can find code on this page to test
http://www.rondebruin.nl/exist.htm

One way is this

Sub Test_Folder_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
Dim FSO As Object
Dim FolderPath As String

Set FSO = CreateObject("scripting.filesystemobject")

FolderPath = "C:\My Folder"
If Right(FolderPath, 1) < "\" Then
FolderPath = FolderPath & "\"
End If

If FSO.FolderExists(FolderPath) = False Then
FSO.CreateFolder (FolderPath)
Else
MsgBox "Folder exist"
End If

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Barb Reinhardt" wrote in message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

I came up with this and it seems to work.

Sub TestForPath(myPath As String)
Dim FSO
Dim myFolderPath As String
Dim myFolderName As String

Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(myPath) Then Exit Sub

myFolderName = Right(myPath, Len(myPath) - InStrRev(myPath, "\"))
myFolderPath = Left(myPath, InStrRev(myPath, "\") - 1)

Call TestForPath(myFolderPath)
'Create new folder
MkDir (myPath)

End Sub




"Barb Reinhardt" wrote:

I'd like to test for a folder path (Z:\FolderName) and if it doesn't exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

' What if the directory doesn't exist
If Len(Dir("Z:\FolderName")) = 0 Then
'Create the Directory
MkDir ("Z:\Foldername")
End If

--
Rui Caetano
IT Manager
Township of East Hanover, NJ

"Barb Reinhardt" wrote in message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

Z: is always there?

on error resume next
mkdir "Z:\foldername"
on error goto 0

If it's there, the mkdir will fail, but who cares???
If it's not there, then the mkdir will create it.



Barb Reinhardt wrote:

I'd like to test for a folder path (Z:\FolderName) and if it doesn't exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

--

Dave Peterson

After my signature is some code that I have posted in the past to the
compiled VB newsgroups, but which will work fine in Excel VBA as well. Note
that it is presented as a Function which means you can check if it worked
okay or not using a structure like this...

If MakeDirectoryPath("c:\dir1\dir2\dir3\dir4") Then
MsgBox "Directory path created successfully"
Else
MsgBox "An error occurred while trying to create that directory"
End If

allowing you to take any steps necessary for either case. Or, if you want to
omit the check, you can call it directly as if it were a subroutine (you can
do this for any function) using either this...

MakeDirectoryPath "c:\dir1\dir2\dir3\dir4"

or this...

Call MakeDirectoryPath("c:\dir1\dir2\dir3\dir4")

Rick

Here is a subroutine (VB6) that will create of all the sub-directories (that
do not already exist) for a specified path argument. The error checking was
made up off the top of my head, but I think it covers the problems that one
might encounter. Note that the path must start with a drive letter, followed
by a colon, followed by a backslash, followed by the directories path.

Function MakeDirectoryPath(ByVal Path As String) As Boolean
Dim X As Long
Dim NewPath As String
Dim Parts() As String
On Error GoTo OOPS
If Path Like "[a-zA-Z]:\*" And InStr(Path, "\\") = 0 Then
If Len(Dir$(Left$(Path, 3))) = 0 Then Exit Function
Parts = Split(Path, "\")
Parts(0) = Parts(0) & "\"
NewPath = Parts(0)
For X = 0 To UBound(Parts)
If Len(Dir$(NewPath, vbDirectory)) = 0 Then MkDir NewPath
If Right$(NewPath, 1) < "\" Then NewPath = NewPath & "\"
If X < UBound(Parts) Then NewPath = NewPath & Parts(X + 1)
Next
MakeDirectoryPath = True
End If
OOPS:
End Function


"Barb Reinhardt" wrote in message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

Sub Test
Dim s as String
s="Z:\FolderName"
If Dir(s, vbDirectory)="" then MkDir s
End Sub

Here's something that Jim Rech Posted:

Option Explicit
Declare Function MakePath Lib "imagehlp.dll" Alias _
"MakeSureDirectoryPathExists" (ByVal lpPath As String) As Long

Sub Test()
MakeDir "c:\aaa\bbb"
End Sub

Sub MakeDir(DirPath As String)
If Right(DirPath, 1) < "\" Then DirPath = DirPath & "\"
MakePath DirPath
End Sub



"Rick Rothstein (MVP - VB)" wrote:

After my signature is some code that I have posted in the past to the
compiled VB newsgroups, but which will work fine in Excel VBA as well. Note
that it is presented as a Function which means you can check if it worked
okay or not using a structure like this...

If MakeDirectoryPath("c:\dir1\dir2\dir3\dir4") Then
MsgBox "Directory path created successfully"
Else
MsgBox "An error occurred while trying to create that directory"
End If

allowing you to take any steps necessary for either case. Or, if you want to
omit the check, you can call it directly as if it were a subroutine (you can
do this for any function) using either this...

MakeDirectoryPath "c:\dir1\dir2\dir3\dir4"

or this...

Call MakeDirectoryPath("c:\dir1\dir2\dir3\dir4")

Rick

Here is a subroutine (VB6) that will create of all the sub-directories (that
do not already exist) for a specified path argument. The error checking was
made up off the top of my head, but I think it covers the problems that one
might encounter. Note that the path must start with a drive letter, followed
by a colon, followed by a backslash, followed by the directories path.

Function MakeDirectoryPath(ByVal Path As String) As Boolean
Dim X As Long
Dim NewPath As String
Dim Parts() As String
On Error GoTo OOPS
If Path Like "[a-zA-Z]:\*" And InStr(Path, "\\") = 0 Then
If Len(Dir$(Left$(Path, 3))) = 0 Then Exit Function
Parts = Split(Path, "\")
Parts(0) = Parts(0) & "\"
NewPath = Parts(0)
For X = 0 To UBound(Parts)
If Len(Dir$(NewPath, vbDirectory)) = 0 Then MkDir NewPath
If Right$(NewPath, 1) < "\" Then NewPath = NewPath & "\"
If X < UBound(Parts) Then NewPath = NewPath & Parts(X + 1)
Next
MakeDirectoryPath = True
End If
OOPS:
End Function

"Barb Reinhardt" wrote in message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt


--

Dave Peterson

Yes, there is almost always an API solution (after all, that is what lies at
the heart of Window). I usually try and stay away from API solutions
(advocating straight VB coded solutions if possible) as they tend to be
hard-to-use, convoluted atrocities most of the time; however, this one is a
relatively tame API function, so I would have no problem recommending its
use.

Rick


"Dave Peterson" wrote in message
...
Here's something that Jim Rech Posted:

Option Explicit
Declare Function MakePath Lib "imagehlp.dll" Alias _
"MakeSureDirectoryPathExists" (ByVal lpPath As String) As Long

Sub Test()
MakeDir "c:\aaa\bbb"
End Sub

Sub MakeDir(DirPath As String)
If Right(DirPath, 1) < "\" Then DirPath = DirPath & "\"
MakePath DirPath
End Sub



"Rick Rothstein (MVP - VB)" wrote:

After my signature is some code that I have posted in the past to the
compiled VB newsgroups, but which will work fine in Excel VBA as well.
Note
that it is presented as a Function which means you can check if it worked
okay or not using a structure like this...

If MakeDirectoryPath("c:\dir1\dir2\dir3\dir4") Then
MsgBox "Directory path created successfully"
Else
MsgBox "An error occurred while trying to create that directory"
End If

allowing you to take any steps necessary for either case. Or, if you want
to
omit the check, you can call it directly as if it were a subroutine (you
can
do this for any function) using either this...

MakeDirectoryPath "c:\dir1\dir2\dir3\dir4"

or this...

Call MakeDirectoryPath("c:\dir1\dir2\dir3\dir4")

Rick

Here is a subroutine (VB6) that will create of all the sub-directories
(that
do not already exist) for a specified path argument. The error checking
was
made up off the top of my head, but I think it covers the problems that
one
might encounter. Note that the path must start with a drive letter,
followed
by a colon, followed by a backslash, followed by the directories path.

Function MakeDirectoryPath(ByVal Path As String) As Boolean
Dim X As Long
Dim NewPath As String
Dim Parts() As String
On Error GoTo OOPS
If Path Like "[a-zA-Z]:\*" And InStr(Path, "\\") = 0 Then
If Len(Dir$(Left$(Path, 3))) = 0 Then Exit Function
Parts = Split(Path, "\")
Parts(0) = Parts(0) & "\"
NewPath = Parts(0)
For X = 0 To UBound(Parts)
If Len(Dir$(NewPath, vbDirectory)) = 0 Then MkDir NewPath
If Right$(NewPath, 1) < "\" Then NewPath = NewPath & "\"
If X < UBound(Parts) Then NewPath = NewPath & Parts(X + 1)
Next
MakeDirectoryPath = True
End If
OOPS:
End Function

"Barb Reinhardt" wrote in
message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to
do
this?

Thanks,
Barb Reinhardt


--

Dave Peterson

Ron,

How do you write such code to call Dialog box to retrieve a folders path and
input the path into cell A1 in Sheet1 (assuming there's a button next to this
cell that would call this dialog)?

I thank you for sharing your expertise with us,
Adnan



"Ron de Bruin" wrote:

Hi Barb

You can find code on this page to test
http://www.rondebruin.nl/exist.htm

One way is this

Sub Test_Folder_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
Dim FSO As Object
Dim FolderPath As String

Set FSO = CreateObject("scripting.filesystemobject")

FolderPath = "C:\My Folder"
If Right(FolderPath, 1) < "\" Then
FolderPath = FolderPath & "\"
End If

If FSO.FolderExists(FolderPath) = False Then
FSO.CreateFolder (FolderPath)
Else
MsgBox "Folder exist"
End If

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Barb Reinhardt" wrote in message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't exist,
I'd like to create it. Can someone provide a snippet of code on how to do
this?

Thanks,
Barb Reinhardt

If you use the code at www.cpearson.com/Excel/BrowseFolder.aspx to display
the dialog, you can use code like the following,

Sub AAA()
Dim FName As String
FName = BrowseFolder("Choose A Folder")
If Len(FName) < 0 Then
Worksheets("Sheet1").Range("A1").Value = FName
End If
End Sub


Where BrowseFolder is the procedure that displays the folder dialog.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Adnan" (donotspam) wrote in message
...
Ron,

How do you write such code to call Dialog box to retrieve a folders path
and
input the path into cell A1 in Sheet1 (assuming there's a button next to
this
cell that would call this dialog)?

I thank you for sharing your expertise with us,
Adnan



"Ron de Bruin" wrote:

Hi Barb

You can find code on this page to test
http://www.rondebruin.nl/exist.htm

One way is this

Sub Test_Folder_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
Dim FSO As Object
Dim FolderPath As String

Set FSO = CreateObject("scripting.filesystemobject")

FolderPath = "C:\My Folder"
If Right(FolderPath, 1) < "\" Then
FolderPath = FolderPath & "\"
End If

If FSO.FolderExists(FolderPath) = False Then
FSO.CreateFolder (FolderPath)
Else
MsgBox "Folder exist"
End If

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Barb Reinhardt" wrote in
message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to
do
this?

Thanks,
Barb Reinhardt

Thank you sir, it worked.

Adnan


"Chip Pearson" wrote:

If you use the code at www.cpearson.com/Excel/BrowseFolder.aspx to display
the dialog, you can use code like the following,

Sub AAA()
Dim FName As String
FName = BrowseFolder("Choose A Folder")
If Len(FName) < 0 Then
Worksheets("Sheet1").Range("A1").Value = FName
End If
End Sub


Where BrowseFolder is the procedure that displays the folder dialog.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Adnan" (donotspam) wrote in message
...
Ron,

How do you write such code to call Dialog box to retrieve a folders path
and
input the path into cell A1 in Sheet1 (assuming there's a button next to
this
cell that would call this dialog)?

I thank you for sharing your expertise with us,
Adnan



"Ron de Bruin" wrote:

Hi Barb

You can find code on this page to test
http://www.rondebruin.nl/exist.htm

One way is this

Sub Test_Folder_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
Dim FSO As Object
Dim FolderPath As String

Set FSO = CreateObject("scripting.filesystemobject")

FolderPath = "C:\My Folder"
If Right(FolderPath, 1) < "\" Then
FolderPath = FolderPath & "\"
End If

If FSO.FolderExists(FolderPath) = False Then
FSO.CreateFolder (FolderPath)
Else
MsgBox "Folder exist"
End If

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Barb Reinhardt" wrote in
message
...
I'd like to test for a folder path (Z:\FolderName) and if it doesn't
exist,
I'd like to create it. Can someone provide a snippet of code on how to
do
this?

Thanks,
Barb Reinhardt