> Tous les forums > Forum Bureautique
 macro pour comparer 2 listesSujet résolu
Ajouter un message à la discussion
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]
maryloo2005
  Posté le 02/05/2015 @ 14:18 
Aller en bas de la page 
Astucienne

Bonjour, (Version Excel 2010)

J'ai un problème que je sais résoudre sans macro mais qu'il faudrait transposer en macro pour que ce tableau puisse être utilisé par une tierce personne utilisatrice d'Excel mais sans compétence informatique. Je connais Excel mais pas trop les macros.

J'ai essayé mais cela a buggé...

En fait, chaque semaine, on reçoit des pièces à réparer (colonne K : Reperation_Entry_Date), on doit pouvoir savoir s'il y a des références nouvelles (colonne D : Reference_Watch) par rapport à toutes les références qui existaient déjà avant cette semaine.

Chaque semaine, la personne utilisatrice du tableau recevra la base de données complète (feuille Reperation). Dans le doc joint, j'ai vidé les données des colonnes qui ne nous servent à rien mais la base reçue est normalement complètement remplie, donc très lourde.

Chaque semaine, de nouvelles lignes s'ajoutent en bas de la base.

L'idéal serait que la personne utilisatrice n'ait qu'à entrer le jour de début de la semaine et le jour de fin de la semaine dans 2 cellules utiles plus tard pour le filtre puis elle cliquerait sur un bouton qui déclencherait la macro et elle pourrait lire, dans une 2e feuille, les références nouvelles.

En V1 et en W1 j'ai copié-collé (sans les guillemets) "Reperation_Entry_Date". La personne utilisatrice aura juste à saisir (sans les guillemets) ">=13/04/2015" en V2 et "<=19/04/2015" en W2, par exemple pour la semaine 16. Bien sûr, elle aura une nouvelle base chaque semaine et elle changera ces dates chaque semaine.

Ensuite, je pense qu'il faut faire les manipulations suivantes et c'est cela que je n'arrive pas à automatiser :

1) Dans la base, supprimer toutes les colonnes inutiles et ne conserver que les colonnes D et K qui deviennent donc les colonnes A et B.

2) Trier dans l'ordre croissant A à Z des Reperation_Entry_Date.

3) Filtrer la semaine. Pour l'exemple cité ci-dessus : semaine du 13/04 au 19/04/2015 (ou semaine 16), je pensais faire un filtre avancé avec la base complète (colonnes A et B) et la zone de critères (V1 à W2), à filtrer sur place.

4) Cela nous donne uniquement les lignes pour la semaine. Copier-coller ce résultat (de A1 à B....) dans la cellule A1 d'une nouvelle feuille du classeur qui, idéalement serait à nommer "semaine 16"mais je pense que cela n'est pas possible avec la macro. Si ce n'est pas possible, ce n'est pas grave, l'utilisatrice pourra à la fin renommer cette Feuil2 "semaine 16".

5) Revenir dans la première feuille "Reperation" et supprimer les lignes filtrées de la base (il faut sélectionner toutes les lignes apparentes, sauf la ligne des titres puis supprimer ces lignes).

6) Enlever le filtre de la base Reperation. Cela nous laisse la base complète, débarrassée des lignes de la semaine 16.

7) Trier la colonne A (Reference_Watch) dans l'ordre croissant A à Z.

8) Se replacer dans la Feuil2. En B2, supprimer le contenu de la cellule et entrer la formule suivante =RECHERCHEV(A2;Reperation!A:B;2;FAUX) puis recopier cette formule vers le bas. Cela va remplacer les dates qui apparaissaient dans ces cellules.

9) Les nouvelles références sont celles pour lesquelles apparaît le message d'erreur #N/A.

10) Filtrer en colonne B pour ne faire apparaître que les #N/A. Masquer la colonne B. Enlever les doublons avec Données/ Supprimer les doublons.

11) On obtient ainsi la liste des nouvelles références apparues en semaine. Chaque semaine, on devra noter les dates de la semaine et lancer la macro pour refaire l'extraction.

J'espère que je me suis fait comprendre. Peut-être existe-t-il une méthode plus simple ?

Ci-dessous le fichier concerné. Je vous remercie d'avance de voir si c'est possible d'automatiser cela, afin que cela soit simple d'utilisation.

MERCI D'AVANCE !

http://cjoint.com/?0Ecnjz4bi2r

Publicité
ferrand
 Posté le 02/05/2015 à 15:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Je ne saurais te dire si ta description est complète qu'une fois que j'aurai bien compris la question .

Quelques points à éclaircir ou confirmer (et excuse-moi si tu l'as déjà dit...) :

1) Si j'ai bien compris, chaque semaine, le classeur contenant la feuille "Reperation" est reçu, qui comprend les données déjà reçues la semaine précédente, plus de nouvelles. Il faut donc extraire les nouvelles données (limitées au colonnes D et K) en fonction de la date.

Les nouvelles données sont-elles systématiquement ajoutées à la fin ?

2) Tu parles de réception hebdomadaire : est-ce que les nouvelles données correspondent à une semaine calendaire (du lundi au dimanche) systématiquement à chaque fois, ou peut-il y avoir des écarts ?

