Outlook VBA Check if File is Open/Not Open

[Felix_Dragonhammer]

So I have a bit of code to check whether or not a file is open or not and act accordingly:

Sub Check()
    
    Dim Ret
    Ret = IsWorkBookOpen("C:\Users\sberger\Desktop\GPAU\Target.xlsx")
    If Ret = False Then
    
        Dim xlApp As Object
        Dim sourceWB As Object
    
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .EnableEvents = False
        End With
    
           Set sourceWB = xlApp.Workbooks.Open("C:\Users\sberger\Desktop\GPAU\Target.xlsm", , False, , , , , , , True)
           sourceWB.Activate
           
    Else
    
        Exit Sub
    
    End If
     
    xlApp.Run ("Test")
    
End Sub

------------------------------------------------------------------------------------------------------------

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    
    Select Case ErrNo
    Case 70:   IsWorkBookOpen = True
    Case Else: IsWorkBookOpen = False
    
    End Select
End Function

Much to my chagrin, I have discovered it is not. The workbook I have it checking is opened, but the code I have insists on opening a new one each time I run it. I can only assume I goofed up the function somehow when I adapted it.

Any help resolving this would be sincerely appreciated! Also, if anyone could provide code to activate the "Target" workbook in the event it is already open, I would appreciate that as well.

 

[RickXL]

Did they both have the same name?

 

[Felix_Dragonhammer]

You mean the sub? Yeah, I thought that might have been the problem, so I checked that 4 or 5 times. I didn't detect any differences.