File checker array

[VegasNath]

Code: Select all

Option Explicit

Function FileExists(strFullName As Variant) As Boolean
    FileExists = Not (Dir(strFullName) = "")
End Function

Function FileInUse(strFullName As String) As Boolean
    Dim f As Integer
    On Error Resume Next
    f = FreeFile
    Open strFullName For Input Lock Read As #f
    Close #f
    FileInUse = (Err = 70)
End Function

Function FileAvailable(strFullName As String) As Boolean
    If FileExists(strFullName) Then
        FileAvailable = Not FileInUse(strFullName)
    End If
End Function

Sub A01_RunDailyProcess()

'''''''''''''''''''''''''''
'File Checker with option to exit
    'Source Files
    arrFiles1 = Array( _
        "Report 5.2 excluding charged off accounts - " & Format(strDateBIFile, "yyyy-mm-dd") & " updated.xls", _
        "Report 5.8- - " & Format(strDateBIFile, "yyyy-mm-dd") & ".xls", _
        "Report 5.16-5.19 - " & Format(strDateBIFile, "yyyy-mm-dd") & ".xls", _
        "Replacement Account Query 1 - " & Format(strDateWork, "dd-mm-yy") & ".xls")
    'Destination Files
    arrFiles2 = Array( _
        "1. Month " & strMnth & " 5.8 Consolidated.xls", _
        "2. Month " & strMnth & " 5.17 Consolidated.xls", _
        "3. Month " & strMnth & " 5.18 Consolidated.xls", _
        "4. Month " & strMnth & " 5.19 Consolidated.xls", _
        "5. Month " & strMnth & " Daily Walk *.xls", _
        "5a. Month " & strMnth & " Replacement Accounts.xls", _
        "6. Month " & strMnth & " Calendarisation Impact and Total Performance To Date.xls")
        
    varInfo = ""
    For Each varFile In arrFiles1
        If FileAvailable(strPath1 & varFile) = False Then
            varInfo = varInfo & strPath1 & varFile & vbCrLf & vbCrLf
        End If
    Next
    For Each varFile In arrFiles2
        If FileAvailable(strPath2 & varFile) = False Then
            varInfo = varInfo & strPath2 & varFile & vbCrLf & vbCrLf
        End If
    Next

    If varInfo <> "" Then
        Msg = vbCrLf & "File Checker"
        Msg = Msg & vbCrLf & vbCrLf & "The following files are not available."
        Msg = Msg & vbCrLf & vbCrLf & varInfo
        Msg = Msg & vbCrLf & vbCrLf & "Process Aborted"
        MsgBox Msg, vbCritical, "File Checker"
        varInfo = ""
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Exit Sub
    Else
        Msg = vbCrLf & "File Checker"
        Msg = Msg & vbCrLf & vbCrLf & "All files are available."
        Msg = Msg & vbCrLf & vbCrLf & "Do you wish to continue?"
            If MsgBox(Msg, vbQuestion + vbYesNo, "Run Daily Process") = vbNo Then
                Msg = vbCrLf & "Run Daily Process"
                Msg = Msg & vbCrLf & vbCrLf & "Process Aborted."
                Msg = Msg & vbCrLf & vbCrLf
                MsgBox Msg, vbCritical, "Run Daily Process"
                Application.Calculation = xlCalculationAutomatic
                Application.ScreenUpdating = True
                Exit Sub
            End If
    End If

'''''''''''''''
End Sub

I am using the above code (thinned out for posting) to check if certain files a. exist and b. are not currently in use.

I am having a problem with the file: "5. Month " & strMnth & " Daily Walk *.xls" (Note the wildcard - There is further variable text in the filename)

If I rename the file (example precede the filename with an x), the file exists function works as expected.
However, if the file is in use, the Fileinuse function is not capturing the fact and continues incorrectly.
Any idea's what would cause this? The Fileinuse function works for all other files (without wildcards).

[HansV]