3) Il apparaît compte-tenu du schéma de réception, plus judicieux d'extraire les données hebdomadaires souhaitées dans un nouveau classeur (à partir duquel sera pilotée l'opération. On peut concevoir l'extraction vers ce classeur dans une feuille hebdomadaire ajoutée chaque semaine ?

4) Il semble qu'il faille en outre traiter l'extraction correspondant à la semaine pour limiter aux nouvelles références (col. D) apparues dans la base ?

Pour la méthode, lorsque l'on utilise VBA, il est (presque) toujours préférable de procéder autrement que l'on aurait opéré manuellement dans le tableur, mais penser la façon qu'on utiliserait avec des documents "papier" qui se transpose souvent plus simplement en VBA...

A te lire

maryloo2005
 Posté le 02/05/2015 à 16:04 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonjour ferrand,

Merci pour ta réponse rapide. De + je crois bien que ce n'est pas la 1ère fois que tu me viens en aide ! Je vais répondre à la fin de ta question, en gras.

1) Si j'ai bien compris, chaque semaine, le classeur contenant la feuille "Reperation" est reçu, qui comprend les données déjà reçues la semaine précédente, plus de nouvelles. Il faut donc extraire les nouvelles données (limitées au colonnes D et K) en fonction de la date. OUI.

Les nouvelles données sont-elles systématiquement ajoutées à la fin ? Je ne sais pas, mais je crois que oui. Je pense que la base vient d'Access et basculée dans Excel. C'est la raison pour laquelle je fais un tri des dates d'entrées. Ainsi, les nouvelles données seront à la fin de la base de données (en bas).

2) Tu parles de réception hebdomadaire : est-ce que les nouvelles données correspondent à une semaine calendaire (du lundi au dimanche) systématiquement à chaque fois, ou peut-il y avoir des écarts ? Oui, c'est du lundi au dimanche.

3) Il apparaît compte-tenu du schéma de réception, plus judicieux d'extraire les données hebdomadaires souhaitées dans un nouveau classeur (à partir duquel sera pilotée l'opération. On peut concevoir l'extraction vers ce classeur dans une feuille hebdomadaire ajoutée chaque semaine ? Oui, je pense. Si c'est plus pratique, c'est ce qui compte. Est-ce que la zone de critères où on n'aurait plus qu'à entrer les dates pour le filtre pourrait être déjà prête dans ce nouveau classeur ? Cela serait bien !

4) Il semble qu'il faille en outre traiter l'extraction correspondant à la semaine pour limiter aux nouvelles références (col. D) apparues dans la base ? Oui.

Pour la méthode, lorsque l'on utilise VBA, il est (presque) toujours préférable de procéder autrement que l'on aurait opéré manuellement dans le tableur, mais penser la façon qu'on utiliserait avec des documents "papier" qui se transpose souvent plus simplement en VBA... Tu as certainement raison !

Merci.

ferrand
 Posté le 02/05/2015 à 16:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bon ! J'avais bien compris, alors !

Je vais voir comment te concocter quelque chose de pratique... Mais j'en suis au punch

et c'est samedi, et n'ayant pas prévu mon repas du soir je vais devoir recourir au pizzaiolo (en face de chez moi !)

Mais bon ! D'ici la fin du week-end...

NB- Le filtrage est une fonction très utile d'Excel, pour consultation ou lors d'opérations en interactivité... mais le but de VBA est de te fourir le résultat final sans intervention intermédiaire de l'utilisateur, on peut donc se passer de filtrer (et ça économise du temps de traitement)

maryloo2005
 Posté le 02/05/2015 à 17:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

OK merci, bonne soirée et à demain alors !

xlcomparator
 Posté le 03/05/2015 à 07:41 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page


Bonjour Maryloo2005,
j'ai écrit un outil pour dépanner les personnes qui ont a comparer des listes et qui ne sont pas à l'aise avec les macros Excel.
Il s'appelle XL Comparator, tu trouveras ici une vidéo de démonstration pour vérifier qu'il répond bien à ton besoin.

A+
Marc

ferrand
 Posté le 03/05/2015 à 09:44 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

xlcomparator a écrit :

Bonjour Maryloo2005,
j'ai écrit un outil pour dépanner les personnes qui ont a comparer des listes et qui ne sont pas à l'aise avec les macros Excel.
Il s'appelle XL Comparator, tu trouveras ici une vidéo de démonstration pour vérifier qu'il répond bien à ton besoin.

A+
Marc

Encore ta pub !

ferrand
 Posté le 03/05/2015 à 12:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

En préalable, avant d'entrer dans le vif du sujet

Interface à partir de laquelle l'utilisateur lancera l'extraction :

Il n'y a à saisir que le nom du classeur, sans omettre l'extension (.xlsx). Une fois qu'il est saisi, s'il ne change pas, il demeurera !

Pour les dates, rien à saisir : à l'ouverture du classeur, la semaine précédente est automatiquement définie pour l'extraction.

Plus exactement, du samedi de la semaine S au vendredi de la semaine S+1, la semaine S sera prise en compte.

