Checking if a valid file exists

[GLT]

Hi,

I have some VBA scanning directories and I need to:

1) Check to see if a valid file exists (at the moment I assume its there,
and VBA bombs out if its not)

2) If #1 above is OK (i.e. if the file does exist) then extract the last
modification date / time

Any help is always greatly appreciated.

Cheers,
GLT.

 

[Daniel Pineault]

Try something like

'---------------------------------------------------------------------------------------
' Procedure : FileExist
' DateTime : 2007-Mar-06 13:51
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : test for the existance of a file
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFile - name of the file to be tested fo
'---------------------------------------------------------------------------------------
Function FileExist(strFile As String) As Boolean
On Error GoTo FileExist_Error

FileExist = False
If Len(Dir(strFile)) > 0 Then
FileExist = True
End If

FileExist_Error:
If Err.Number <> 0 Then
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: ModExtFiles / FileExist" &
vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
End If
Exit Function
End Function

If it returns true then you can simply use the FileDateTime() function to
detrmine the last modified date of the file.

 

[GLT]

Hi Daniel,

Thanks for your reply, I actually tried something like this earlier but kept
getting a '53 - file Not Found error'.

What I actually found in another post was a module which detects the '53 -
file not found error' error then determines from that if the file actually
exists.

I used this code here and created a module (Where JLName is the name of the
file and path):

Public Function FileExists(JLName As String) As Boolean
Dim intFileLength As Integer
On Error Resume Next

intFileLength = FileLen(strFileSpec)

If Err = 53 Then
FileExists = False
Else
FileExists = True
End If

End Function

Problem is, if I type this into the immediate window:

FileLen("\\mm1t4fp1\APPS\BKUPEXEC\LOG\jljob.dat") - it returns nothing.

If I debug the module above, it processes the error as a false (it should be
true).

If I type the following in Windows Explorer:

\\mm1t4fp1\APPS\BKUPEXEC\LOG\jljob.dat

the file attempts to open just fine.

Can anyone advise why I cannot get FileLen to return a value?

Any help is always greatly appreciated..

Cheers,
GLT.

 

[Douglas J. Steele]

In the Immediate window, you need to put a question mark in front of the
function if you want a value returned:

?FileLen("\\mm1t4fp1\APPS\BKUPEXEC\LOG\jljob.dat")

Note that you can simply use:

If Len(Dir("\\mm1t4fp1\APPS\BKUPEXEC\LOG\jljob.dat")) > 0 Then
' File exists
Else
'File does not exist
End If

 

[GLT]

Hi Douglas,

Thanks for your reply - I put a question mark in front of the line in the
immediate window and it returned 9.

I then tried modifying the code as follows (it first checks to see what type
of server it is, then sets the filename according to the server name).

No matter which way I try to program this I either get a 53 - File not found
error, or it get processed as a false when it is true.

The 'F' varible works with no problems at all - I am at a loss to understand
why one works perfectly and the other does not.

Can anyone offer any suggestions?

Cheers,
GLT.

If IsCluster Then

F = LCase(Dir("\\" & rsClusterList.Fields("ClstrFull") & "\" &
rsClusterList.Fields("ClstrVolume") & "\bkupexec\log\0000.*"))
JLName = ("\\" & rsClusterList.Fields("ClstrFull") & "\" &
rsClusterList.Fields("ClstrVolume") & "\bkupexec\log\jljob.dat")

Else

F = LCase(Dir("\\" & myClient & "\apps\bkupexec\log\0000.*"))
JLName = ("\\" & myClient & "\apps\bkupexec\log\jljob.dat")

'IF#4
End If

DoEvents

If IsCluster Then

F = LCase(Dir("\\" & rsClusterList.Fields("ClstrFull") & "\"
& rsClusterList.Fields("ClstrVolume") & "\bkupexec\log\0000*.*"))
'JLName = LCase(Dir("\\" & rsClusterList.Fields("ClstrFull")
& "\" & rsClusterList.Fields("ClstrVolume") & "\bkupexec\log\jljob.dat"))

Else

F = LCase(Dir("\\" & myClient & "\apps\bkupexec\log\0000*.*"))
'JLName = LCase(Dir("\\" & myClient &
"\apps\bkupexec\log\jljob.dat"))

