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]

The above code shows a reference to Workbooks("Nas Compare") but does show where it is opened. The Workbooks.Open call above that is opening something else.

 

[ItalianPlatinum]

NAS Compare is the workbook where the VBA is stored. it just closes the workbook when everything is finished. the workbook open is opening that file that starts the vba

    Const BasePath = "MY PATH\"
    
    FilePath = BasePath & "Div" & ".csv"

 

[6StringJazzer]

Under the comment 'Close NAS File, why are you opening a file that's already open? And then immediately closing it?

 

[ItalianPlatinum]

When file exists I only need it to open, so I can copy then paste data into file where the vba is. I'm not following already open, previous code wasnt opening it until now?

As you can see it is copying then pasting so it isnt just being opened then right after closed their are steps in between that

 

[6StringJazzer]

'copy and paste NAS file into NAS DIV sheet in NAS Compare
Workbooks.Open Filename:="MY PATH\" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv" <-----File is opened here
Cells.Copy

With Workbooks("Nas Compare").Sheets("NAS DIV")
    .Range("A1").PasteSpecial
    .Range("5:5").AutoFilter
    .Protect AllowFormattingColumns:=True, DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

'Close NAS File
Workbooks.Open Filename:="MY PATH\" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"  <-----Same file is opened again here
ActiveWorkbook.Close <-----Then immediately closed

 

[ItalianPlatinum]

I didnt know how to bring that file to the front to close it. so the way i do it is use open again and the activeworkbook close. i am sure there is a better way to

 

[ItalianPlatinum]

tried your VBA it is telling me when file 2 doesn't exist a error box pops up that file wasnt found as if its trying to open it but fails not warns the user it doesnt exist. I want to send my full VBA. seeing that my mock up listed File 1 and File 2 in the same path and they arent. so i maneuvered some things around to adjust. for that see below may give you more clarify this time leaving for you all tasks opening/closing files. i took what you said on the above and modified, i had to change some things for confidentiality:

Option Explicit
Sub RUN_DIV() '''VBA is on NAS COMPARE''''
    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
    Dim File2 As Workbook
  
'Set file paths
    Const BasePath = "MY PATH 1\"
    Const sPath = "MY PATH 2\"
  
'find path if not found give msg (File1)
    FilePath = BasePath & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
    TestStr = Dir$(FilePath)
  
    If TestStr = "" Then
        ErrMsg = "File1 NOT FOUND"
    End If
  
'check if File2 Exists
    Set File2 = OpenCopyFile2(sPath)
    If File2 Is Nothing Then
        ErrMsg = ErrMsg & vbCrLf & "File2 File NOT FOUND"
    Else

'check if File2 file is NOT empty give msg end of VBA if Empty
      If File2.Worksheets(1).Range("A" & rows.count).End(xlUp).row <= 3 Then
         File2.Close SaveChanges:=False
         ErrMsg = ErrMsg & vbCrLf & "File2  File is Empty"
      End If
   End If

   If ErrMsg <> "" Then
      MsgBox ErrMsg
   Else

'copy paste File2 file and copy into NAS Compare
Range("A:Z").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("Nas Compare").Sheets("Distr").Range("A1").PasteSpecial
Application.CutCopyMode = False

'Close File2 file
File2.Close SaveChanges:=False

'copy and paste File1 into NAS DIV sheet in NAS Compare
Workbooks.Open Filename:="MY PATH 1\" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv"
Cells.Copy

With Workbooks("Nas Compare").Sheets("DIV")
    .Range("A1").PasteSpecial
    .Range("5:5").AutoFilter
    .Protect AllowFormattingColumns:=True, DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

'Close File1
Workbooks("MY PATH 1\" & Format(Now(), "MM-DD-YY") & " " & "Div" & ".csv").Close

''''''''''''''''other activty'''''''''''''''''''''''''''''''''''''

'save and close (Nas Compare)
Workbooks("Nas Compare").Close SaveChanges:=True
  
    End If
End Sub

Function OpenCopyFile2(sPath As String) As Workbook
    Dim sPartial    As String
    Dim sFName      As String
  
    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)
    On Error Resume Next
    Workbooks.OpenText Filename:=sPath & sPartial, _
                       DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, _
                       ConsecutiveDelimiter:=False, _
                       Tab:=False, _
                       Semicolon:=False, _
                       Comma:=True, _
                       Space:=False, _
                       Other:=False, _
                       FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2)), _
                       TrailingMinusNumbers:=True
    Set OpenCopySPCT = ActiveWorkbook
End Function

 

[ItalianPlatinum]

any suggestions?