Si cela correspond à la périodicité de réception, parfait, sinon m'indiquer les correctifs qu'il faudrait apporter.

On peut sélectionner une autre semaine (vers le passé, par rapport à celle indiquée ci-dessus, non vers l'avenir !). Compte tenu des données de ton classeur, on pourra remonter jusqu'à la semaine du 03/01/2011. On peut fixer une autre date de départ, il s'agit simplement d'une date conventionnelle au-delà de laquelle on considère inutile de remonter, et qui est utile pour paramétrer l'utilisation de la barre de défilement destinée à faire varier la semaine d'extraction.

Une barre de défilement s'incrémente selon 2 pas différents (un "petit" et un "grand") selon que l'on clique sur les flèches d'extrémités [pas de 1, soit une semaine] ou dans la barre de part ou d'autre du curseur [pas de 50, soit presque un an].

Les valeurs de la barre se situent entre un minimum : 0 correspondant à la date conventionnellement choisie comme point de départ ; et un maximum (la semaine affichée à l'ouverture, variable, le maximum est donc réactualisé à chaque ouverture du classeur. Toute modification de la valeur de la barre (clic sur les flèches ou dans la barre) entraîne la mise à jour de la semaine définie pour l'extraction.

A noter que ce dispositif permet de refaire une extraction antérieure. L'extraction actuelle devrait en principe correspondre à la semaine définie automatiquement à l'ouverture, qu'il n'y a alors pas lieu de modifier.

J'envisage de nommer les feuilles d'extraction selon le numéro de semaine, exemple : S.2015-18. Si tu souhaites autre chose, me l'indiquer.

Le nom de ce classeur n'est pas utilisé dans le code, il peut donc être renommé à convenance. De même pour la feuille interface (Feuil1), je n'utilise que son rang dans le code, il faut donc qu'elle demeure la première feuille du classeur, mais peut être renommée à convenance.

J'ai opté pour des contrôles dits "contrôle de formulaire", permettant de concentrer tout le code sur un seul module standard (à l'exception de la macro lancée à l'ouverture). Cependant, ces vieux contrôles intégrés à Excel ne permettent pas de mise en forme très sophistiquée... Aussi si tu souhaites affiner la mise en forme, je pourrais les remplacer par des contrôles active-X (au moins coloriables) [mais codes à ajuster, car ils ne se programment pas de la même façon]. Cela vaut surtout pour la barre de défilement car le bouton peut être remplacé dans les mêmes conditions par n'importe quelle forme, offrant d'autres possibilités de mise en forme.

Pour tester :

1) Tu effaces les éléments de dates en E6, F6, H6, J6 ; tu enregistres le classeur et le fermes ; tu le rouvres et tu devrais voir réapparaître les même données dans ces cellules, puisque calculées sur la date du jour.

2) Tu agis sur la barre de défilement, pour voir les modifications qui s'ensuivent...

http://cjoint.com/?EEdmvFgNXb1

Je ne m'étends pas ici sur les calculs de dates, mais si tu es intéressée je pourrais fournir des explications plus détaillées.



Modifié par ferrand le 03/05/2015 12:09
maryloo2005
 Posté le 03/05/2015 à 12:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonjour xlcomparator,

Cet outil a l'air bien pratique bien que pour mon cas perso, cela ferait trop de manipulations chaque semaine. Je l'ai essayé mais j'ai eu un message d'erreur.

Il faut dire que ma base fait 18 M0 ! c'est peut-être trop lourd, non ?

Merci tout de même pour l'astuce que je pourrais utiliser pour de plus petits fichiers.

Bon dimanche.

maryloo2005
 Posté le 03/05/2015 à 12:51 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonjour ferrand,

Je vois que tu as bien travaillé en ce dimanche matin ! MERCI. En fait, l'extraction ne se fera certainement que pour la semaine précédente ou présente. Je ne pense pas remonter en 2011 ! Mais l'idée de la barre de défilement est bien pratique et me convient parfaitement. L'esthétique est tout à fait suffisante. Rien à changer de ce côté là.

Par contre, j'ai fait ce que tu m'as dit : effacer les données, enregistrer le classeur, le fermer, le rouvrir mais j'ai eu une première fenêtre de bug disant ceci :

Erreur d'exécution '1004'

La méthode 'Worksheets' de l'objet '_Global' a échoué

j'ai cliqué sur le bouton "fin" pour pouvoir continuer.

J'ai indiqué le nom du classeur dans lequel j'ai ma base, j'ai choisi une semaine puis j'ai voulu cliquer sur le bouton "Lancer l'extraction" mais cela n'a rien lancé du tout. Le bouton s'est entouré de poignées, comme si je cliquais sur un dessin !

Voila pour l'instant où j'en suis. Cet après-midi, je ne serai pas trop disponible. Rien ne presse de toute façon.

MERCI encore.

ferrand
 Posté le 03/05/2015 à 13:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Normal que l'extraction ne donne rien : pas de macro attachée au bouton, je suis en train de l'écrire.

