fs.FileExists: Inconsistent Answer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This should be simple, but I must be simple:
fs.FileExists() and Dir() both work differently with variable versus value
of variable.

In my code, I assign
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
and I assign a value to str_ftp_ToFile.

I break where I'm trying to see if the file (value of str_ftp_ToFile) exists
and open the immediate window. In the Immediate window, I type what you see
following the ? including the comment after '
You can see what vba gives me in the line after:
----------------
? str_ftp_ToFile 'gives me the correct value for the file name:
"Y:\0Costpoint\PPDs\2007\P08_Aug\Paydate 2007-08-31\rpt402_735.csv"
? fs.FileExists("Y:\0Costpoint\PPDs\2007\P08_Aug\Paydate
2007-08-31\rpt402_735.csv") 'and it gives me Correct Answer True:
True
? fs.FileExists(str_ftp_ToFile) 'and it gives me Incorrect Answer when I
use the variable:
False
? str_ftp_ToFile 'and it still gives me the same value for the file
name:
"Y:\0Costpoint\PPDs\2007\P08_Aug\Paydate 2007-08-31\rpt402_735.csv"
? Dir("Y:\0Costpoint\PPDs\2007\P08_Aug\Paydate 2007-08-31\rpt402_735.csv")
'and Dir() gives me:
rpt402_735.csv
? Dir(str_ftp_ToFile)
' and I get Run-time error '52': Bad file name or number

What am I missing?
Why, when I paste the value of the variable into these functions, does it
give me an answer different from when I use the variable?
The variable is dimensioned str_ftp_ToFile As String.

Thank you
George
 
Works fine for me.

How do you assign the value to str_ftp_ToFile? (I'm just wondering whether
you're introducing a blank or unprintable character that you're not aware
of)
 
Good thought. I tried Trim(str_ftp_ToFile). That's why I started pasting
the value of str_ftp_ToFile instead of the variable.

My current theory is that it has something to do with Y:\ is Mapped Network
Drive.
Perhaps the literal is easier for FileExists() to recognize and handle
Network Drive.

George
 
The code below indicates how str_ftp_ToFile is assigned. At your suggestion,
I added the Excel Clean() function to strip-out unprintable characters.
I also tried assigning the network \\unc\ mapped value.
That too gave True for the literal and False for the variable.

Private Function TestFilesFound()
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim str_ftp_FromFile As String, str_ftp_ToFile As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryA40b Files to Download")
With rst
If Not .EOF Then
.MoveFirst
Do While Not .EOF
str_ftp_FromFile = !Source
str_ftp_ToFile = Chr(34) & Clean(!Directory) & "\" &
Clean(!Target) & Chr(34)
Debug.Print Dir(PathOnly(str_ftp_ToFile) & "\", vbDirectory)
Debug.Print fs.FileExists(str_ftp_ToFile), str_ftp_ToFile
' Debug.Print Dir(str_ftp_ToFile)
fHandleFile str_ftp_ToFile, 1
.MoveNext
Loop
End If
End With
Set rst = Nothing
Set fs = Nothing
End Function

Function Clean(strString As String) As String
'Google: Non-Printable Characters Conversion Function
Clean = Excel.WorksheetFunction.Clean(strString)
End Function
 
You were correct. The problem was how I assigned the value. For reasons
forgotten, I was enclosing the file name in quotes, Chr(34). When I took
that out, FileExists() worked. I makes sense (now) that vba knew
str_ftp_ToFile was a string, and didn't need the quotes

Thanks for your help.
 
Back
Top