Optimisation du code - Gestion multi utililsateurs à distance

**Ennekaw** · 13/08/2018, 16h45

Bonjour,

Je cherche à développer une solution pour la gestion multi-utilisateur à distance dans le cadre du télétravail.
J'utilise pour cela un dossier sur un disque réseau dans lequel je stocke des mini fichiers texte avec pour titre la ligne en cours d'utilisation ainsi que le nom de l'utilisateur.
Cela fonctionne parfaitement et de façon instantanée en local. mais les temps de réponses à distance sont trop long (entre 5 et 10 secondes de temps mort entre chaque cellule).
J'espère être clair.

Je cherche donc à optimiser un maximum afin de réduire le temps de traitement.
Toutes les idées sont les bienvenues!!!!

Voici ma fonction qui est déclenchée par Workbook_SheetSelectionChange

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Function verrouillage(ByVal ligne As String) As Boolean
    Dim CheminVerrou As String
    Dim fs As Scripting.FileSystemObject
    Dim UtilisateurActuel As Boolean
 
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set dossiersource = fs.GetFolder("V:\SEB - Test Télétravail\verrou\")
 
    For Each fichier In dossiersource.Files
        If Left(fichier.Name, Len(ligne)) = ligne Then
            If InStr(1, fichier.Name, Application.UserName) = 0 Then
                UtilisateurActuel = False
                MsgBox "cette ligne est vérouillée par " & Mid(fichier.Name, Len(ligne) + 2, InStr(Len(ligne) + 2, fichier.Name, "-") - Len(ligne) - 2)
                verrouillage = True
                Exit For
            Else
                UtilisateurActuel = True
            End If
        Else
            verrouillage = False
        End If
    If InStr(1, fichier.Name, Application.UserName) Then
        On Error GoTo errorHandler
        Kill dossiersource & "\" & fichier.Name
        On Error GoTo 0
    End If
    Next fichier
 
    If verrouillage = False Then
        Set a = fs.CreateTextFile("V:\SEB - Test Télétravail\verrou\" & ActiveCell.Row & "-" & Application.UserName & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", "."), True)
        If UtilisateurActuel = False Then MsgBox "ligne libre!!"
    Else
        Application.EnableEvents = False
        ActiveCell.Offset(1, 0).Select
        Application.EnableEvents = True
        Set a = fs.CreateTextFile("V:\SEB - Test Télétravail\verrou\" & ActiveCell.Row & "-" & Application.UserName & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", "."), True)
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
Exit Function
errorHandler:
    a.Close
    Resume
End Function

Merci de m'avoir lu!

Invité · 13/08/2018, 17h05

Bonjour

Ce n'est peut-être pas ton code qui est en défaut, mais la connexion.

Philippe

**eriiic** · 13/08/2018, 17h17

Bonjour,

tu ne peux pas tout mettre dans un fichier unique ?
Ouvrir 20 fichiers sera toujours 20 fois plus lent qu'un seul. D'autant plus si l'utilisateur a un mauvais ping.
eric

**Ennekaw** · 13/08/2018, 17h21

Merci Philippe pour ta réponse.
Mon code fonctionne bien en local. Effectivement, c'est le temps de réponse du disque réseau qui pose problème. Mais je me dis qu'au plus mon code est léger et optimisé, au plus je me rapprocherais de la solution

**Ennekaw** · 13/08/2018, 17h24

Envoyé par eriiic

Bonjour,

tu ne peux pas tout mettre dans un fichier unique ?
Ouvrir 20 fichiers sera toujours 20 fois plus lent qu'un seul. D'autant plus si l'utilisateur a un mauvais ping.
eric

Merci de ta réponse!

Le choix de la création des fichiers texte vient justement du temps de réponse: aucun d'eux n'est ouverts, le code parcourt seulement leurs noms, ce qui fait un gain de temps considérable par rapport à ouvrir, modifier, puis enregistrer.
Mon premier test utilisait un fichier unique en csv qui pesait moins d'un kilo et les temps de réponses dépassaient les 20 secondes!

**eriiic** · 13/08/2018, 17h38

Je n'y connais rien en réseau mais au cas où, il n'existerait pas une notion de flux prioritaire ?
C'est plus important de faire passer les flux d'applications temps réel que le chargement du rapport de la dernière réunion.

Et partager le classeur est exclu ?

