Stopping VBA if 3 scenarios are met

[ItalianPlatinum]

Hello - I have existing VBA that am looking to enhance currently I have 3 criteria, currently working for 1 & 3 looking to add #2:

I have #2 embedded into the VBA with another sub and currently causing issues as it doesn't stop the current VBA Test1.

1. Stopping VBA if file 1 is missing (working)
2. Stopping VBA if file 2 is missing (trying to add)
3. Stopping VBA is file 2 is empty (working)

Sub TEST1
    Dim UsdRws As Long
    Dim FilePath As String
    Dim TestStr As String
    Dim FoundFile As Boolean
    Dim rws As Long
    Dim bottomrow, lastblank As Long
    Dim lr As Long
    Dim vCols As Variant, vRows As Variant
    Dim i As Long, k As Long

FilePath = "MY Files\" & Format(Now(), "MM-DD-YY") & " " & "TEST" & ".csv"

    If FilePath <> "" Then
        TestStr = Dir$(FilePath)
        FoundFile = (TestStr <> "")
    Else
        FoundFile = False
    End If

    If FoundFile Then
    
    'OTHER CODE/Processes (i.e. copying, pasting, transfering, filtering data, inputting formulas, closing files/sheets)

Call openFile2  

        Else
    MsgBox "File is Empty"
  End If
      
    Else
        MsgBox "File 1 NOT FOUND"
    End If

Sub OpenFile2()
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
  
    sPath = "MY FILES\"      ' <<<<< change accordingly
  
    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
    Else
        MsgBox "File not found.", vbExclamation
    End If
End Sub

 

[6StringJazzer]

You can turn the Sub into a Function that tells you whether the file was opened successfully:

Sub TEST1
    Dim UsdRws As Long
    Dim FilePath As String
    Dim TestStr As String
    Dim FoundFile As Boolean
    Dim rws As Long
    Dim bottomrow, lastblank As Long
    Dim lr As Long
    Dim vCols As Variant, vRows As Variant
    Dim i As Long, k As Long

FilePath = "MY Files\" & Format(Now(), "MM-DD-YY") & " " & "TEST" & ".csv"

    If FilePath <> "" Then
        TestStr = Dir$(FilePath)
        FoundFile = (TestStr <> "")
    Else
        FoundFile = False
    End If

    If FoundFile Then
 
    'OTHER CODE/Processes (i.e. copying, pasting, transfering, filtering data, inputting formulas, closing files/sheets)

           If openFile2 Then
              ' Do all your processing here
           Else
                ' File2 was missing
           End If
        Else
    MsgBox "File is Empty"
  End If
   
    Else
        MsgBox "File 1 NOT FOUND"
    End If

Function OpenFile2() As Boolean

    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String

    sPath = "MY FILES\"      ' <<<<< change accordingly

    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
        OpenFile2 = True
    Else
        MsgBox "File not found.", vbExclamation
        OpenFile2 = False
    End If
End Function

 

[ItalianPlatinum]

so if i change it to a function and file 2 is missing will the VBA stop right there in its tracks or will it just move to the next task?

 

[6StringJazzer]

IF File1 is present AND File2 is present THEN
the code in the area labeled "do your processing here" will be executed. I missed your comment the first time, but addressed it here.

I would also change your logic flow just a bit for clarity. Here is a modification that is cleaner.

Note:
1. You did not show any code that determines whether File2 is empty.
2. You are checking for File1 name being null but it is not possible. See comment.

Sub TEST1()

    Dim UsdRws As Long
    Dim FilePath As String
    Dim TestStr As String
    Dim FoundFile As Boolean
    Dim rws As Long
    Dim bottomrow, lastblank As Long
    Dim lr As Long
    Dim vCols As Variant, vRows As Variant
    Dim i As Long, k As Long

    FilePath = "MY Files\" & Format(Now(), "MM-DD-YY") & " " & "TEST" & ".csv"

    If FilePath = "" Then ' This test will never be TRUE because you are setting it to a string above.
       MsgBox "File is Empty"
    Else
        TestStr = Dir$(FilePath)
        FoundFile = (TestStr <> "")

        If Not OpenFile2 Then
           MsgBox "File2 is missing"
        Else
           'OTHER CODE/Processes (i.e. copying, pasting, transfering, filtering data, inputting formulas, closing files/sheets)
        End If
      
    End If
  
End Sub

Function OpenFile2() As Boolean

    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String

    sPath = "MY FILES\"      ' <<<<< change accordingly

    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
        OpenFile2 = True
    Else
        MsgBox "File not found.", vbExclamation
        OpenFile2 = False
    End If
End Function

 

[ItalianPlatinum]

So this is File 1

FilePath = "MY Files\" & Format(Now(), "MM-DD-YY") & " " & "TEST" & ".csv"

This is File 2

OpenFile2()

And where I have file is emptying i see i left it out was right above my other processes

If Workbooks("Compare").Sheets("Periodic").Range("I" & rows.count).End(xlUp).row > 2 Then

Not sure if it changes anything

 

[ItalianPlatinum]

Ok tested it out but it is telling me twice my file 2 is missing. never telling me file 1 is missing.

 

[ItalianPlatinum]

