Check various files exist

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Check various files exist

Post by 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
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78962
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check various files exist

Post by 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
Last edited by HansV on 31 Jan 2010, 21:14, edited 1 time in total.
Reason: to add missing Next (thanks, Nathan)
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Check various files exist

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78962
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check various files exist

Post by 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
    ...
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Check various files exist

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78962
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check various files exist

Post by HansV »

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