Code VBA qui fonctionne de façon alternative ou aléatoire
Bonjour,
Je rencontre actuellement une problématique majeure sur excel avec mon fichier programmé en VBA. Je ne peux malheureusement pas rendre le fichier disponible puisqu’il contient des milliers de données nominatives confidentielles. Ceci dit, malgré cela, je prends tout de même la chance de poser la question au cas où quelqu’un aurait une piste solution possible à me proposer sur laquelle je pourrais travailler / explorer / expérimenter certaines choses. Voici la problématique :
Le fichier contient plusieurs formulaires et plusieurs feuilles. Les données de la feuille 1 sont importées dans un formulaire par un clique sur un bouton. L’utilisateur complète dans ce formulaire les champs manquants qui complètent les infos qui se trouvaient sur la feuille 1. Une fois les champs manquants complétés, il ‘’finalise’’ l’entrée en cliquant sur un autre bouton. Le code VBA de ce bouton copie alors les valeurs de ce formulaire vers la feuille 2, et supprime les données initiales de la feuille 1. Là où les problèmes commencent, c’est que le code fonctionne de façon aléatoire, en fait, il fonctionne toujours, mais le résultat est parfois incorrect.
Je m’explique, dans environ 90% des cas, la transaction s’effectue correctement. Dans l’autre 10%, les données sont effectivement supprimées de la feuille 1, mais également ne se copies pas sur la feuille 2, ou encore, écrase une entrée qui était déjà existante sur la feuille 2. J’ai du mal à croire que la problématique puisse être dans le code puisque ça fonctionne la majorité du temps sans problème. Comme la problématique est occasionnelle, toujours avec le même code dans rien varier, je trouve ça très louche (et en plus ça me fait perdre un temps précieux à tenter de récupérer dans les backups les données perdues). J’ai tenté de cibler des éléments du contexte qui pourrait expliquer (ex : même utilisateur, même poste de travail, même moment, etc..) et il ne semble pas y avoir de redondance dans la disparition des entrées, c’est à dire, ça arrive aléatoirement à différents utilisateurs, à différents endroits et à différents moments.
À noter que le fichier se trouve dans un emplacement réseau et est utilisé par une 20 aines d’utilisateurs différents dans une 10aine de sites géographiques différents. À l’ouverture du fichier, l’utilisateur peut choisir d’y accéder en lecture seule ou non en fonction de si des données doivent être saisies ou non, et ce, afin de laisser l’accès écriture au fichier aux autres utilisateurs. La version utilisée est Excel 2010 sur windows 7.
Je suis tout à fait conscient qu’il peut être difficile de bien saisir la problématique avec ces quelques lignes et sans fichier…. Mais si jamais vous avez une piste exploratoire à me proposer pour solutionner le tout, je suis preneur!
Un immense merci à toi, qui a pris le temps de lire et peut-être de me proposer quelque chose ?
- Messages
- 4'064
- Excel
- 2021 FR 64 bits
- Inscrit
- 13/06/2016
- Emploi
- bénévole associations Goutte d'Or
Bonjour,
Je pense que le problème vient d'une mauvaise gestion de l'accès simultané en écriture d'une même plage de données sur ton fichier situé sur le réseau.
C'est une piste intéressante...
As-tu quelque chose en tête quant à une façon de faire pour faciliter ou améliorer cette gestion ? Idéalement, je dois pouvoir laisser l'accès à chaque utilisateur en lecture en tout temps. Ils doivent également pouvoir se connecter en écriture au besoin.
Actuellement, à l'ouverture du fichier, la box en pièce jointe s'affiche. Crois-tu que ça peux entraîner le fait que si deux utilisateurs sont dans le fichier en simultané (un en lecture et l'autre en écriture) ça peut engendrer la problématique ? J'en serais surpris... mais je ne sais plus du tout où chercher!
Un gros merci!
- Messages
- 4'064
- Excel
- 2021 FR 64 bits
- Inscrit
- 13/06/2016
- Emploi
- bénévole associations Goutte d'Or
2 utilisateurs peuvent accéder simultanément en écriture à ton fichier s'il a été déclaré partagé et cela peut poser problème s'il agissent sur les mêmes feuilles.
J'ai rencontré ce problème et ai pris comme solution de déclarer le fichier non partagé avec gestion d'une file d'attente pour l'écriture comme ceci
Sub trait_écriture()
'..... contrôle libre accès classeur pour écriture sinon attente 5 secondes ...........................................
Do While IsWorkBookOpenforwrite(Nom_fichier)
date_fin = DateAdd("s", 5, Now)
Application.Wait date_fin
Loop
'......... ouverture classeur ............................................................................
Workbooks.Open Filename:=Nom_fichier
Set classeur = ActiveWorkbook
'......... écriture dans classeur ............................................................................
'...
'..... sauvegarde classeur ...................................................................
classeur.Save
End Sub
Function IsWorkBookOpenforwrite(ByVal Nom_fichier As String) As Boolean
Dim no_fichier As Long
On Error Resume Next
no_fichier = FreeFile()
Open Nom_fichier For Binary Access Read Lock Read Write As #no_fichier
If Err.Number = 0 Then IsWorkBookOpenforwrite = False _
Else IsWorkBookOpenforwrite = True
Close no_fichier
End Function
Si je comprends bien, pour éviter que deux personnes se retrouvent en écriture en simultané dans le même fichier partagé, le code relatif à la file d'attente doit être inscrit dans "This workbook" afin qu'il s’exécute à l'ouverture du fichier, c'est bien ça ? ? Et je dois remplacer "Nom_fichier" par le nom de mon classeur excel je présume ? Pensez-vous que d'autres modification à code soit requise pour l'Adapter à mon fichier ?
Dois-je mettre un code quelconque sous la ligne "----- écriture dans classeur ----- " ?
Cette piste de solution m'apparaît logique et pourrait effectivement expliquer la problématique.
Me reste qu'à adapter le code!
À nouveau, merci!
- Messages
- 4'064
- Excel
- 2021 FR 64 bits
- Inscrit
- 13/06/2016
- Emploi
- bénévole associations Goutte d'Or
Dans mon cas, le fichier en accès simultané d'écriture était ouvert à partir d'un autre.
Pour vous, il faut juste placer une boucle d'attente si le fichier n'est pas ouvert en lecture seule. Du coup, vous n'êtes pas concerné par un code quelconque sous la ligne "----- écriture dans classeur ----- ". Cependant cette boucle demande une réflexion supplémentaire car sinon elle risque d'être infinie.
- Messages
- 4'064
- Excel
- 2021 FR 64 bits
- Inscrit
- 13/06/2016
- Emploi
- bénévole associations Goutte d'Or
Bonjour,
Pour que cela fonctionne, il faut que votre fichier soit en accès exclusif, c'est à dire non partagé. Le code pourrait alors être de ce type :
Private Sub Workbook_Open()
Application.DisplayAlerts = False
If ThisWorkbook.ReadOnly Then
réponse = MsgBox("Voulez-vous modifier ce fichier ?", vbYesNo + vbDefaultButton2)
If réponse = vbYes Then
Nom_fichier = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'..... contrôle libre accès classeur pour modification sinon attente 5 secondes .......................................
Do While IsWorkBookOpenForWrite(Nom_fichier)
date_fin = DateAdd("s", 5, Now)
Application.Wait date_fin
Loop
'..... lancement nouvelle instance Excel avec ouverture fichier pour modification ....................................
Set xl = New Application
xl.Visible = True
xl.Workbooks.Open fileName:=Nom_fichier, IgnoreReadOnlyRecommended:=True
'..... fermeture instance Excel avec ouverture fichier en lecture seule ....................................
Application.Quit
End If
End If
End Sub
Function IsWorkBookOpenforwrite(ByVal Nom_fichier As String) As Boolean
Dim no_fichier As Long
On Error Resume Next
no_fichier = FreeFile()
Open Nom_fichier For Binary Access Read Lock Read Write As #no_fichier
If Err.Number = 0 Then IsWorkBookOpenforwrite = False _
Else IsWorkBookOpenforwrite = True
Close no_fichier
End Function
Bonjour,
J'ai intégré votre proposition de code dans mon fichier.,Lorsque l'application s'ouvre en lecture seule, la fenêtre qui demande si on veut faire une modification s'affiche bien. Lorsque l'on clique oui, le fichier s'ouvre en mode écriture et se ferme en lecture seule. Bref, ce bout la fonctionne bien.
J'ai donc placé sur le réseau cette nouvelle programmation. Je laisse mes utilisateurs faire quelques saisies et vérifierai s'il y a toujours des entrées qui disparaissent ou non. Je me croise les doigts pour ça fonctionne... je vous reviens en début de semaine prochaine avec les résultats.
grand merci, et bon week-end!
Alors donc, après quelques jours d'essais, la problématique ne s'est pas représenté.
Je reste tout de même à l'affût au cours des prochains jours.
Entre temps, je considère la problématique résolue, un grand merci!