mise à jour d'un classeur par un autre

[Bertrouille]

Bonjour le fofo,

Je viens vers vous pour profiter de vos lumières et de vos compétences.

Voici ma problématique, j’ai un classeur contenant une base de 22000 enregistrements (regroupant 400 noms sur 52 semaines) je la nommerai BDD1
Cette BDD1 est un classeur partagé sur un serveur, elle doit être mise à jour toutes les semaines par des personnes identifiées.

Pour renseigner et mettre à jour la BDD1, j’ai créé un classeur(je le nommerai REQ) contenant des commandes permettant d’identifier l’utilisateur et s’il est autorisé d’extraire de la BDD1 les enregistrements dont il a l’administration pour qu’il puisse travailler en local. Jusque là, pas de problème.

Mon souci apparaît quand du classeur REQ je veux mettre à jour le classeur BDD1.

Voici le code que j’ai écrit pour mettre à jour :

Sub misajour()

Workbooks("accueil.xls").Sheets("index").Select <= REQ
nbemployeagence = Workbooks("accueil.xls").Sheets("index").Range("b5", Range("b5").End(xlDown)).Count

Workbooks.Open Filename:=\\serveur\heure$\paye.xls <= BDD1
nbemployetotal = Workbooks("paye.xls").Sheets("en_cours").Range("b2", Range("b2").End(xlDown)).Count

For nba = 1 To nbemployeagence
For nbt = 1 To nbemployetotal
If Workbooks("accueil.xls").Sheets("index").Range("aa" & (4 + nba)).Value = Workbooks("paye.xls").Sheets("en_cours").Range("aa" & (1 + nbt)) Then
Workbooks("accueil.xls").Sheets("index").Range("aa" & (4 + nba)).EntireRow.Copy
Workbooks("paye.xls").Sheets("en_cours").Range("aa" & (1 + nbt)).EntireRow.PasteSpecial
End If
Next nbt
Next nba
Workbooks("paye.xls").Close savechanges:=True

Workbooks("accueil.xls").Close savechanges:=False

End Sub

Comme vous pouvez le constater je fais du ligne par ligne ce qui prend un temps fou et rend la procedure inutilisable.

Voila pourquoi je me tourne vers vous, avez-vous une idée pour optimiser ce processus ? Pour ma part je tourne en rond.

J’espère avoir été suffisamment clair et désolé pour la longueur du message

Merci d’avance.

Bertrouille qui patouille

 

[CBernardT]

Re : mise à jour d'un classeur par un autre

Bonjour Bertrouille,

1- Tu peux déjà éviter l'affichage :
En début de macro placer :
Application.ScreenUpdating = False

2-Sans les fichiers, un essai de macro par tableau dynamique pour accélérer les opérations.

Sub misajour()
Dim Tab1 As Variant, Tab2 As Variant
Dim nbemployeagence As Long, nbemployetotal As Long
Dim MyValue As Byte, i As Long, j As Long, k As Byte

' Vérification de l'intention de l'utilisateur
MyValue = MsgBox("Souhaitez-vous vraiment transférer les données hebdomadaires ?", vbYesNo + vbCritical + vbDefaultButton2, "DECISION DE TRANSFERT")
If MyValue = vbNo Then Exit Sub

Application.ScreenUpdating = False
With Workbooks("accueil.xls")
'Wb1.Sheets("index").Select ' <= REQ
Tab1 = .Sheets("index").UsedRange
nbemployeagence = .Sheets("index").Range("B5 ", Range("B65536").End(xlUp)).Count
End With
Workbooks.Open Filename:=\\serveur\heure$\paye.xls ' <= BDD1
With Workbooks("paye.xls")
Tab2 = .Sheets("en_cours").UsedRange
nbemployetotal = .Sheets("en_cours").Range("B2 ", Range("B65536").End(xlUp)).Count
End With

