I am using excel 2003. In my workbook the user can enter the directory they
want to use for saving a file. They enter the path in cell B200. I want to
create the entered directory if it doesn't already exist. I am using this
code and it works if the directory doesn't exist, but if it already exists,
the program shuts down. I would appreciate any help. Strappend and str3
will be combined for the file name.

Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").Value
strpath = ActiveSheet.Range("b200").Value
str3 = ActiveSheet.Range("c8").Value

If Dir(strpath) = "" Then MkDir strpath

fsavename = strpath & strappend & str3 & ".xls"

Modify as

If Dir(strpath, vbDirectory) = "" Then MkDir strpath

If this post helps click Yes
---------------
Jacob Skaria


"bigjim" wrote:

I am using excel 2003. In my workbook the user can enter the directory they
want to use for saving a file. They enter the path in cell B200. I want to
create the entered directory if it doesn't already exist. I am using this
code and it works if the directory doesn't exist, but if it already exists,
the program shuts down. I would appreciate any help. Strappend and str3
will be combined for the file name.

Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").Value
strpath = ActiveSheet.Range("b200").Value
str3 = ActiveSheet.Range("c8").Value

If Dir(strpath) = "" Then MkDir strpath

fsavename = strpath & strappend & str3 & ".xls"

I'd just ignore any error:

on error resume next
mkdir "C:\test"
on error goto 0

of if it were several deep:

on error resume next
mkdir "C:\test"
mkdir "C:\test\test2"
mkdir "C:\test\test2\test3"
on error goto 0


bigjim wrote:

I am using excel 2003. In my workbook the user can enter the directory they
want to use for saving a file. They enter the path in cell B200. I want to
create the entered directory if it doesn't already exist. I am using this
code and it works if the directory doesn't exist, but if it already exists,
the program shuts down. I would appreciate any help. Strappend and str3
will be combined for the file name.

Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").Value
strpath = ActiveSheet.Range("b200").Value
str3 = ActiveSheet.Range("c8").Value

If Dir(strpath) = "" Then MkDir strpath

fsavename = strpath & strappend & str3 & ".xls"

--

Dave Peterson

Thank you so much. I'm still learning this stuff and I find it's usually a
pretty simple fix if you know what your doing. Obviously, you do. Thanks
again, Jim

"Jacob Skaria" wrote:

Modify as

If Dir(strpath, vbDirectory) = "" Then MkDir strpath

If this post helps click Yes
---------------
Jacob Skaria


"bigjim" wrote:

I am using excel 2003. In my workbook the user can enter the directory they
want to use for saving a file. They enter the path in cell B200. I want to
create the entered directory if it doesn't already exist. I am using this
code and it works if the directory doesn't exist, but if it already exists,
the program shuts down. I would appreciate any help. Strappend and str3
will be combined for the file name.

Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").Value
strpath = ActiveSheet.Range("b200").Value
str3 = ActiveSheet.Range("c8").Value

If Dir(strpath) = "" Then MkDir strpath

fsavename = strpath & strappend & str3 & ".xls"

of if it were several deep:

mkdir "C:\test"
mkdir "C:\test\test2"
mkdir "C:\test\test2\test3"


Here's a little UDF for handling several sub directories in one step:

http://www.EXCELGAARD.dk/Lib/UDFs/MAKEPATH/

CE

Or you could use a Windows API.

This samle loops through a range of cells and creates folders by the values in
those cells.

(saved from a previous post)

You may want to test to see if the folder exists after the attempt. (If you use
a mapped drive that doesn't exist (like x:), you may want to see a warning:

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

Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim myPath As String
Dim res As Long

With Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myPath = myCell.Value
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If
res = MakePath(myPath)
If res = 1 Then
'ok
Else
MsgBox myPath & " does not exist!"
End If
Next myCell

End Sub



Charlotte E wrote:

of if it were several deep:

mkdir "C:\test"
mkdir "C:\test\test2"
mkdir "C:\test\test2\test3"

Here's a little UDF for handling several sub directories in one step:

http://www.EXCELGAARD.dk/Lib/UDFs/MAKEPATH/

CE

--

Dave Peterson