Par contre ton erreur me paraît curieuse, je n'en vois pas la raison. Au lieu de cliquer sur Fin, clique sur Débogage, afin de repérer la ligne surlignée sur laquelle se déclenche l'erreur. Tu cliqueras ensuite sur l'icône en forme de carré dans l'éditeur pour réinitialiser (correspond à Fin).

L'erreur ne se produit pas chez moi, et sans cette indication je ne sais où aller chercher. D'autant plus que l'erreur 1004 est une erreur propre à Excel, dépendant souvent de l'environnement... Quelle version d'Excel utilises-tu ?

ferrand
 Posté le 03/05/2015 à 16:16 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

j'ai terminé la macro principale, mais je vais certainement la reprendre (au moins sur la fin)...

Voici (pour clarifier les informations) ce qu'elle fait, pas à pas :

1) Affectation du nom du fichier (E3) à une variable, vérification du nom : - qu'il y en ait un, - qu'il comporte une extension, - que cette extension soit "xls" ou "xlsx". Rend la main à l'utilisateur pour rectification, le cas échéant.

2) Par sécurité, réexécute la mise à jour de la semaine définie, ainsi si l'utilisateur a opéré des modifs manuelles avant de lancer, un réalignement sur la valeur de la barre de défilement est fait, avant de poursuivre.

3) Construit le nom de la feuille à ajouter pour l'extraction (dans une variable) et récupère la date de début de la semaine à extraire (dans une variable également).

4) Vérifie qu'aucune feuille de ce nom n'existe déjà dans le classeur. Si c'était le cas, demande à l'utilisateur si souhaite refaire cette extraction, s'il répond non la procédure est abandonnée, s'il répond oui la feuille concernée est affectée à une variable objet-feuille. (NB: une feuille a pu être créée et la procédure interrompue avant terme, ce qui explique qu'on doive la refaire, par exemple). Si pas de feuille à ce nom (cas général), une feuille est ajoutée en fin, nommée, les colonnes A et B sont redimensionnée et les en-têtes portées ligne 1.

5) La suite se déroule dans le classeur base de donnée qui doit avoir été préalablement ouvert. En cas d'erreur, classeur non ouvert ou ouvert mais dont le nom ne correspond pas à celui attendu, un message le signale à l'utilisateur et la procédure est interrompue.

6) Récupération des numéros de dernière ligne et de dernière colonne utilisées de la base (dans des variables). Tri (par sécurité) sur la colonne K, ordre croissant.

7) Détection (sur la col. K) en partant de la fin et en remontant, de la ligne dont la valeur est inférieure ou égale à la fin de semaine recherchée (conservation dans une variable), recherche à partir de cette dernière ligne, en remontant, de la ligne ou début la semaine recherchée (et conservation également).

8) De cette ligne de début à cette ligne de fin, affectation des valeurs des colonnes D et K de la base aux colonnes A et B de la feuille prévue à cet effet.

9) Chaque valeur extraite en A de la feuille est comparée à la partie de la colonne D de la base précédant la semaine extraite afin de vérifier si cette valeur y figure ou non (utilisation de la fonction de feuille de calcul NB.SI). Si elle y figure, la mention est mise en grisé, sinon "nouveau" est ajouté en colonne C.

Tout se déroule parfaitement jusqu'au 8. Mais le 9 prend un temps conséquent pour s'exécuter ! Je n'avais pas complètement apprécié les dimensions : semaine 17, 10 valeurs extraites, mais c'était trompeur ; semaine 16, 1157 ; semaine 15, 800. Et la même valeur nouvelle dans la semaine peut en outre y être répétée.

J'avais trouvé judicieux de conserver l'extraction complète pour signaler les "nouveau", mais il est sans doute mieux d'épurer l'extraction avant report sur la feuille ! Et je réfléchis si une méthode autre que NB.SI permet d'optimiser l'exécution.

maryloo2005
 Posté le 03/05/2015 à 23:10 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonsoir,

Je reviens faire un petit tour sur le forum. Je n'ai plus le message d'erreur 1004 !

Versions : Chez moi j'utilise Xls 2013 mais la personne qui fera les manip sera sur 2010.

Oui la base est très, très lourde d'ordinaire : (+ de 150 000 lignes et un poids de 18 Mo au 20/04/2015) et chaque jour des centaines de lignes s'ajoutent !! Pour alléger le fichier joint, j'avais supprimé complètement le contenu des colonnes autres que D et K. Je pense qu'il faudrait commencer par supprimer toutes les colonnes inutiles de la base (De A à T, SAUF les colonnes D et K qui deviendront donc les colonnes A et B).

Pour le reste, je te fais confiance. Je suis impressionnée par toutes ces manipulations et je voudrais comprendre comment tu utilises la fonction NB.SI pour comparer les 2 listes !

Merci, bonne nuit.

ferrand
 Posté le 04/05/2015 à 10:23 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

J'ai apporté une modification au schéma précédent qui semble améliorer un peu les choses. Rien de modifié jusqu'au 8. Pour la suite on oublie NB.SI pour procéder autrement :

9) La partie de la base antérieure à la semaine extraité est retriée sur la colonne D (en préparation) de la suite.

