[XL-2007] controler en vba si fichiers sont ouverts et modifié ou non pour les sauvegarder ou non (sans message)

Salut, commence par Baliser ton code

voir Code de macro pour vérifier si un fichier est déjà ouvert ou allégé dans la FAQ

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Option Explicit
 
Sub TestFileOpened()
    ' Test to see if the file is open.
    If IsFileOpen("c:\Book2.xls") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
        ' Add code here to handle case where file is open by another
        ' user.
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
        ' Open the file in Microsoft Excel.
        Workbooks.Open "c:\Book2.xls"
        ' Add code here to handle case where file is NOT open by another
        ' user.
    End If
End Sub
 
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.
 
Private Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
 
    On Error Resume Next
    filenum = FreeFile()
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum
    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
        Case 0
            ' No error occurred.
            ' File is NOT already open by another user.
            IsFileOpen = False
        Case 70
            ' Error number for "Permission Denied."
            ' File is already opened by another user.
            IsFileOpen = True
        Case Else
            ' Another error occurred.
            Error errnum
    End Select
End Function