How to Check if wb is open

[fluffyvampirekitten]

I want to check if the workbook has been open , run its code

if not open up the workbook , then activate wb1 and run its code

any suggestion ?
thanks in advance

Private Sub SimPat6_Merge()
'
'
' Step 35 - 39
'
Dim MaxRowNum As Long
Dim PatientMergeWB As Variant
Dim FileName As String
Dim SimPat As Workbook
    'Update Column S and T
    'S = Active Ext ID , T = Inactive Ext ID
    With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
    End With
    
    'Open Patient merge unit file to run the macro faster
                PatientMergeWB = Application.GetOpenFilename( _
    FileFilter:="PatientMergeMasterList, *.xls; *.xlsx", Title:="Open Patient Merge Master List")
    Workbooks.Open PatientMergeWB
    'Activate/Select the Simpat Workbook - Worksheet "SimPat"
    Workbooks("SimpatTest1-macro").Worksheets("SimPat").Activate
            
    'Set up an Error handler
    On Error GoTo errorFound
    Err.Clear
    On Error GoTo 0
    'Locate last filled row in column S (this instead of the loop)
    MaxRowNum = Range("C" & Rows.Count).End(xlUp).Row
    
    'IndexMatch Active Ext ID
    Range("S3:S" & MaxRowNum).Formula = "=IFERROR(INDEX('[PatientMergeMasterList.xls]2015'!$J:$J,MATCH(C3,'[PatientMerge.xls]2015'!$J:$J,0)),"""")"
 
 
    'IndexMatch Inactive Ext ID
    Range("T3:T" & MaxRowNum).Formula = "=IFERROR(INDEX('[PatientMergeMasterList.xls]2015'!$K:$K,MATCH(C3,'[PatientMerge.xls]2015'!$K:$K,0)),"""")"
    Columns("S:T").EntireColumn.AutoFit
            
    'Copy and Paste data as value
    Sheets("SimPat").Range("S3:T" & MaxRowNum).Copy
    Sheets("SimPat").Range("S3:T" & MaxRowNum).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    'Close the error Handler
    Exit Sub
errorFound:
    If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
    Err.Clear

End Sub

 

[JoeMo]

You can use this UDF in your code.

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Example:

Sub Test()
'code
If WorkBookOpen(wb.name) then
'do something
Else
'do something else like open a workbook
End If
'rest of code
End Sub

 

[fluffyvampirekitten]

You can use this UDF in your code.

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Example:

Sub Test()
'code
If WorkBookOpen(wb.name) then
'do something
Else
'do something else like open a workbook
End If
'rest of code
End Sub

Thank you so much ^^