Edit : La qualité de service permet d’offrir aux utilisateurs des débits et des temps de réponse différenciés par applications (ou activités) suivant les protocoles mis en œuvre au niveau de la structure.
https://fr.wikipedia.org/wiki/Qualit%C3%A9_de_service

**Ennekaw** · 13/08/2018, 17h52

En fait, je n'ai absolument aucun accès aux paramètres réseau :/
Initialement, les classeurs dans lesquels je veux intégrer ces codes sont déjà partagés. L'idée est d'en ouvrir une copie locale car à chaque ouverture/enregistrement/fermeture, l'utilisateur distant verrouille le classeur pour des durées allant parfois jusqu'au quart d'heure!!!
C'est justement pour contourner ça que je m'arrache le peu de cheveux qui me restent sur ce code

Autre idée que j'ai eu mais qui, à priori, n'est pas réalisable, serait de stocker directement dans une variable tableau le contenu du dossier "verrou" mais je n'ai pas réussi sans faire une boucle qui parcourt les fichiers, donc inutile...

**eriiic** · 13/08/2018, 17h56

J'avais complété mon post.

Un fichier partagé n'est pas verrouillé, il est utilisable (plus ou moins bien certes) par plusieurs utilisateurs simultanément.
Il faut gérer les collisions mais en les sensibilisants aux bonnes pratiques ça les réduit.
Il me semble que les dernières versions d'excel sont mieux orientées coté travail collaboratif.
eric

**eriiic** · 13/08/2018, 18h44

Je viens de tester sur mon réseau (où je suis seul), balayer un répertoire est extrèmement lent (fait avec ta macro allégée).
En local : 528 fichiers / 0.17 s
Réseau : 193 fichiers / 30 s
Avec Dir() le temps est divisé par 2. Mieux mais ça ne sera pas suffisant.

Tu pourrais essayer avec un fichier unique (enfin un par appli) mais en accès aléatoire (Open xxx for random).
C'est un fichier texte très structuré ou chaque ligne à ses données dans champs de taille fixe.
Tu sais que tu as besoin de lire (ou écrire) la données ligne 1200, 5ème champ, tu iras lire directement cette valeur sans lire tout le fichier.
eric

**Ennekaw** · 14/08/2018, 09h35

Merci beaucoup Eriic

Concernant le nombres de fichiers à parcourir, il ne devrait pas excéder la demi-douzaine, il y a rarement plus d'utilisateurs simultanés.
Et côté configuration réseau, je suis obligé de me plier aux règles existantes, aucun moyen d'obtenir plus de paquet...

Je vais regarder du côté de open xxx random. Je ne connais pas du tout. Je vais prendre de le temps de me documenter un peu et je reviendrai faire un retour lorsque j'aurai fait un essai!

**Ennekaw** · 20/08/2018, 17h37

Salut !
Désolé pour le temps mort, je ne pratique vba que sur mon "temps libre" professionnel, soit pas souvent

Après pas mal d’essai, j'ai ce code qui tourne bien en local, très rapide. Je le teste en réseau demain.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Sub test(ByVal ligne As String)
Dim texte As String * 50
Dim UtilisateurActuel As Boolean, LigneOccupée As Boolean
Dim NumLigneUtilisateur As Integer, NumLigneOccupée As Integer, DerniereLigne As Integer
 
    If Dir("T:\PRESTAFORM TOUS\AFPR-POE\seb\verrou\verrou - " & Replace(Date, "/", ".")) = "" Then
        Open "T:\PRESTAFORM TOUS\AFPR-POE\seb\verrou\verrou - " & Replace(Date, "/", ".") For Random As #1
        texte = ActiveCell.Row & "-" & UCase(Application.UserName) & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", ".")
        Put #1, 1, texte
        MsgBox "ligne libre"
        test = True
        Close #1
        Exit Function
    End If
    On Error GoTo errorHandler
    Open "T:\PRESTAFORM TOUS\AFPR-POE\seb\verrou\verrou - " & Replace(Date, "/", ".") For Random As #1 Len = Len(texte)
    On Error GoTo 0
    LigneOccupée = False
    UtilisateurActuel = False
    For I = 1 To 20
        Get #1, I, texte
        If ligne = Left(texte, Len(ligne)) Then
            LigneOccupée = True
            NumLigneOccupée = I
        End If
        If InStr(1, UCase(texte), UCase(Application.UserName)) <> 0 Then '= 0 Then
            UtilisateurActuel = True
            NumLigneUtilisateur = I
        End If
        If I > 1 Then
            If EOF(1) Then
                DerniereLigne = I
                Exit For
            End If
        End If
    Next
    If LigneOccupée = True Then
        If UtilisateurActuel = True Then
            If NumLigneOccupée = NumLigneUtilisateur Then
                texte = ActiveCell.Row & "-" & UCase(Application.UserName) & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", ".")
                Put #1, NumLigneUtilisateur, texte
            Else
                Get #1, NumLigneOccupée, texte
                MsgBox "cette ligne est vérouillée par " & Mid(texte, Len(ligne) + 2, InStr(Len(ligne) + 2, texte, "-") - (Len(ligne) + 2))
                ActiveCell.Offset(1, 0).Select
            End If
        Else
            Get #1, NumLigneOccupée, texte
            MsgBox "cette ligne est vérouillée par " & Mid(texte, Len(ligne) + 2, InStr(Len(ligne) + 2, texte, "-") - (Len(ligne) + 2))
            ActiveCell.Offset(1, 0).Select
        End If
    Else
        If UtilisateurActuel = True Then
            texte = ActiveCell.Row & "-" & UCase(Application.UserName) & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", ".")
            Put #1, NumLigneUtilisateur, texte
        Else
            texte = ActiveCell.Row & "-" & UCase(Application.UserName) & "-" & Replace(Date, "/", ".") & "-" & Replace(TimeValue(Now), ":", ".")
            Put #1, DerniereLigne, texte
        End If
    End If
 
