Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
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. In article , "JMay" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
Or remove: & "\*.*" To give you: X = Dir(PathName) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "JMay" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
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 "JE McGimpsey" wrote in message : 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. In article , "JMay" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
Thanks Jim,
Yes, that's what I decided I needed to do, based on JE's comment. Thanks for "nailing-it"!! JMay "Jim Cone" wrote in message : Or remove: & "\*.*" To give you: X = Dir(PathName) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "JMay" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
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 "JMay" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand results of UDF
This line:
x = Dir(PathName & "\*.*") 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").FileExi sts(strFileName) End Function "JMay" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gap or something I cannot understand | Excel Discussion (Misc queries) | |||
not understand | New Users to Excel | |||
I don't Understand | Excel Worksheet Functions | |||
Help me understand this code | Excel Discussion (Misc queries) | |||
< |
Excel Worksheet Functions |