For i = 1 To nbemployeagence
For j = 1 To nbemployetotal
If Tab1(i , 27) = Tab2(j , 27) Then
For k = 1 To LBound(Tab1)
Tab2(j , k) = Tab1(i, k)
Next k
End If
Next j
Next i
' Report des données
With Workbooks("paye.xls")
.Sheets("en_cours").UsedRange = Tab2
.Close savechanges:=True
End With
Workbooks("accueil.xls").Close savechanges:=False
End Sub

Cordialement

Bernard

 

[Bertrouille]

Re : mise à jour d'un classeur par un autre

bonjour bernard,

merci pour ta reponse, je teste tout ça dans l'apres midi et je ne manquerai pas de tenir toute la communauté informée du résultat

bertrouille qui a retrouvé la pechouille

 

[Creepy]

Re : mise à jour d'un classeur par un autre

Bonjour le Forum, Bertrouille,

Je pense que pour toi la meilleure solution est de faire une base de données sur le réseau. En effet ce sera beaucoup plus rapide de faire une requête SQL que de balayer tout un document Excel.

Si cela ne t'es pas possible, voici quelques pistes que tu peux explorer.

Dans ton code ce qui génère un long delai c'est d'une part le fait qu'il y ait 22 000 lignes et surtout le fait que tu compares chaque ligne de ton fichier BdeD à celui de req !! Bonjour le traffic reseau !!! C'est ton boss qui va être content !!

voici quelques conseils qui je l'espère vont reduire ce temps :

1 - Stock les données de ton fichier REQ dans un tableau !!! Comme cela tu vas sur le reseau et tu compares au ficheir cela va eviter les pertes de temps.

2 - Tu peux aussi stocker le contenu du document BdeD dans un tableau et comparer tes données tableau à tableau. une fois fait tu ecris dans le ficheir BdeD les données.

Avantage comme tu travailles dans le mémoire du PC tu es super rapide et tu limites le traffic réseau qui est souvent source de pertes de temps.

3 - Mets un Exit for juste avant ton End If. en effet si tu trouves ta ligne en mettons ligne 50, ton code copie/colle les données et continue de boucler sur les lignes restantes. Hors si tu as une seule ligne correspondante pourquoi continuer !! ?? Autant passer à la ligne suivante de ton fichier REQ non ?

4 - Si possible essaie de copier ton fichier en local, faire les modifications et recopier sur le reseau, là encore gain de traffic et donc de temps.

Voila, j'espère que ces quelques pistes t'aideront, mais je serais toi je me pencherais sur la solution base de données, qui sera de loin la plus rapide.

A+

Creepy

 

[Bertrouille]

Re : mise à jour d'un classeur par un autre

bonjour bernard, creepy, le fofo

le serveur ayant planté hier je n'ai pu essayer le code qu'en local et tout allait bien.
aujourd'hui tout est en place, et ça fonctionne un peu long (environ 2 minutes pour enregistrer les modif sur la BBD globale) mais par rapport au ligne par ligne il n'y a pas photo.

Pour répondre à creepy, je suis d'accord avec toi que ça irait plus vite sans passer par Excel mais le boss ne veut pas investir donc je fais avec les moyens du bord

Merci encore à tous les deux pour votre aide et surtout votre réactivité

Bertrouille

 

[Creepy]

Re : mise à jour d'un classeur par un autre

Hello All,

Faudra leur expliquer aux boss un jour que parfois investir un peu permet de recuperer sur le long terme !

Pfff tous les même. Access est pas si cher que ca ! Surtout pour une entreprise qui récupère la TVa, amortit, etc ...

Tu peux aussi lui parler de Mysql qui est gratuit. Là c'est le top du top en base de données ! Prenonce juste le mot magique : GRATUIT !!! lol

Pour te donner un exemple j'avais de grosse grosse requête sur une base Access en reseau (genre 12 mn de traitement) je suis passé en MySQL : 45s !!!

A+

Creepy