VBA For workbooks.open, if file doesnt exist goto on error

[willow1985]

To anyone who can help.

I am still learning VBA and wrote a code to open a file but want to add some sort of error handler that if that file name/location does not exist, try another location and if that does not exist do a msg box that says "document not found" clear a certain cell and end sub.

Note/Context: The purpose of my whole code is to open a document, make sure it is filled out completely, and if so allow the user to close the log and if not, prompt the user that the form is incomplete.
The problem is there could be 2 possible names for the document which are referenced in 2 separate columns (-24 or 10) and locations (9 or 12).

Here is my code and how I would like to modify it done in comments:

Application.ScreenUpdating = False
Dim FORM As String
Dim FILE As String
FORM = ActiveCell.Offset(0, 9).Range("A1").Value
FILE = ActiveCell.Offset(0, -24).Range("A1").Value & ".xlsx"
FORM2 = ActiveCell.Offset(0, 12).Range("A1").Value
FILE2 = ActiveCell.Offset(0, 10).Range("A1").Value & ".xlsx"
Workbooks.Open FORM

'if error GoTo TryFORM2

If Worksheets("CAPA Form").Range("L12").Value = False Then
     Workbooks("Quality Log.xlsm").Activate
     ActiveCell.ClearContents
     Workbooks(FILE).Activate
     MsgBox "CAPA Report is Incomplete. Please Complete the Report Before Closing Out"
Else

        
  Workbooks("Quality Log.xlsm").Activate
Workbooks(FILE).Close Savechanges:=False

ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
End If

'TryFORM2:

'Workbooks.Open FORM2

'if error Workbooks("Quality Log.xlsm").Activate  ActiveCell.ClearContents MsgBox "Document not found" End Sub

'If Worksheets("CAPA Form").Range("L12").Value = False Then
    ' Workbooks("Quality Log.xlsm").Activate
     'ActiveCell.ClearContents
    ' Workbooks(FILE2).Activate
    ' MsgBox "CAPA Report is Incomplete. Please Complete the Report Before Closing Out"
'Else

        
  'Workbooks("Quality Log.xlsm").Activate
'Workbooks(FILE2).Close Savechanges:=False

'ActiveCell.Offset(0, 1).Range("A1").Select
  '  ActiveCell.FormulaR1C1 = "=TODAY()"
 '   ActiveCell.Select
 '   Selection.Copy
   ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   '     :=False, Transpose:=False
'Application.CutCopyMode = False
'ActiveWorkbook.Save
'End If

Application.ScreenUpdating = True

End Sub

Thank you to anyone who can help with the comments sections of this code

 

[Logit]

Untested here :

Sub OpenAndCheckForm()
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False

    Dim FORM As String, FORM2 As String
    Dim FILE As String, FILE2 As String
    Dim wb As Workbook

    ' Define file paths and names
    FORM = ActiveCell.Offset(0, 9).Range("A1").Value
    FILE = ActiveCell.Offset(0, -24).Range("A1").Value & ".xlsx"
    FORM2 = ActiveCell.Offset(0, 12).Range("A1").Value
    FILE2 = ActiveCell.Offset(0, 10).Range("A1").Value & ".xlsx"

    ' Attempt to open the first form
    On Error Resume Next
    Set wb = Workbooks.Open(FORM)
    On Error GoTo ErrorHandler

    ' If the first form doesn't exist, try the second form
    If wb Is Nothing Then
        On Error Resume Next
        Set wb = Workbooks.Open(FORM2)
        On Error GoTo ErrorHandler
    End If

    ' If neither form is found, handle the error
    If wb Is Nothing Then
        MsgBox "Document not found.", vbExclamation
        ActiveCell.ClearContents
        Application.ScreenUpdating = True
        Exit Sub
    End If

    ' Check if the form is complete
    If wb.Worksheets("CAPA Form").Range("L12").Value = False Then
        ' If incomplete, show a message and clear the cell
        Workbooks("Quality Log.xlsm").Activate
        ActiveCell.ClearContents
        MsgBox "CAPA Report is Incomplete. Please Complete the Report Before Closing Out"
    Else
        ' If complete, close the form and update the log
        Workbooks("Quality Log.xlsm").Activate
        wb.Close SaveChanges:=False

        ' Update the log with today's date
        ActiveCell.Offset(0, 1).Range("A1").Value = Date
        ActiveWorkbook.Save
    End If

    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    ' Handle any unexpected errors
    MsgBox "An unexpected error occurred.", vbCritical
    Application.ScreenUpdating = True
End Sub

 

[willow1985]

Untested here :

Sub OpenAndCheckForm()
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False

    Dim FORM As String, FORM2 As String
    Dim FILE As String, FILE2 As String
    Dim wb As Workbook

    ' Define file paths and names
    FORM = ActiveCell.Offset(0, 9).Range("A1").Value
    FILE = ActiveCell.Offset(0, -24).Range("A1").Value & ".xlsx"
    FORM2 = ActiveCell.Offset(0, 12).Range("A1").Value
    FILE2 = ActiveCell.Offset(0, 10).Range("A1").Value & ".xlsx"

    ' Attempt to open the first form
    On Error Resume Next
    Set wb = Workbooks.Open(FORM)
    On Error GoTo ErrorHandler

    ' If the first form doesn't exist, try the second form
    If wb Is Nothing Then
        On Error Resume Next
        Set wb = Workbooks.Open(FORM2)
        On Error GoTo ErrorHandler
    End If

    ' If neither form is found, handle the error
    If wb Is Nothing Then
        MsgBox "Document not found.", vbExclamation
        ActiveCell.ClearContents
        Application.ScreenUpdating = True
        Exit Sub
    End If

    ' Check if the form is complete
    If wb.Worksheets("CAPA Form").Range("L12").Value = False Then
        ' If incomplete, show a message and clear the cell
        Workbooks("Quality Log.xlsm").Activate
        ActiveCell.ClearContents
        MsgBox "CAPA Report is Incomplete. Please Complete the Report Before Closing Out"
    Else
        ' If complete, close the form and update the log
        Workbooks("Quality Log.xlsm").Activate
        wb.Close SaveChanges:=False

        ' Update the log with today's date
        ActiveCell.Offset(0, 1).Range("A1").Value = Date
        ActiveWorkbook.Save
    End If

    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    ' Handle any unexpected errors
    MsgBox "An unexpected error occurred.", vbCritical
    Application.ScreenUpdating = True
End Sub

This is perfect, Thank you!

 

[Logit]

Great !