I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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



MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

I'd just try to make the directory and ignore the error if it's already there.

on error resume next
mkdir strpath
on error goto 0

This assumes that the strpath is one level deep.

If you have to make multiple levels:

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

bigjim wrote:

I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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

MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

--

Dave Peterson

That explains it. I didn't know it wouldn't go more than one layer at a
time. That presents a problem. I think I'll just check if the directory
exists and if it doesn't use a msgbox to alert the uset to make the directory
theirself and end the program. Thanks for your help.

Jim

"Dave Peterson" wrote:

I'd just try to make the directory and ignore the error if it's already there.

on error resume next
mkdir strpath
on error goto 0

This assumes that the strpath is one level deep.

If you have to make multiple levels:

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

bigjim wrote:

I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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

MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

--

Dave Peterson

Or you could use a Windows API.

This sample 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


bigjim wrote:

That explains it. I didn't know it wouldn't go more than one layer at a
time. That presents a problem. I think I'll just check if the directory
exists and if it doesn't use a msgbox to alert the uset to make the directory
theirself and end the program. Thanks for your help.

Jim

"Dave Peterson" wrote:

I'd just try to make the directory and ignore the error if it's already there.

on error resume next
mkdir strpath
on error goto 0

This assumes that the strpath is one level deep.

If you have to make multiple levels:

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

bigjim wrote:

I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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

MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

--

Dave Peterson


--

Dave Peterson

Dave: Is ther a reason you didn't use

object.FolderExists(folderspec)

?????

"Dave Peterson" wrote:

Or you could use a Windows API.

This sample 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


bigjim wrote:

That explains it. I didn't know it wouldn't go more than one layer at a
time. That presents a problem. I think I'll just check if the directory
exists and if it doesn't use a msgbox to alert the uset to make the directory
theirself and end the program. Thanks for your help.

Jim

"Dave Peterson" wrote:

I'd just try to make the directory and ignore the error if it's already there.

on error resume next
mkdir strpath
on error goto 0

This assumes that the strpath is one level deep.

If you have to make multiple levels:

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

bigjim wrote:

I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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

MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

--

Dave Peterson


--

Dave Peterson

If all I want to do is test to see if the directory exists, I'd use something
like:

Dim teststr as string
teststr = ""
on error resume next
teststr = dir("somepathhere\" & "nul")
on error goto 0
if teststr = "" then
'not there
else
'is there
end if

Or even use dir() with vbdirectory...

I don't see a real good need to create another object when I can stay in native
VBA.

Besides, after I get the results from FSO.folderexists, I still may need to
create the folder.

So if I know the structure, why not just use mkdir and ignore any errors.

And if I don't want know the structure (and don't want to parse it), I'd use the
API (if I could find it!).


joel wrote:

Dave: Is ther a reason you didn't use

object.FolderExists(folderspec)

?????

"Dave Peterson" wrote:

Or you could use a Windows API.

This sample 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


bigjim wrote:

That explains it. I didn't know it wouldn't go more than one layer at a
time. That presents a problem. I think I'll just check if the directory
exists and if it doesn't use a msgbox to alert the uset to make the directory
theirself and end the program. Thanks for your help.

Jim

"Dave Peterson" wrote:

I'd just try to make the directory and ignore the error if it's already there.

on error resume next
mkdir strpath
on error goto 0

This assumes that the strpath is one level deep.

If you have to make multiple levels:

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

bigjim wrote:

I am using excel 2003. The user enters a path name where he wants to save a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save it.

Worksheets("ticket").Activate

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

MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

ine could use the FileSystemObject and a UDF to show on the worksheet
whether its a good folder or not.

In the Developer environment, under the Tools menu, select References
scroll down until you see Microsoft Scripting Runtime....check the box and
click OK

then in a code module, add the UDF:

Function FolderExists(sText As String) As Boolean
With New FileSystemObject
FolderExists = .FolderExists(sText)
End WithEnd Function
End Function


in a scpreadsheet, type some file path in any cell, say B5, then in the
adjascent cell
=FolderExists(B5)
it will show TRUE or False appropriately



"bigjim" wrote in message
...
I am using excel 2003. The user enters a path name where he wants to save
a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save
it.

