Don't understand results of UDF

J

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
 
J

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.
 
J

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
 
J

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
 
J

JMay

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

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top