Close #1
Exit Function
errorHandler:
    Close #1
    Resume
End Sub

La suite au prochain numéro....

**eriiic** · 20/08/2018, 18h29

Bonjour,

Pas sûr que tu y gagnes là. Tu utilises As Random un peu de la même façon qu'avant, avec de multiples lectures. Et avec le réseau que tu as l'air d'avoir....

J'imaginais plus l'inverse : une table des lignes avec l'ident, et date-heure si tu en as besoin. Eventuellement autant de champ par ligne que de feuilles concernées dans le classeur, sinon un fichier verrou par feuille qui sera plus facile à gérer si leur nombre varie.
Tu as le fichier toto à partager, tu prévois un fichier verrous_toto.txt.
Un utilisateur se met en ligne 300, tu lis la ligne 300 de verrous_toto.txt pour voir si elle est dispo (erreur à gérer sans doute si > à la dernière ligne notée). Si oui et s'il n'y est pas déjà, tu inscris son occupation avec son ident (texte As String * 50 à baisser sans doute).
Et si tu connais la ligne maxi tu peux la créer tout de suite, plus d'erreur à gérer à ce niveau, elles seront toutes crées.

Tu n'as pas à balayer le directory vu que tu connais le nom du fichier, et tu lis le minimum nécessaire au lieu de boucler sur tous les utilisateurs.
Dans le cas le plus défavorable ça te fait 1 lecture + 1 écriture (de qq octets).
eric

Edit : je n'ai pas trouvé comment barrer un texte. En gris ce n'est plus d'actualité. Pas d'erreur si lecture au-delà de la dernière ligne, texte = 50 espaces

PS : à y réfléchir crée ta propre extension, .ver par exemple. Ca évitera qu'un curieux mette le bazar dans un fichier.

**Ennekaw** · 21/08/2018, 09h34

C'était ma première idée aussi. Mais cela m'oblige à stocker quelque part la ligne à déverrouiller et pouvoir effacer la ligne qui correspond. L'idéal aurait été une variable "old_target" dans worksheet_selectionchanges

Il n'y a qu'une seule feuille à gérer et un maximum de 6 utilisateurs simultanés. La lecture intégrale des entrées est donc rapide (en local... Test réseau à venir dans la journée) et me permet d'effectuer une maintenance des entrées qui ne seraient plus valides. Je ne balaie le contenu du fichier qu'une seule fois...
Je vais quand-même essayer d'écrire une version en suivant ton idée.
Encore merci

**eriiic** · 21/08/2018, 10h03

Bonjour,

cela m'oblige à stocker quelque part la ligne à déverrouiller

En quoi est-ce un problème ?
Le 'quelque part' se fait tout seul sans que tu n'aies à t'en soucier.
Et 6 lectures c'est 6 fois plus lent. Pour du temps réel ce n'est pas négligeable, la moindre latence devient vite exaspérante si tu dois faire une série de saisies.
eric

**Pierre Fauconnier** · 21/08/2018, 10h24

Salut.

