Don't understand results of UDF

[JMay]

In cell B4 I have (as text):

C:\Documents and Settings\XlsFileSavedAsText.txt

In Cell C2 I have:

=PathExists(B4)

In my Module1 I have:

Private Function PathExists(PathName As String) As Boolean
' Returns True If PathExists
On Error GoTo NoPath
x = Dir(PathName & "\*.*")
If x = "" Then GoTo NoPath
PathExists = True
Exit Function
NoPath:
PathExists = False
End Function

Why is my Cell C2 showing False
when I definitely
C:\Documents and Settings\XlsFileSavedAsText.txt Exists?

TIA,

Jim

 

[JE McGimpsey]

Because

C:\Documents and Settings\XlsFileSavedAsText.txt\*.*

definitely DOESN'T exist.

Your UDF is looking for a Path, not the entire filename.

If you remove the "\XlsFileSavedAsText.txt" from B3, then PathExists
will return TRUE.

 

[Jim Cone]

Or remove:
& "\*.*"
To give you:
X = Dir(PathName)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JMay" <[email protected]>
wrote in message
In cell B4 I have (as text):
C:\Documents and Settings\XlsFileSavedAsText.txt
In Cell C2 I have:
=PathExists(B4)
In my Module1 I have:

Private Function PathExists(PathName As String) As Boolean
' Returns True If PathExists
On Error GoTo NoPath
x = Dir(PathName & "\*.*")
If x = "" Then GoTo NoPath
PathExists = True
Exit Function
NoPath:
PathExists = False
End Function

Why is my Cell C2 showing False
when I definitely
C:\Documents and Settings\XlsFileSavedAsText.txt Exists?
TIA,
Jim

 

[JMay]

JE - Thanks for clearing up my confusion. Obviously,
the Code searches at the Folder level ONLY;
It was the reference in the code *.* that confused me.
This should never happen again. LOL
Tks,
Jim

 

[JMay]

Thanks Jim,
Yes, that's what I decided I needed to do,
based on JE's comment. Thanks for "nailing-it"!!
JMay

 

[Deborah Digby]

Are you looking to see if the path exists or the file itself exists? (You
parameter name is confusing as you are passing the full flilename not just
the path) If you want to check that the file exists then uses this syntax

x = dir(Pathname)

if you want to check if the path exists then

x = dir(left(pathname, instrrev(pathname,"\")) & "*.*")
(ideally replace "\" with application.pathseparator to allow for different
filesystems)

what you have coded means "does C:\Documents and
Settings\XlsFileSavedAsText.txt\*.* exist?", to which the answer is no.

hope this helps,
D

 

[Guest]

This line:
instructs the UDF to search for

C:\Documents and Settings\XlsFileSavedAsText.txt\*.*

Since xlsFileSavedAsText.txt is a file, not a folder with files in it - it
returns false. You could remove the & "\*.*" or try:


Private Function udfFileExists(strFileName As String) As Boolean
udfFileExists =
CreateObject("Scripting.FileSystemObject").FileExists(strFileName)
End Function