Determining if a File Is Open, and Handling Appropriately

[reberryjr]

I'm trying to write some code that will identify if a certain file is already open. If it is, then I want a msg box to appear. If it isn't, then I want some other code to run. I did some searching, and found some code that looks like it would work. I implemented that coding into my application, but it's not catching the open file. I'm not sure where I went wrong here.

Function IsWorkBookOpen(Name As String) As Boolean
    
    Dim xWB As Workbook
    
    On Error Resume Next
    
    Set xWB = Application.Workbooks.Item(Name)
    
    IsWorkBookOpen = (Not xWB Is Nothing)
End Function
Private Sub cmd_Export_6827_Click()
Dim xRet As Boolean
xRet = IsWorkBookOpen("6827_BKR371.xls*")
If xRet Then
    MsgBox ("Please ensure that all 6827_BKR371 files have been saved, and closed before proceeding.")
Else
    Call Export_6827_BKR371
    Unload Me
End If
End Sub

 

[Domenic]

Try...

Private Sub cmd_Export_6827_Click()
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If UCase(Left(wb.Name, 15)) = "6827_BKR371.XLS" Then
            Exit For
        End If
    Next wb
    If wb Is Nothing Then
        Call Export_6827_BKR371
        Unload Me
    Else
        MsgBox ("Please ensure that all 6827_BKR371 files have been saved, and closed before proceeding.")
    End If
End Sub

Hope this helps!

 

[reberryjr]

@Domenic for some reason, it's still not recognizing that there is an open file with the naming convention.

 

[Domenic]

Have you confirmed that you do in fact have an open workbook whose first 15 characters start with "6827_BKR371.XLS" ?

If so, is the workbook opened in a separate and new instance of Excel?

 

[reberryjr]

Yes, there is an open file with tat naming convention. It does appear that the open file is in a separate instance of Excel.

 

[Domenic]

In that case, I think you'll need to first loop through each instance of Excel, and then loop through each workbook within the Workbooks collection for the instance. Unfortunately, I don't have the code for doing such a task. Try searching Google for "how to loop through each instance of excel using vba". Actually, I did a quick search and found this link...

https://www.ozgrid.com/forum/forum/...-all-open-workbooks-in-all-instances-of-excel

I haven't tested it, but hopefully you'll be able to adapt it for your needs.