10) L'extraction sur la feuille est triée de la même façon, sur la colonne A donc. Et on y procède à une élimination des doublons (de références), de façon à alléger le test.

11) La plage de références préexistantes dans la base et la plage de références de la semaine étant triées identiquement en ordre croissant, on peut défiler les références de la semaine et pour chacune, l'éliminer lorsqu'on trouve la même référence dans la base, ou stopper le test lorsqu'on atteint une référence supérieure. On passe alors à la suivante mais on reprend la comparaison à partir du point atteint dans la base (réf. suivante lorsqu'il y a eu égalité et élimination, même réf. quand apparue supérieure). Un seul passage permet donc d'éliminer les références utilisées antérieurement.

12) Rétablissement du tri par date sur la base et sur la feuille.

Le temps d'expurger quelques éléments parasites et de vérifier que pas d'erreur flagrante, je t'envoie une version à tester.

Si l'erreur 1004 ne se produit plus, c'est qu'elle était provoquée par une situation conjoncturelle qui ne s'est pas reproduite... Rien à chercher si elle ne survient pas à nouveau.

A suivre...

ferrand
 Posté le 04/05/2015 à 10:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

http://cjoint.com/?EEek3LjaCAn

Par rapport à ma version antérieure, le temps d'exécution a été divisé par 4 en gros. Déjà mieux, il faudrait que je vérifie si en procédant hors de la feuille jusqu'à la fin on ne gagnerait pas encore un peu de temps...

Ce qui est sûr, c'est que la méthode conduit à un temps de vérification de plus en plus long puisque la base augmente continuellement. Une amélioration pourrait être apportée si au lieu de se référer à la base pour trouver les nouvelles références, on se référait à une liste des références déjà apparues, sans doublon. Quand j'aurais un moment, je regarderais pour la constituer, même si l'opération peut être un peu longue, elle ne serait à faire qu'une fois. Ensuite on n'aurait qu'à ajouter les nouvelles à la liste.

Teste la version actuelle avec ta base réelle. Je ne pense pas que le poids de la base puisse avoir une incidence sensible sur l'exécution de la procédure. La taille de la liste sur laquelle on opère ne varie pas, que l'on supprime ou non les colonnes voisines...

maryloo2005
 Posté le 04/05/2015 à 22:32 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonsoir ferrand,

Je viens d'essayer. C'est super ! Je retrouve bien les nouvelles références que j'avais moi-même pour la semaine 16 !

J'ai testé d'autres semaines. C'est finalement assez rapide et très bien présenté. Merci. J'ai testé aussi les messages qui apparaissent si on demande deux fois la même semaine.

Même après avoir renommé ton fichier.xlsm, cela a encore bien fonctionné.

J'ouvre d'abord la grande base puis j'ouvre ton fichier .xlsm. C'est bien comme cela qu'il faut procéder ? Par contre, une fois je n'ai pas eu l'erreur 1004 et une autre fois je l'ai eue.

Voici la ligne surlignée en jaune quand je clique sur débogage (dans la partie Function DateBarre() :

s = Worksheets(1) .Shapes ("Barre de défilement 1") .ControlFormat.Value

Je pense que l'erreur survient quand je veux ouvrir ton fichier .xlsm, saisir tout de suite les variables (nom du fichier base et dates de la semaine) et lancer tout de suite l'extraction.

Je n'ai pas le message d'erreur quand je l'ouvre, puis que j'efface les variables, puis que je l'enregistre, puis que je le ferme, puis que je l'ouvre à nouveau...

Donc faut-il faire ces manipulations à chaque fois ?

Merci pour ta réponse. A suivre.

ferrand
 Posté le 05/05/2015 à 01:05 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

J'aurai une autre version à te proposer, mais je dois d'abord vérifier (car écrite pour une bonne part en regardant un film, ce qui comporte quelques risques !). Elle est basée sur la conservation d'une liste des références, qui devrait par la suite accélérer la vérification des références extraites hebdomadairement, puisqu'on passe d'une base de plus de 150000 à une liste de 2240. J'ai aussi quelques ajouts à faire pour agrémenter la première constitution de la liste (même si ça ne sert qu'une fois), car au premier essai j'ai interrompu l'opération au bout de 10 minutes pensant que ça boguait quelque part, ce qui n'était pas le cas, l'opération a en fait finalement dépassé 15 minutes, et il y a intérêt dans ce cas à avoir un indicateur que le programme travaille et que les choses avancent (lentement mais) normalement. Je n'aurai sans doute pas le temps demain mais je pense tester d'ici après demain.

Par contre ton erreur 1004 me chagrine un peu, d'autant qu'elle n'est pas systématique, ce qui complique...

Je n'ai pas bien saisi si elle se produisait à l'ouverture, ou lors d'un changement de semaine par action sur la barre de défilement, ou bien lorsque l'extraction est lancée. Car la ligne de code "fautive" est concernée dans ces 3 cas. Le problème est que n'ayant pas l'erreur, je n'ai pas moyen de tester comment la surmonter !

En tout cas, cette partie du code est indépendante de l'extraction proprement dite, et n'a aucun rapport avec la base. Essaie de remplacer dans la ligne de code indiquée "Barre de défilement 1" par "Scroll Bar 1", pour voir si l'erreur continue de se produire.

maryloo2005
 Posté le 06/05/2015 à 19:24 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonsoir ferrand,

Je viens de ré-essayer :

1) j'ouvre la base

2) j'ouvre le fichier qui contient la macro, je tape le nom de la base, je lance l'extraction : ça marche.

