Macro Excel VBA : Vérifier si un fichier est déjà ouvert avant de l'ouvrir

05 Sep

Macro Excel VBA : Vérifier si un fichier est déjà ouvert avant de l’ouvrir

Cette semaine pour finaliser un script , je cherchais une solution pour vérifier qu’un Fichier Excel était libre avant de l’ouvrir. Par chance en parcourant le net, je suis tombé sur une publication du site « support.microsoft.com ».

Peut être que cette fonction répondra également à vos attentes….

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Function IsFileOpen(filename As String)

Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.

filenum = FreeFile() ' Get a free file number.

' Attempt to open the file and lock it.

Open filename For Input Lock Read As #filenum

Close filenum ' Close the file.

errnum = Err ' Save the error number that occurred.

On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.

Select Case errnum

' No error occurred.

' File is NOT already open by another user.

Case 0

IsFileOpen = False

' Error number for "Permission Denied."

' File is already opened by another user.

Case 70

IsFileOpen = True

' Another error occurred.

Case Else

Error errnum

End Select

End Function

Source : https://support.microsoft.com/en-gb/help/291295/macro-code-to-check-whether-a-file-is-already-open

Macro Excel VBA : Vérifier si un fichier est déjà ouvert avant de l’ouvrir