[corbis]

Не совсем то, что требуется. Вроде обманул, только не знаю, насколько грамотно такое решение:

Код:

Public wBook As Workbook

Function IsBookOpen(wbFullName As String) As Boolean
    Dim iFF As Integer
    iFF = FreeFile
    On Error Resume Next
    Open wbFullName For Random Access Read Write Lock Read Write As #iFF
    Close #iFF
    IsBookOpen = Err
End Function

Private Sub Button_Click()
flag = 0
        
   If (IsBookOpen("C:\test.xlsx")) Then

           If VarType(wBook) <> 9 Then
               MsgBox ("Закрой test'")
                           Else
               MsgBox ("Можно копировать")
           End If
           
    Else
       Workbooks.Open Filename:="C:\test.xlsx"
       Set wBook = ActiveWorkbook
   End If
End Sub