3) je change de semaine avec la barre de défilement, je relance l'extraction : ça marche.

Pas de problème ! Pas de message d'erreur.

Je voudrais savoir combien de temps tu as passé pour résoudre ce problème ?

Encore merci et bonne soirée.

Mary

ferrand
 Posté le 07/05/2015 à 21:06 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Si cela règle la question tant mieux, sinon j'aurais substitué un contrôle active-X. Comme je l'ai dit, je n'ai pu vraiment chercher comment résoudre le problème, n'ayant pas l'erreur ! Mais j'avais constaté que lorsqu'on cliquait droit sur le contrôle pour le sélectionner (ce qui fait apparaître son nom dans la zone Nom, à gauche de la barre de formule, [on peut d'ailleurs modifier le nom dans cette zone...]), le nom par défaut apparaissait en anglais avant de se mettre en français, ce qui m'a brusquement remis en mémoire qu'à l'origine, Microsoft fournissait une version traduite de VBA permettant de programmer en français, non sans que cela suscite quelques problèmes par moments (on l'a d'ailleurs vu, dans l'autre sens, lorsque cette pratique a été abandonnée, un traducteur était censé remettre le code en anglais, il était rare que la macro retraduite s'exécute sans erreur !). Or les noms français résultent d'une traduction des noms de code, réalisée à la même époque, et la possibilité que l'interpréteur de commandes puisse ne pas les identifier n'était pas à exclure...

