Good morning,

I have cells C5:C10 (merged cells) in my worksheet "Sheet1" where users can
type in a path, including the file name (e.g. "C:\File.xls"), that users want
to save the workbook.

I need some help to write a code to validate the path to make sure that the
path is correct. If the path is not correct, I want to message the users
that the path you have entered is not a correct path format.

This code will be part of if - then statement of my other code.

Please help.

Thanks.

Dim strFile as String
strFile = Trim(Range("C5"))

If Dir(Left(strFile, InStrRev(strFile, "\")), vbDirectory) = "" Then
MsgBox "Invalid Path"
End If

PS: Alternatively you can also use FSO FileSystemObject to validate...

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


"Souny" wrote:

Good morning,

I have cells C5:C10 (merged cells) in my worksheet "Sheet1" where users can
type in a path, including the file name (e.g. "C:\File.xls"), that users want
to save the workbook.

I need some help to write a code to validate the path to make sure that the
path is correct. If the path is not correct, I want to message the users
that the path you have entered is not a correct path format.

This code will be part of if - then statement of my other code.

Please help.

Thanks.

Using FSO..

Dim strFile As String, fso As Object
strFile = Trim(Range("C5"))

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(Left(strFile, InStrRev(strFile, "\") - 1)) Then
MsgBox "Folder exists"
End If

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


"Jacob Skaria" wrote:

Dim strFile as String
strFile = Trim(Range("C5"))

If Dir(Left(strFile, InStrRev(strFile, "\")), vbDirectory) = "" Then
MsgBox "Invalid Path"
End If

PS: Alternatively you can also use FSO FileSystemObject to validate...

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


"Souny" wrote:

Good morning,

I have cells C5:C10 (merged cells) in my worksheet "Sheet1" where users can
type in a path, including the file name (e.g. "C:\File.xls"), that users want
to save the workbook.

I need some help to write a code to validate the path to make sure that the
path is correct. If the path is not correct, I want to message the users
that the path you have entered is not a correct path format.

This code will be part of if - then statement of my other code.

Please help.

Thanks.

Modify to suit:

Sub test()

'Establish file name somehow
MyPath = "C:\Documents and Settings\My Documents\My Book.xls"

'Checks for validity
Check = Dir(MyPath)
If Check = "" Then
MsgBox "Invalid file name"
End If
'Rest of your code
'would then go here

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Souny" wrote:

Good morning,

I have cells C5:C10 (merged cells) in my worksheet "Sheet1" where users can
type in a path, including the file name (e.g. "C:\File.xls"), that users want
to save the workbook.

I need some help to write a code to validate the path to make sure that the
path is correct. If the path is not correct, I want to message the users
that the path you have entered is not a correct path format.

This code will be part of if - then statement of my other code.

Please help.

Thanks.

the good ol' DIR() still lives. With it, you get the file name or nothing ...

Option Explicit
Sub test()
MsgBox ValidPath("C:\Temp\LOG_SSG1_2009-09-09.log")
End Sub
Function ValidPath(spath As String) As Boolean
Dim sname As String
sname = Dir(spath)
ValidPath = sname < ""
End Function

"Souny" wrote:

Good morning,

I have cells C5:C10 (merged cells) in my worksheet "Sheet1" where users can
type in a path, including the file name (e.g. "C:\File.xls"), that users want
to save the workbook.

I need some help to write a code to validate the path to make sure that the
path is correct. If the path is not correct, I want to message the users
that the path you have entered is not a correct path format.

This code will be part of if - then statement of my other code.

Please help.

Thanks.