FileInUse tries to open the file named strFullName. This will obviously only work for a single filename - how could you open a range of files?
Moreover, it is unclear what FileInUse should return if some of the files matching the wildcard are in use and others aren't.

[VegasNath]

FileInUse tries to open the file named strFullName. This will obviously only work for a single filename - how could you open a range of files?

Does it? My limited understanding is that each file (in turn) within the array(s) is checked to see if the file exists and is not in use. Does this involve opening?

Moreover, it is unclear what FileInUse should return if some of the files matching the wildcard are in use and others aren't.

Sorry, i don't understand. The idea is that the process cannot continue unless all files exist and are free to use. If this is not the case, then a string gathers the data of all files that do not meet this criteria which is provided in a messagebox, before aborting the process.

It all appears to work as expected, except the one file that contains a wildcard in the string. If this file is in use, the checker does not catch it.

[HansV]

Don't you read your own code? The line

Open strFullName For Input Lock Read As #f

opens the file, as the keyword Open indicates. It doesn't open the file in Excel, but in VBA, and assigns it file number f. You cannot open multiple files at once and assign them all file number f. That would make no sense.

"Daily Walk *.xls" is not a single file - it stands for ALL files with extension .xls whose name starts with Daily Walk. It could be "Daily Walk Nathan.xls" and also "Daily Walk 37.xls" and also "Daily Walk through a boring neighbourhood.xls".

So you should either provide a specific name without wildcards, or loop through all files matching the wildcard. "Air code" for the latter goes something like this:

Code: Select all

Dim strFile As String
strFile = Dir(strPath & "5. Month " & strMnth & " Daily Walk *.xls")
Do While Not strFile = ""
  ' Do something with strFile here
  ...
  ' On to the next file
  strFile = Dir
Loop

[VegasNath]

Hans, thankyou for your response, as always, I do appreciate your help.

I think we may be at cross purposes here. The reason that i use the wildcard is that I use the same file (and code) in different locations from month to month. The exact filename changes each month, hence the wildcard. However, in all situations, there will only ever be 1 match for the wildcard, therefore I don't beleive looping will help here as there will only be one match.

The problem is that this one specific file (that I just so happen to use a wildcard to match it) is found, but does not return "busy" by the FileInUse function.

I apologise for any confusion.

I have just had a thought.... This specific file requires 2 passwords to gain entry, one to open it and a second to gain write access. Could this be the cause of the problem? The whole idea of the check is to ensure that write access is available, so the code would require the passwords in order to carry out the test?? If you agree that this is the problem, please advise how to modify the code to do so.

Thanks hans.

[HansV]

No, the password has nothing to do with it. The Open command in FileInUse does not operate at the Excel level, but at the operating system level - it just sees the file as a collection of bytes on disk.

If there will always be only one file matching the wildcard, try this:

Code: Select all

    ...

    arrFiles2 = Array( _
        "1. Month " & strMnth & " 5.8 Consolidated.xls", _
        "2. Month " & strMnth & " 5.17 Consolidated.xls", _
        "3. Month " & strMnth & " 5.18 Consolidated.xls", _
        "4. Month " & strMnth & " 5.19 Consolidated.xls", _
        Dir(strPath & "5. Month " & strMnth & " Daily Walk *.xls"), _
        "5a. Month " & strMnth & " Replacement Accounts.xls", _
        "6. Month " & strMnth & " Calendarisation Impact and Total Performance To Date.xls")

    ...

[VegasNath]

Thankyou, that does work as expected. So that I understand, can you elaborate on this for me. This is to do with the wildcard?

[HansV]

The Dir function with a wildcard path/filename as argument returns the name of the first file that matches the pattern. Since you mentioned that there will always be only one file at a time matching the pattern, the first file is also the only file, so that is sufficient.

(If there are more files matching the pattern, each subsequent Dir, this time without an argument, will return the next filename, until there are no more. Then Dir will return an empty string "".)

[VegasNath]

Great, Thankyou very much!