J'ai pas ailleurs bouclé le programme d'extraction d'une liste complète de références : après divers essais, j'ai abandonné l'idée d'un indicateur pour suivre l'avancement de la procédure, cela avait surtout pour effet d'augmenter considérablement la durée d'exécution jusqu'à 2 heures (estimation, je ne suis pas allé jusqu'au bout !). Sans indicateur, la procédure se déroule chez moi en 13 minutes environ. Il me reste à nettoyer les débris de mes essais et je te le livre avec explications.

ferrand
 Posté le 08/05/2015 à 10:40 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Voilà la version 2, assez analogue dans son fonctionnement à la version précédente, mais qui effectue la détection des nouvelles références à partir d'une listes des références existantes, déjà trouvées dans la base. Il convient donc, avant de l'utiliser pour l'extraction des nouvelles références, de lui faire constituer la liste des références présentes dans la base.

Pour cela, suivre les indications de la zone colorée en jaune : mentionner le nom du dernier fichier base traité, dont on peut extraire la totalité des références déjà utilisées (dans la zone prévue pour cela), ouvrir ce classeur bien sûr, et cliquer sur le bouton 'Constituer la liste de références'.

L'opération est longue, donc patienter jusqu'à ce qu'elle soit terminée. Au bout de quelques secondes, la feuille apparaîtra masquée, rien d'anormal, laisser le programme travailler, avec la base modèle l'opération testée a pris 13 minutes environ chez moi. Une fois terminée, la feuille sera réapparue, les 3 premières lignes des indications dans la zone jaune, exécutées, auront disparues, le bouton 'Constituer...' aura été rendu inactif (pour éviter qu'un clic malencontreux ne relance l'opération) et le classeur Base fermé. La liste aura été constituée sur la feuille Refer, vérifier que tout est en ordre ; le test sur la base modèle recensait 2240 références.

Si tout paraît OK, il n'y a alors plus qu'à cliquer sur le bouton 'Liste de références terminée' : cela effacera la zone jaune, supprimera les boutons temporaires utilisés, et supprimera le module (Module2) qui contenait ces procédures à n'utiliser qu'une fois.

Le classeur (qu'on peut renommer, comme précédemment) se présentera alors avec 3 feuilles : Feuil1 (qu'on peut renommer aussi), peu différente de la version 1, Refer contenant la liste des références déjà enregistrées, Semaine destinée à l'extraction hebdomadaire des nouvelles références. Si l'on veut renommer ces 2 dernières feuilles, il faudra également changer les noms dans les macros, sinon ça ne pourra plus fonctionner. Enregistrer ce classeur afin de pouvoir l'utiliser pour la prochaine extraction (pas d'enregistrement automatique dans la mesure où il peut toujours s'avérer utile de fermer sans enregistrer...)

http://cjoint.com/?EEik3wMlt5g

ferrand
 Posté le 08/05/2015 à 11:34 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Version 2 : interface

Elle est assez semblable à la précédente. Zone de saisie pour le nom du classeur base de données, affichage de la semaine extractible, possibilité de modifier (vers le passé) avec une ScrollBar [même si cela ne présente d'utilité que pour une ou deux semaines en cas de retard dans les extractions ; le pas long a été ramené à 5, mais on peut remonter rapidement vers l'origine en faisant coulisser le curseur de la barre], en dessous, la dernière semaine traitée sera rappelée (dès lors qu'il y en aura une) pour info., bouton pour lancer l'extraction.

A l'ouverture, l'affichage de la semaine logiquement extractible se fait automatiquement (proc. évènementielle Workbook_Open, dans le module ThisWorkbook) à partir de la date du jour, antériorisée de 5 jours : elle détermine le lundi précédant cette dernière date [fonction LuPréc renvoyant la date du lundi précédant une date donnée, s'il ne s'agit pas d'un lundi], affecte la valeur max pouvant être prise par la scrollbar [calculée par la fonction MaxBarre = nombre de semaine écoulées depuis le lundi 03/01/2011 (référence conventionnelle) à la date du lundi précédemment calculée], affecte cette valeur max à la scrollbar et lance la procédure MajSemaine, destinée à mettre à jour l'affichage de la semaine (théoriquement) à traiter.

Cette dernière procédure récupère la valeur de la scrollbar, reconvertie en date par la fonction DateBarre (date correspondant à un lundi), définit l'année à laquelle appartient la semaine commençant ce lundi [fonction Année, basée sur la propriété que le 28 décembre appartient toujours à la dernière semaine de l'année calendaire], puis le numéro de semaine [fonction Semaine qui le renvoit à partir de la date (lundi) et de l'année], et affiche ces données.

Une action sur la ScrollBar, modifiant sa valeur, relance cette procédure qui met alors à jour les données selon la nouvelle valeur de la barre.

Tout ceci fonctionne donc de la même façon que précédemment.

ferrand
 Posté le 08/05/2015 à 12:36 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Version 2 : extraction

L'extraction proprement dite des références de la semaine considérée se fait toujours dans la base de données, naturellement. C'est ensuite que les procédures se différencient.

Une différence, plus formelle que réelle, apparaîtra à l'examen du module contenant les procédures, c'est que la procédure d'extraction et déterminantion des références nouvelles, constituée auparavant par une seul macro, a été modularisée en diverses procédures réalisant des actions élémentaires. C'est plus conforme aux règles conseillées de programmation, et cela devrait permettre de s'y retrouver plus facilement par la suite en cas de dysfonctionnement...

Le bouton lance la procédure principale : ExtracSemaine, qui va commander les procédures secondaires au fil du déroulement :

- Elle récupère le nom du classeur base et lance une vérification (nom et ouverture) : fonction VérifBase qui l'opère et renvoie le numéro de dernière ligne de la base de données (si conforme) ou 0 (sinon).

- Si la vérification est positive, lance également la proc. MajSemaine (à des fins de sécurité, comme auparavant) mais avec un argument optionnel qui lui indique qu'elle doit en outre préparer la feuille Semaine pour la nouvelle extraction (effacement de l'extraction antérieure, indication de la semaine à extraire).

- Elle procède à la délimitation de la zone hebdomadaire à extraire de la base et opère l'extraction en reportant les valeurs extraites sur la feuille Semaine (comme auparavant).

- Elle lance une épuration de cette extraction (élimination des références doublons) : fonction EpurExtrac, qui trie l'extraction en ordre croissant des références et procède à l'élimination de doublons, et renvoie le numéro de dernière ligne occupée par l'extraction sur Semaine après épuration. (Si aucune référence n'a été extraite, le mentionne et renvoie 0.)

- Si des références ont été extraites, affecte la plage liste de référence à une variable, plage appelée par une fonction References qui trie la liste de la feuille Refer en ordre croissant des références et renvoie la plage de références (objet Range).

- Elle opère la comparaison des références extraites avec les références listées (comme auparavant) en effaçant les références déjà listées.

- Elle lance à la suite le bilan de cette opération : fonction InserRef qui rétablit l'ordre par dates de la liste de références (Refer), trie de la même façon les références (nouvelles) restant sur Semaine, ajoute ces nouvelles références à la liste Refer et renvoie le nombre de références ajoutées. Si aucune référence nouvelle n'a subsisté, le mentionne et renvoie 0.

- Elle affiche un message indiquant que l'extraction a été réalisée et, soit qu'il n'y avait pas de références relatives à la semaine cherchée, soit qu'il n'y avait pas de références nouvelles par rapport à la liste, soit le nombre de références nouvelles figurant sur Semaine et ajoutées à la liste.

On note que la recherche se faisant sur une liste de références, dès lors que cette liste est à jour, tout traitement d'une semaine antérieure ne peut aboutir qu'à : "aucune référence nouvelle". Il n'y a donc plus de signalisation particulière si l'on procède au traitement d'une semaine antérieure : si elle n'a pas été traitée, le résultat fera apparaître s'il y a lieu des références nouvelle, sinon non. Il est toutefois souhaitable de traiter les semaines dans l'ordre chronologique, si l'on veut que la liste de références comporte pour chacune la date de sa première apparition.

maryloo2005
 Posté le 11/05/2015 à 10:04 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

Bonjour Ferrand,

J'ai été absente ce w-end. En fait, j'avais envoyé ta première version, qui marchait bien chez moi, à la dame qui va s'en servir, avant que tu m'envoies la 2e version.

J'attends de ses nouvelles car elle ne rentre, elle aussi, que ce matin.

J'ai tout de même téléchargé ta 2e version. La liste de références s'est faite au bout d'un certain temps. Mais pour la semaine 16 cela m'a dit qu'il n'y avait pas de nouvelles références alors qu'il y en avait avec la 1ère version et avec mes essais à moi !

Donc pour l'instant, je préfère qu'elle utilise la 1ère version qui fonctionnait bien et on verra ce qu'elle en pense. A suivre donc !

Merci et bonne semaine.

ferrand
 Posté le 11/05/2015 à 11:03 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

maryloo2005 a écrit :

J'ai tout de même téléchargé ta 2e version. La liste de références s'est faite au bout d'un certain temps. Mais pour la semaine 16 cela m'a dit qu'il n'y avait pas de nouvelles références alors qu'il y en avait avec la 1ère version et avec mes essais à moi !

Après constitution liste, les nouveautés de la semaine 16 sont dans la liste, donc plus nouvelles !

ferrand a écrit

On note que la recherche se faisant sur une liste de références, dès lors que cette liste est à jour, tout traitement d'une semaine antérieure ne peut aboutir qu'à : "aucune référence nouvelle". Il n'y a donc plus de signalisation particulière si l'on procède au traitement d'une semaine antérieure : si elle n'a pas été traitée, le résultat fera apparaître s'il y a lieu des références nouvelle, sinon non. Il est toutefois souhaitable de traiter les semaines dans l'ordre chronologique, si l'on veut que la liste de références comporte pour chacune la date de sa première apparition.

Particularités 2e version -

• Liste globale de références, mise à jour à chaque traitement.

• Procédure plus rapide.

• Stabilité du fichier de traitement, une seule feuille hebdomadaire d'extraction, réutilisée.



Modifié par ferrand le 11/05/2015 11:07
maryloo2005
 Posté le 11/05/2015 à 11:24 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucienne

OK. Excuse-moi, je n'avais pas tout compris. J'avoue que c'est un peu complexe pour moi !

Finalement, je pense que je vais donc garder la première version car l'utilisatrice ne fera pas toujours ses extractions dans l'ordre chronologique.

Et avec la 2e version, il faut refaire la liste de réf à chaque fois ? Cela me semble donc plus long, non ?

ferrand
 Posté le 11/05/2015 à 12:10 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

maryloo2005 a écrit :

Et avec la 2e version, il faut refaire la liste de réf à chaque fois ? Cela me semble donc plus long, non ?

Justement non ! La liste est constituée définitivement. C'est d'ailleurs pour cela que j'ai mis une procédure de suppression de tous les éléments utilisés pour la constitution initiale de la liste.

Publicité
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]

