Option Explicit

'ファイルのクローズを待つ関数
Function WaitFileClose(ByVal sFileName As String, _
        ByVal iWaitSecond As Long, ByVal iTimeOut As Long) As Long
    Dim iCount As Long
    Dim iFileNo As Long
    Dim dfWait As Double

    On Error GoTo ErrorHandler

    dfWait = iWaitSecond / (24# * 60 * 60)
    iCount = 1
    Do While True
        iFileNo = FreeFile()
        On Error Resume Next
        Open sFileName For Binary Lock Read Write As #iFileNo
        If Err = 0 Then
            On Error GoTo ErrorHandler
            Close #iFileNo
            WaitFileClose = 0
            Exit Function
        Else
            On Error GoTo ErrorHandler
            If iCount >= iTimeOut Then Exit Do
            iCount = iCount + 1
            Application.Wait Now() + dfWait
        End If
    Loop
    WaitFileClose = -1
    Exit Function
ErrorHandler:
    WaitFileClose = Err
End Function

'テストマクロ
Sub Test_WaitFileClose()
    Dim sFileName As String
    Dim iRet As Long

    sFileName = "C:\My Documents\Book1.xls"

    If Dir$(sFileName) = "" Then
        MsgBox "ファイルがありません。"
        Exit Sub
    End If

    '1秒間隔で10回、ファイルのロックを試みます
    iRet = WaitFileClose(sFileName, 1, 10)

    Select Case iRet
        Case 0
            MsgBox "ファイルは閉じています。"
        Case -1
            MsgBox "タイムアウトです。ファイルは開いています。"
        Case Else
            MsgBox "予期せぬエラーです。"
    End Select
End Sub