Worksheets("ticket").Activate

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



MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

Hello Patrick,

Just a quick couple of questions related to your solution: Does the
Microsoft Scripting Runtime box need to be checked on each separate machine
that the macro runs on? And if so, then is there a way to bypass that like
check the box via VBA?
--
rpw


"Patrick Molloy" wrote:

ine could use the FileSystemObject and a UDF to show on the worksheet
whether its a good folder or not.

In the Developer environment, under the Tools menu, select References
scroll down until you see Microsoft Scripting Runtime....check the box and
click OK

then in a code module, add the UDF:

Function FolderExists(sText As String) As Boolean
With New FileSystemObject
FolderExists = .FolderExists(sText)
End WithEnd Function
End Function


in a scpreadsheet, type some file path in any cell, say B5, then in the
adjascent cell
=FolderExists(B5)
it will show TRUE or False appropriately



"bigjim" wrote in message
...
I am using excel 2003. The user enters a path name where he wants to save
a
file. I need this code to check to see if the directory they enter exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to save
it.

Worksheets("ticket").Activate

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



MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

once the reference is selected, this remains so until deselected. so long as
the dll exists on each users' pc, there's no need to do anything further


"rpw" wrote in message
...
Hello Patrick,

Just a quick couple of questions related to your solution: Does the
Microsoft Scripting Runtime box need to be checked on each separate
machine
that the macro runs on? And if so, then is there a way to bypass that
like
check the box via VBA?
--
rpw


"Patrick Molloy" wrote:

ine could use the FileSystemObject and a UDF to show on the worksheet
whether its a good folder or not.

In the Developer environment, under the Tools menu, select References
scroll down until you see Microsoft Scripting Runtime....check the box
and
click OK

then in a code module, add the UDF:

Function FolderExists(sText As String) As Boolean
With New FileSystemObject
FolderExists = .FolderExists(sText)
End WithEnd Function
End Function


in a scpreadsheet, type some file path in any cell, say B5, then in the
adjascent cell
=FolderExists(B5)
it will show TRUE or False appropriately



"bigjim" wrote in message
...
I am using excel 2003. The user enters a path name where he wants to
save
a
file. I need this code to check to see if the directory they enter
exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to
save
it.

Worksheets("ticket").Activate

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



MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned
to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim

Thank you for the answer!
--
rpw


"Patrick Molloy" wrote:

once the reference is selected, this remains so until deselected. so long as
the dll exists on each users' pc, there's no need to do anything further


"rpw" wrote in message
...
Hello Patrick,

Just a quick couple of questions related to your solution: Does the
Microsoft Scripting Runtime box need to be checked on each separate
machine
that the macro runs on? And if so, then is there a way to bypass that
like
check the box via VBA?
--
rpw


"Patrick Molloy" wrote:

ine could use the FileSystemObject and a UDF to show on the worksheet
whether its a good folder or not.

In the Developer environment, under the Tools menu, select References
scroll down until you see Microsoft Scripting Runtime....check the box
and
click OK

then in a code module, add the UDF:

Function FolderExists(sText As String) As Boolean
With New FileSystemObject
FolderExists = .FolderExists(sText)
End WithEnd Function
End Function


in a scpreadsheet, type some file path in any cell, say B5, then in the
adjascent cell
=FolderExists(B5)
it will show TRUE or False appropriately



"bigjim" wrote in message
...
I am using excel 2003. The user enters a path name where he wants to
save
a
file. I need this code to check to see if the directory they enter
exists
and if not I want to make the directory for them. This is the code I'm
using, but I get the error path not found when the program tries to
save
it.

Worksheets("ticket").Activate

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



MsgBox strpath

Rem making directory if it doesn't exist
If Dir(strpath, vbDirectory) = "" Then MkDir strpath

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

ThisWorkbook.SaveAs Filename:=fsavename

I am using the msgbox to make sure the path entered is getting assigned
to
strpath and it is. J8 and C8 are the file name. In his case strpath =
c:\2008\Jun\ J8 = C005482 and C8 = Encana. I checked and the folder
c:\2008\jun\ was not created.

Any help would be appreciated.

Jim