Vous devez être connecté pour participer à la discussion.
Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !
Recevoir PC Astuces par e-mail


La Lettre quotidienne +226 000 inscrits
Avec l'actu, des logiciels, des applis, des astuces, des bons plans, ...

Les bonnes affaires
Une fois par semaine, un récap des meilleurs offres.

Les fonds d'écran
De jolies photos pour personnaliser votre bureau. Une fois par semaine.

Les nouveaux Bons Plans
Des notifications pour ne pas rater les bons plans publiés sur le site.

Les bons plans du moment PC Astuces

Tous les Bons Plans
Câble rétractable Baseus USB C vers USB C 60W à 7,99 €
7,99 € 10 € -20%
@Amazon
SSD WD Blue SN580 1 To (PCIe 4, NMVe M.2, 4150 Mo/s) à 64,99 €
64,99 € 76,99 € -16%
@Amazon
Smartphone 6.67 pouces Xiaomi Redmi Note 12 Pro 5G (FHD+ OLED 120 Hz, 6Go/128Go) à 169 €
169 € 260 € -35%
@AliExpress
PC portable Lenovo 15.6 pouces LOQ 15ARP9 (FHD IPS 144 Hz, Ryzen 7 7435H, 16 Go RAM DDR5, SSD 512 Go, RTX 4060 8 Go) à 899,99 €
899,99 € 1099 € -18%
@Cdiscount
Alimentation modulaire MSI MAG 850GL 850W (PCIe 5.0, 12V2x6) à 104,90 €
104,90 € 129,99 € -19%
@RueDuCommerce
Lot de 2 ampoules TP-Link Tapo L510E blanche E27 à 12,90 €
12,90 € 21 € -39%
@Amazon

Sujets relatifs
Comparer 2 listes
Creation d' une boucle macro dans fichier EXCEL pour impression
Macro pour word 2013
Macro pour raccourci impression
Macro pour ouverture d'un fichier Excel
Macro pour créer un Gencode sur Excel - EAN 18
Publipostage pour des listes de groupes
macro pour trier un tableau et effacer les lignes sans saisie
Variable exterieure pour un macro
Macro pour faciliter ma comptabilité
Plus de sujets relatifs à macro pour comparer 2 listes
 > Tous les forums > Forum Bureautique