Mon petit grain de sel...

Les classeurs partagés, c'est déjà bof bof, mais alors verrouiller des lignes d'un classeur distant via des fichiers txt, je n'avais encore jamais vu

Au delà de l'éventuelle prouesse technique mais de la très certaine fragilité d'un tel montage, je me dis qu'utiliser Excel dans ce genre de configuration est vraiment aberrant. Même si je n'aime pas les classeurs partagés, on pourrait envisager de les travailler en Office 365 (pour le prix de quelques licences).

L'usine à gaz qui est en train d'être construite ici est tout sauf professionnelle car non pérenne et non fiable (même si je sais qu'on fait avec ce qu'on a...

)

**eriiic** · 21/08/2018, 11h34

Bonjour Pierre,

Je lui avais signalé au post #8 qu'un classeur partagé (avec ses limitations) ne bloquait pas les autres utilisateurs contrairement à ce qu'il avait l'air de penser.
Mais il n'a pas rebondi dessus. Peut-être que ce sont ces limitations qui le gênent (?)

Fragilité, tu fais bien de soulever la question.
Quid d'un accès simultané ou 2 utilisateurs ont ok en réponse ? Vu les temps de réponse ça va arriver...
Il faut ouvrir le fichier en le verrouillant avec Lock Read Write.
eric

**Pierre Fauconnier** · 21/08/2018, 11h42

Salut Eric

Le classeur partagé ne bloque effectivement pas les autres utilisateurs, mais j'ai déjà du mal (comme on dit chez nous) à l'envisager comme solution sur un réseau local, alors distant, je ne te dis même pas les #@$*|&{#é#€ qui me viennent en tête

Les mécanismes de verrou sont parmi les plus fragiles sur les SGBD pro et des armées d'ingénieurs ont planché dessus, alors je doute qu'un fichier txt créé à distance amène sécurité et fiabilité.

Je verrais déjà moins mal une consolidation des classeurs locaux avec rejet des lignes modifiées par plusieurs utlisateurs, mais il est très clair qu'Excel n'est pas du tout fait pour ça. Même Access, de par l'accès distant, est irréaliste dans ce cas-ci.

Une solution relativement légère serait de s'orienter vers une base de données sur Azure, qui permet l'hébergement de bases Access, si je ne dis pas de conneries. Alors oui, ça a un coup, mais au moins c'est pro, fiable, fait pour,... (Et le coût de la perte de données ou de confiance sur celles-ci n'est pas à négliger dans la solution Excel, d'ailleurs).

Je ne sais pas ce que cette usine à gaz est censée gérer, mais je plains la personne qui va devoir rendre des comptes en cas de plantage

**Ennekaw** · 21/08/2018, 12h09

Merci Pierre.
Comme tu dis, on fait avec ce qu'on a... Comme tu t'en doutes, je n'ai absolument aucun moyen d'obtenir une licence pour quoi que ce soit!!!
Eriic, quand je parles de "blocage" des fichiers partagés, c'est vrai que le terme n'est pas le bon, mais lors des ouvertures et enregistrements des utilisateurs distants, le classeur était effectivement figé pendant une bonne dizaine de minutes sur le site local... Voilà le pourquoi de l'usine à gaz. Ce n'est effectivement pas des plus fiables, mais est tout du moins un garde fou....

**eriiic** · 21/08/2018, 12h23

C'est clair qu'un classeur partagé n'est pis-aller développé sans doute avec les contraintes de l'époque.

Le mettre sur onedrive avec chacun une copie synchronisée pourrait être une autre solution à explorer non ?
Si les fichiers ne sont pas énormes le compte gratuit sera suffisant.
eric

**Ennekaw** · 21/08/2018, 13h51

Comme dit plus haut, je ne pourrais avoir aucune autorisation. Je travaille dans une grande boite et le télétravail est développé avec des outils qui ne conviennent pas à notre activité (spécifique au sein de la boite). Je n'ai que deux solution, soit "sécuriser" l'accès exclusif aux dossiers via le code (si fragile que soit cette solution) soit la sécuriser en amont avec les dossiers physiques, ce qui risque de générer des conflits lors de la compilation des données...
Bref, je dois tester dans l'après-midi le temps d'accès au fichier texte (le plus léger possible) afin de voir si je continue à me casser la tête ou pas

Dans tous les cas, ce genre réflexions tordues me permettent de progresser à chaque .

Optimisation du code - Gestion multi utililsateurs à distance [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager