0

I've got the following module checks for the number of files and writes to a .txt file when files are missing:

Sub CheckFiles(strDir As String, strType As String, chknum As Integer)

    Dim file As Variant, i As Integer
    Dim sFilename As String
    strDir = ThisWorkbook.Path & "\Source\"
    sFilename = ThisWorkbook.Path & "\Logs.txt"


    If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
    file = Dir(strDir & strType)
    While (file <> "")
        i = i + 1
        file = Dir
    Wend

    If i <> chknum Then

    ' Archive file at certain size
    If FileLen(sFilename) > 20000 Then
        FileCopy sFilename _
            , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt"))
        Kill sFilename
    End If

    ' Open the file to write
    Dim filenumber As Variant
    filenumber = FreeFile
    Open sFilename For Append As #filenumber

    Print #filenumber, CStr(Now) & ", " & "Source file: " & strType & " is missing " & chknm - i & " file(s)"

    Close #filenumber

End If


End Sub

The CheckFiles sub is called from this module:

Sub Report()

'other code
Call CheckFiles("", "File1.xlsx", 1)
Call CheckFiles("", "File2.xlsx", 1)
Call CheckFiles("", "File3.xlsx", 1)
Call CheckFiles("", "File4*.xlsx", 24)
'other code


End Sub

What I'm trying to do is to exit the Report sub if files are missing.

I've tried adding On Error Goto EH at the beginning of CheckFiles and adding the following at the end of the Report sub when error is detected but it doesn't work.

Done:
    Exit Sub
EH:
    Call EmailError
End Sub

How can I achieve this?

aab
  • 1,643
  • 2
  • 13
  • 22
  • 1
    How could you have >1 file with the same name though? – Tim Williams Aug 20 '19 at 16:52
  • 3
    Why don't you change your `Report` procedure to a `Boolean Function` and have it return true/ false if there is a missing file. You can then use that in your `CheckFiles` procedure. – Dean Aug 20 '19 at 16:55
  • @TimWilliams the file names are different. – aab Aug 20 '19 at 17:05
  • @Dean I have other code in the `Report`, it is the main module. I don't want it to affect the rest of the code on the module. Will it? – aab Aug 20 '19 at 17:06
  • 1
    But doesn't `CheckFiles("", "File4.xlsx", 24)` check for 24 copies of File4.xlsx ? Not a big deal - just pointing that out. – Tim Williams Aug 20 '19 at 17:09
  • @TimWilliams My mistake it's supposed to be `CheckFiles("", "File4*.xlsx", 24)`. – aab Aug 20 '19 at 17:10
  • first, you have to decide where you want to exit if only one of the files is missing. do you want to skip all the calls to check? or only the one regarding the missing file? is it relevant to check file 1 if file 2 is missing? do you want to check file 3 if file 2 is missing? – Tuckbros Aug 20 '19 at 17:13
  • @Tuckbros l don't want to skip any call to check. What if want if to check if file 1 is missing and if it is write to txt file, then check if file 2 is missing and if it is write to txt file, etc. then exit sub if any of the files is missing. – aab Aug 20 '19 at 17:23
  • If that is "only" this, the answer is there : https://stackoverflow.com/questions/16351249/vba-check-if-file-exists – Tuckbros Aug 20 '19 at 17:59
  • @Tuckbros I've tried that but it isn't working for me – aab Aug 20 '19 at 20:11
  • I don't understand the issue. does the checkFiles do what you want ? if you want to return a result from `CheckFiles` to the `Report` you can turn your `Sub CheckFiles`to a `function` and return whatever you want, true or false or the number of missing files or pass to `Sub CheckFiles` a 4th argument by reference, to value it as for the function with true, false or the number of missing files. Based on the result of the function or on the value of the retrieved argument you will be able to set a `if` condtion as you want in the `Report` – Tuckbros Aug 21 '19 at 05:36
  • @Tuckbros the `CheckFiles` does what I want it to (check files and write number of missing files to the `txt` file if any are missing). My main issue is I'm trying to exit to the `report` sub if `CheckFiles` writes to the `txt` file (i.e. stop the running the script is files are missing). `CheckFiles` works on its own but when i try to run it with the `Report` module it does not write to the `txt` file, I've also tried it with the excamples in the link. – aab Aug 21 '19 at 06:03
  • make sure the path are rigth. called from Report you can give a value to the first argument. – Tuckbros Aug 21 '19 at 07:42

0 Answers0