End If

'Test the 'jljob.dat' file - if it has the same date as today,
then it means there
'is an outstanding message. Set MyBkpAlert to '1' if there is
an alert.


If Len(Dir(JLName)) > 0 Then

JLDate = FileDateTime(JLName)

FrmJLDate = Format(JLDate, "ddmmyy")
FrmNow = Format((Now() - ProcDay), "ddmmyy")

If FrmJLDate = FrmNow Then

MyBkpAlert = 1

Else

MyBkpAlert = 0

End If

End If

 

[Douglas J. Steele]

What line of code raises the error?

 

[GLT]

Hi Douglas,

I set

JLName = "" (just as a test)

This line processes JLName as true when it should be false:

If Len(Dir(JLName)) > 0 Then

Then this line returns a 53 - File Not Found error:

JLDate = FileDateTime(JLName)

I'm starting to think maybe I have a string problem - should JLName show up
as (when I put it in a messagebox):

"//pathname/filename"

or

//pathname/filename

Cheers,
GLT.

ie

 

[GLT]

Hi,

I think I have worked out the problem, but still don't know what the
solution is:

It appears when I tested this in the immediate window:

?Len(Dir("\\mm1t4fp1\APPS\BKUPEXEC\LOG\jljob.dat"))

returns 9

If I renamed the two directories BKPEXEC or LOG (to something that doesnt
exist) and ran the same test above, it returns a null.

However, if I renamed the server or the 1st directory (APPS) to something
that didnt exist, then it returns an error 52 - Bad Filename

So I am guessing that the DIR function only validates part of the pathname
and the filename?

Cheers,
GLT

 

[Douglas J. Steele]

Yes, I encounter problems if the server or share don't exist. Why, though,
would you have such a situation?

 

[GLT]

Hi Douglas,

Thanks for your reply - my code is scanning through a bunch of servers (it
gets the list of servers from another database via sql).

There are some servers that our team are not responsible for which do not
have the backup software installed, so by searching for that directory / file
(and having a true result returned) is the way the servers logs get processed.

Also, sometimes if there is a comms problem for a server, files will not be
found.

I think I have worked out the problem I was having (it actually consisted of
two problems):

1) I was using the DIR function for two different situations, but at the
same time.
I found a post for VBA Excel which cautioned if the DIR function is used and
then files are scanned in other routines (which is the case for me), then
using the DIR function for another purpose while processing other files will
cause them to 'loose' or 'reset' their location. When I used the code listed,
the file processing stopped working.

--------------------------------------------
** Thanks to Pat Obrien for this **

'Just a quick caution….

If you’re using the DIR command to loop through files, and are calling other
routines to process the files, the other routines cannot have their own DIR
command used in them.

This resets the location that the DIR command looks at and subsequent calls
to DIR in the main routine will fail.'

-----------------------------------------
So from the code I posted earlier, I moved the JLName out of the file
processing loop, and the files started processing again.

F = LCase(Dir("\\" & myClient & "\apps\bkupexec\log\0000.*"))
JLName = ("\\" & myClient & "\apps\bkupexec\log\jljob.dat")

2) Once I did the above, and then tried your code, it worked perfectly - no
errors even if the server does not exist.

Cheers,
GLT.

 

[Douglas J. Steele]

Yeah, sorry, I should have mentioned that. Glad you got it working.

 

[lloyd sturge]

Does anybody know of a work around for Access 2003 or another way to find out if a file exists using the UNC format '\\serverath\file.mdb'?

Any help would be appreciated! Thanks!

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[Dirk Goldgar]

Does anybody know of a work around for Access 2003 or another way to find
out if a file exists using the UNC format '\\serverath\file.mdb'?

Any help would be appreciated! Thanks!

That doesn't looke right. I believe it should be:

Dir(" \\server\path\file.mdb ")

(except that I had to put spaces after the opening quote and before the
leading quote, just to keep Windows Mail from "fixing" the URL and making it
a hyperlink).

 

[Douglas J. Steele]

Dir() works fine for me on UNCs, as long as the server and share names are
legitimate. (of course, the colon in \\serverath\file.mdb means it isn't a
valid UNC...)