Check various files exist

[VegasNath]

I have some code that checks if various files exist. The list of files that need to be checked is growing over time so I am wondering if I can create an array of workbooks and loop through the array. If so, how? I have 2 different file path's so I am guessing that I would need to create 2 separate array's?

Code: Select all

Option Explicit

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

Sub A01_FileChecker()

    Dim Msg As String, strPath1 As String, strPath2 As String, strFile As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This is the File Checker which ensures that all the required files are in the current folder (ThisWorkbook.Path)
    strPath1 = ThisWorkbook.Path & "\"
    strPath2 = strPath1 & "Reports" & "\"
    
    Msg = vbCrLf & "ERROR"
    Msg = Msg & vbCrLf & vbCrLf & "Ensure that the Bat File has run successfully, and that all source files are available."
    Msg = Msg & vbCrLf & vbCrLf & "The following file is unavailable, and should be created before continuing."
    Msg = Msg & vbCrLf & vbCrLf
    
    strFile = "File1.xls"
    If FileExists(strPath1 & strFile) = False Then
        MsgBox Msg & strPath1 & strFile & vbCrLf & vbCrLf, vbExclamation
        Exit Sub
    End If
    
    'more of the same
    
    strFile = "File36.xls"
    If FileExists(strPath2 & strFile) = False Then
        MsgBox Msg & strPath2 & strFile & vbCrLf & vbCrLf, vbExclamation
        Exit Sub
    End If
    
    'more of the same
        
End Sub

I rarely use array's in vba so I would appreciate any help. Thanks

[HansV]

You could use something like this:

Code: Select all

Sub A01_FileChecker()
  ' This is the File Checker which ensures that all the required files
  ' are in the current folder (ThisWorkbook.Path)
  Dim Msg As String, varFile As Variant

  Msg = vbCrLf & "ERROR"
  Msg = Msg & vbCrLf & vbCrLf & "Ensure that the Bat File has run successfully, and that all source files are available."
  Msg = Msg & vbCrLf & vbCrLf & "The following file is unavailable, and should be created before continuing."
  Msg = Msg & vbCrLf & vbCrLf

  For Each varFile In Array(ThisWorkbook.Path & "\File1.xls", _
      ThisWorkbook.Path & "\Reports\File36.xls", ...)
    If FileExists(varFile) = False Then
      MsgBox Msg & varFile, vbExclamation
      Exit Sub
    End If
  Next varFile
End Sub

[VegasNath]

Hi Hans, Thankyou for your speedy response.

I am getting a compile error at line: If FileExists(varFile) = False Then

Any ideas?

Also, would I need to stipulate the filepath prior to every file? Only, the array will be very long, so I am trying to ensure that it remains readable and easily adaptable.

[HansV]

Oops, change the header of FileExists to

Function FileExists(strFullName As Variant) As Boolean

Well, you said that the files were in different folders, just two now, but next time you'll probably want 37 folders. So it's better to specify the full path. You can always format it in an easily to read form:

Code: Select all

  Dim arrFiles As Variant
  arrFiles = Array( _
    ThisWorkbook.Path & "\File1.xls", _
    ThisWorkbook.Path & "\Reports\File36.xls", _
    "C:\Test\File3.xls", _
    "C:\Docs\File4.xls")
  For Each varFile In arrFiles
    ...

[VegasNath]

Hans, Thanks again.

Your suggestion makes perfect sense and I will adopt that method which will be much easier for future ammendments.

PS: For any future visitors to this thread, the above code is missing a 'Next' after the 'For'.

Thanks again.

[HansV]

Thanks, I edited my reply to add the missing Next.