ok got it to work had to maneuver things around. Only open question is if both files are missing it will just tell me first file is missing but not telling me 2nd file is missing as well. And not sure how to still do that and not have my process keep going. Because other than that its working

Option Explicit
Sub RUN_DIV()
    Dim UsdRws As Long
    Dim FilePath As String
    Dim TestStr As String
    Dim FoundFile As Boolean
    Dim rws As Long
    Dim bottomrow, lastblank As Long
    Dim lr As Long
    Dim vCols As Variant, vRows As Variant
    Dim i As Long, k As Long
    
Application.ScreenUpdating = False
Application.EnableEvents = False

'find path if not found give msg (end of vba)
    FilePath = "MYPATH" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
    
    If FilePath <> "" Then
        TestStr = Dir$(FilePath)
        FoundFile = (TestStr <> "")

    Else
        FoundFile = False
    End If
    
    If FoundFile Then
    
'If found check if Exists
    If Not OpenCopyFile2 Then
        MsgBox "File2 NOT FOUND"
    Else

'If found check if file2 is NOT empty
If Workbooks("Compare").Sheets("Periodic").Range("A" & rows.count).End(xlUp).row > 3 Then

Application.Calculation = xlCalculationManual

'OTHER PROCESSES

'Close File2
Call CLOSEFile2

Application.Calculation = xlCalculationAutomatic
    
Application.ScreenUpdating = True
Application.EnableEvents = True
      
         Else
         Call CLOSEFile2
    MsgBox "File2 is Empty"
  End If
      
  End If
    Else
    MsgBox "DIV File NOT FOUND"
    End If     
End Sub

Function OpenCopyFile2() As Boolean
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
  
    sPath = "MY PATH\"      ' <<<<< change accordingly
  
    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
        OpenCopyFile2 = True
    Else
        OpenCopyFile2 = False
    End If
End Function

Sub CLOSEFile2()
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
  
    sPath = "MY PATH\"      ' <<<<< change accordingly
  
    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks(sFName).Close SaveChanges:=False
    Else
    End If
End Sub

 

[6StringJazzer]

This will check all file conditions first, and if there are any errors give an error message describing all errors. Otherwise it will do the processing. I recommend keeping your indentation organized so it is easier to read the code.

Option Explicit

Sub RUN_DIV()

   Dim UsdRws As Long
   Dim FilePath As String
   Dim TestStr As String
   Dim FoundFile As Boolean
   Dim rws As Long
   Dim bottomrow, lastblank As Long
   Dim lr As Long
   Dim vCols As Variant, vRows As Variant
   Dim i As Long, k As Long
   Dim ErrMsg As String
 
   Application.ScreenUpdating = False
   Application.EnableEvents = False
 
   'find path if not found give msg (end of vba)
   FilePath = "MYPATH" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
 
   If FilePath <> "" Then
      TestStr = Dir$(FilePath)
   Else
      ErrMsg = "DIV File NOT FOUND"
   End If
 
   'If found check if Exists
   If Not OpenCopyFile2 Then
      ErrMsg = ErrMsg & vbCrLf & "File2 NOT FOUND"
   End If
    
   'If found check if file2 is NOT empty
   If Workbooks("Compare").Sheets("Periodic").Range("A" & Rows.Count).End(xlUp).Row <= 3 Then
      Call CLOSEFile2
      ErrMsg = ErrMsg & vbCrLf & "File2 is Empty"
   End If
 
   If ErrMsg <> "" Then
      MsgBox ErrMsg
   Else
       
      Application.Calculation = xlCalculationManual
    
      'OTHER PROCESSES
    
      'Close File2
      Call CLOSEFile2
    
      Application.Calculation = xlCalculationAutomatic
    
      Application.ScreenUpdating = True
      Application.EnableEvents = True
    
   End If
 
End Sub

Function OpenCopyFile2() As Boolean
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String

    sPath = "MY PATH\"      ' <<<<< change accordingly

    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
        OpenCopyFile2 = True
    Else
        OpenCopyFile2 = False
    End If
End Function

Sub CLOSEFile2()
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String

    sPath = "MY PATH\"      ' <<<<< change accordingly

    sPartial = "dist_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks(sFName).Close SaveChanges:=False
    Else
    End If
End Sub

 

[ItalianPlatinum]

I do agree on the indentation. when I copied over I think it messed up that is my fault.

I tested with file 1 missing and file 2 found but empty. So it is saying file 2 was empty but not saying file 1 was missing. I would expect to see File 1 missing then file 2 is empty. I even tried with File 1 present and still same message as prior which is what I would expect if file 1 is present in the latter test.

is it related to this?
If FilePath <> "" Then. if i flip the <> to = when the file is present it shows nicely just the inverse saying file wasnt found

 

[6StringJazzer]

You have a problem that I mentioned earlier but it's still there.

   'find path if not found give msg (end of vba)
   FilePath = "MYPATH" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
 
   If FilePath <> "" Then
      TestStr = Dir$(FilePath)
   Else
      ErrMsg = "DIV File NOT FOUND"
   End If

FilePath can never be "". I think maybe you want to do this:

   'find path if not found give msg (end of vba)
   FilePath = "MYPATH" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
 
   TestStr = Dir$(FilePath)
   If TestStr = "" Then
      ErrMsg = "DIV File NOT FOUND"
   End If