Verify that multiple files exists.

[ffanias]

Hello everyone.

I have learned VBA thru experience and I am not familiar with resolving issues when it comes into theory.
I have created a file that sends approx 200 emails. Before clicking the send button I want to verify that the user has entered the correct name of the file and the correct path..
The below macro is running OK ( with some modifications ) for one single file but I want it to work for all 200 files.
If I use DO UNTIL loop the loop becomes infinite, with the DO WHILE loop I am not getting the results I want as it shows that all files exists even I have a mistake.


Thanks a lot.

Sub check_files_exist()
'
' check_files_exist Macro
'
'DECLARATIONS
Dim myfile As String
Dim mypath As String
Dim filename As String
Dim x As Integer
'WHERE TO START FROM SINCE MY FIRST LINE WITH DATA IS NUMBER 2
x = 2
'START THE LOOP
Do While Sheet1.Cells(x, 1) <> ""

path = Sheet1.Cells(x, 4)
filename = Sheet1.Cells(x, 3)
myfile = path + filename
If Dir(myfile) <> "" Then
MsgBox "ALL FILES EXIST"
Else
MsgBox "CHECK THE FILES"

End If

Loop

End Sub

 

[NdNoviceHlp]

It's kind of difficult to figure exactly what U want and where the file names (e-mail names?) are. In newer XL versions the DIR function no longer works. Here's some code using the file system object. Patch it in to yours. HTH. Dave

Dim ofsobj As Object
Set ofsobj = CreateObject("Scripting.FilesystemObject")
x = 2
'START THE LOOP
For cnt = 2 To 202
 Path = Sheet1.Cells(cnt, 4)
 Filename = Sheet1.Cells(cnt, 3)
 myfile = Path & Filename
If ofsobj.fileexists(myfile) = False Then
MsgBox myfile & " doesn't exist!"
End If
Next cnt
Set ofsobj = Nothing

ps. please use code tags

 

[pbornemeier]

I modified it a but:

Option Explicit

Sub check_files_exist()
    '
    ' check_files_exist Macro
    '
    'DECLARATIONS
    Dim myfile As String
    Dim sFilePath As String
    Dim sFileNameExt As String
    Dim sFilePathNameExt As String
    Dim lFirstRow As Long
    Dim lLastRow As Long
    Dim lRowIndex As Long
    Dim lErrorCount As Long
    Dim lSkipped As Long
    Dim sOutput As String
    Dim lFilesFound As Long
    
    'WHERE TO START FROM SINCE MY FIRST LINE WITH DATA IS NUMBER 2
    lFirstRow = 2
    lLastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    
    'Remove any color from cells in Sheet1
    Sheet1.Cells.Interior.Color = xlNone
    
    'START THE LOOP
    For lRowIndex = lFirstRow To lLastRow
        If Sheet1.Cells(lRowIndex, 1) <> vbNullString Then
        
            'Make sure FilePath ends in a \
            If Right(Sheet1.Cells(lRowIndex, 4).Value, 1) <> "\" Then
                Sheet1.Cells(lRowIndex, 4).Value = Sheet1.Cells(lRowIndex, 4).Value & "\"
            End If
            
            'Import values from worksheet
            sFilePath = Sheet1.Cells(lRowIndex, 4)
            sFileNameExt = Sheet1.Cells(lRowIndex, 3)
            sFilePathNameExt = sFilePath + sFileNameExt
            
            'Does FilePathNameExt exist
            If Dir(sFilePathNameExt) = vbNullString Then
                'sFilePathNameExt does not exist, color column C:D yellow
                Sheet1.Range(Sheet1.Cells(lRowIndex, 3), Sheet1.Cells(lRowIndex, 4)).Interior.Color = rgbYellow
                lErrorCount = lErrorCount + 1
            Else
                'File exists, count it
                lFilesFound = lFilesFound + 1
            End If
        Else
            'This row was skipped, color column A orange
            Sheet1.Cells(lRowIndex, 1).Interior.Color = rgbOrange
            lSkipped = lSkipped + 1
        End If
    Next
    
    If lErrorCount > 0 Then
        sOutput = lErrorCount & " file(s) do not exist.  They have been tinted yellow in columns C:D" & vbLf & vbLf
    End If
    If lSkipped > 0 Then
        sOutput = "There are " & lSkipped & " files that had nothing in column A and were skipped.  They have been tinted orange in column A" & vbLf & vbLf
    End If
    
    If sOutput <> vbNullString Then
        MsgBox sOutput & lFilesFound & " files were found.", , "Error Report"
    Else
        MsgBox "All " & lFilesFound & " files were found.  None were missing or skipped.", , "All Found Report"
    End If
    
End Sub

 

[Fluff]

In newer XL versions the DIR function no longer works.

DIR works well for me in xl2013 and I believe it still works with 2016

 

[NdNoviceHlp]

Whoops my bad re. the DIR function. I believe I got the FileSearch function mixed up with DIR. Anyways, thanks Fluff for correcting my erroneous input. Dave

 

[ffanias]

Thank you all for your quick reply.
The code of Phil is exaclty what I was looking for as it highlights the incorrect files, that would be my next project to improve the code but Phil got me first.

Thanks guys

 

[maheshvba]

'Make sure FilePath ends in a \
Not understood from here on the script.
I'm copying all the directories with the filename one by one eg : C:\Users\xxxxx\Desktop\Test\ccp\test_15 190729.xls
C:\Users\xxxxx\Desktop\Test\ccp\test_15 190729.xls

which is showing zero files found. Please help me how should I put the files on the excel sheet to find all the available paths with file availability and I need to send an email as well. Is it possible here, please?