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

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

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.

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)

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

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

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 !

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.

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.

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.

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 ?

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.

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.

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...

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...

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.

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.

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

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.

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

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.

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.

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.

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.

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 ?

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.

Rebonjour,

OK je n'avais pas enregistré extractionbase2 !! Excuse-moi.

Donc j'ai relu toutes tes explications et je reprends tout à zéro :

1) je constitue la liste de réf. J'obtiens bien 2240 ref.

2) MAIS au moment où je clique sur "Liste de ref terminée" j'ai : "Erreur d'exécution 1004. L'accès par programme au projet Visual Basic n'est pas fiable".

3) Je clique donc sur Debogage pour te dire d'où ça vient :

Dans Sub Nettoyer()

la ligne en jaune est : "With this Work.book.VBProject"

4) Je ressors du programme macro. Le bouton Lancer l'extraction ne fonctionne pas.

5) Je ferme le fichier extractionbase2 en enregistrant cette fois. Je l'ouvre à nouveau et là le bouton Lancer l'extraction fonctionne : j'ai demandé la semaine 19. Pas de nouvelles réf. évidemment puisque j'ai tjrs la base originale qui se terminait le 18/4.

Donc à part cette erreur, cela a l'air de fonctionner. Par contre, j'ai une question : Quand on est en semaine 22 par exemple et qu'on a été absente la semaine 21, si j'ai bien compris, on ne pourra pas extraire la semaine 21 dans la feuille Semaine ? Il faudra aller consulter la semaine 21 dans la liste de références avec les dates en face ? Ou est-ce qu'on pourra le faire si on ne l'a encore pas fait ?

Merci pour ta réponse et ta patience.

1) L'erreur 1004 : est-ce que cette case est cochée dans les paramètres du centre de gestion de la confidentialité ?

Accès par Gestion > Centre de gestion de la confidentialité > Paramètres...

Si l'erreur s'est produite sur cette commande, le nettoyage a été fait, sauf la suppression du Module2. Tu peux fort bien le supprimer manuellement.

2) Le fichier est opérationnel pour extraction de la semaine suivante à réception d'une nouvelle base...

3) Si l'on n'a aucune raison de revenir en arrière, l'indication de la dernière semaine traitée fournie une indication pour savoir si l'on n'en a qu'une à traiter où plus.

4) Lorsque j'ai dit qu'il était souchaitable de les traiter dans l'ordre chronologique, c'est pour être sûr que la référence inscrite dans la liste porte la première date (la plus ancienne) à laquelle elle a été détectée. Mais cela n'empêche pas de fonctionner si on traite dans le désordre. Une fois une référence listée, elle n'est plus rajoutée une nouvelle fois, la liste est sans doublon.

On a de toute façon intérêt de traiter dans l'ordre chronologique pour être sûr de ne pas en omettre. Si toutefois, il y a une possibilité importante de ne pas traiter dans l'ordre, si l'on veut garantir que la date de la liste est la plus ancienne, je peux inclure un module de vérification pour rectifier ce point le cas échéant.

Exemple : on traite la semaine 19 et on a une référence nouvelle au 5 mai, elle est donc listée avec la date du 5 mai. Si on traite ensuite la semaine 18 (qu'on n'avait pas traitée, et qu'on retrouve la même référence au 28 avril. En l'état actuel elle sera éliminée puisque présente dans la liste, et reste dans la liste avec la date du 5 mai. On peut ajouter une vérification sur la date pour que dans un tel cas, la référence ne soit pas doublonnée dans liste mais que la date en soit rectifiée au 28 avril (antérieure au 5 mai). A voir...

Bonsoir,

Effectivement, cette case n'était pas cochée chez moi. Par contre, je pensais qu'il fallait cocher en haut "Activer toutes les macros..." à partir du moment où on se servait d'un fichier en .xlsm !

Donc j'ai recoché Désactiver toutes les macros avec notification.

J'ai réessayé. Plus de problème.

A propos de ton exemple semaine 19 et 18, 5/5 et 28/4, je ne sais pas si cela a de l'importance ou pas pour mon utilisatrice. Je verrai avec elle.

A propos de ton exemple semaine 19 et 18, 5/5 et 28/4, je ne sais pas si cela a de l'importance ou pas pour mon utilisatrice. Je verrai avec elle.

OK !

bonjour,

Je viens de penser à qq chose d'important : en fait, le fichier de base change de nom chaque jour : "base150504" par exemple pour le 4 mai et "base150505" pour le 5 mai. Je ne sais plus si je t'avais précisé cela ?

Avec la 2e version, je viens de faire des essais et là : aie, aie !

La liste était constituée : ok mais quand j'ai changé le nom du fichier et que j'ai redemandé une semaine antérieure, il m'a sorti + de 400 nouveautés alors que dans cette semaine là (la 16), avec la version 1, il n'y avait effectivement que 2 nouveautés !

Est-ce moi qui ai mal agi ? Merci.

Le nom du fichier base n'a aucune importance, il faut simplement que ce fichier soit ouvert et que son nom soit indiqué (en E3) sur le fichier d'extraction.

Ceci dit ta mention que le nom change "tous les jours" n'est pas cohérent avec une réception hebdomadaire !

Chaque fichier base est supposé avoir le même contenu que le précédent pour les dates antérieures communes, c'était le point de départ de la question. Il faut confirmer que c'est bien le cas !

Une fois la liste constituée, on n'y revient plus, c'est fait une fois pour toutes, ton fichier d'extraction comporte donc 3 feuilles, dont une feuille nommée 'Refer' qui contient la liste des références existantes.

Si donc à ce moment tu modifies le nom du fichier base, dans la mesure où il ne contient aucune semaine nouvelle, tu dois avoir invariablement pour toute semaine antérieure : "Extraction réalisée : aucune référence nouvelle." Et pour une semaine postérieure (qui ne figure pas dans le fichier) : "Extraction réalisée : aucune référence trouvée."

D'autre part, s'il te sort 400 nouveautés ou plus, cela signifie que l'épuration consécutive à l'extraction n'a pas été faite ! J'ai vérifié le code du fichier que tu dois utiliser après constitution de la liste initiale, je n'y vois pas de problème.

Il faudrait donc me faire passer le fichier sur lequel tu as cette erreur (dans l'état où il est) que je puisse voir de quoi il retourne.

Bonsoir,

Tu as parfaitement raison.

En fait, la dame reçoit tous les jours une nouvelle base actualisée (et nommée avec la date du jour) mais elle souhaite faire sa recherche de nouvelles références une fois par semaine. C'est pour cela qu'elle devra effectivement noter le nouveau nom du fichier ouvert en E3.

Ce soir, j'ai refait des essais et je n'ai eu aucun problème. Tout a bien fonctionné. Je ne sais pas ce qu'il s'est passé la nuit dernière. Il était 4 h du mat. Je n'avais pas les idées claires, sûrement. Mais cette histoire de nom de fichier qui changeait m’empêchait de dormir.

Je vais mieux dormir cette nuit puisque "Le nom du fichier base n'a aucune importance, il faut simplement que ce fichier soit ouvert et que son nom soit indiqué (en E3) sur le fichier d'extraction".

Ouf, merci et bonne nuit.

Bonjour Ferrand,

Je suis donc allée ce matin installer la macro version2 chez mon amie. Je préférais le faire avec elle. Tout a bien fonctionné avec sa base datée du 15 mai 2015 ! Merci.

Par contre, j'ai une question à te poser : Dans la feuille Refer sont apparues 2 nouveautés du 12 mai, donc de la semaine 20.

Mais elle doit attendre le lundi 18 mai pour pouvoir lancer l'extraction de la semaine 20 complète, c'est bien cela, n'est-ce pas ?

Lundi, elle va donc lancer l'extraction de la semaine 20. En supposant qu'il y ait encore 1 nouveauté le 15/05 après-midi, est-ce que ces 2 nouveautés du 12/5 (+ 1 du 15/05), donc est-ce que ces 3 nouveautés apparaîtront bien dans la feuille Semaine pour la semaine 20 ? ou seulement celle du 15/5 puisque les 2 du 12/5 sont déjà listées dans la feuille Refer ?

J'en saurai plus lundi soir car elle va essayer toute seule lundi matin et elle me tiendra au courant.

Je te dirai si tout va bien. J'espère ! Elle aura peut-être au fil du temps d'autres questions ou besoins. Je t'en parlerai alors !

MERCI encore et bon w-end.

Bonjour,

En mettant en route le fichier version 2, tu as donc réalisé la constitution initiale de la liste de références, à la date du fichier base utilisé.

Si le nettoyage a été fait, les éléments utilisés pour cette opération (qui n'est plus à refaire une fois faite) ont été supprimés (indications dans la zone en jaune, boutons pour l'opération et Module2). Ton fichier est prêt pour l'utilisation opérationnelle hebdomadaire prévue.

Si dans cette liste initiale, tu as déjà 2 références nouvelles au 12/05 (semaine non encore traitée) :

- celles-ci n'apparaîtront plus lors du traitement de la semaine, car déjà extraites et ajoutées à la liste.

- Si cela ne la gêne pas, elle peut laisser ainsi...

- Sinon : effacer les 2 reférences qui doivent figurer en fin de liste (triée par dates) : elles seront de nouveau identifiées comme nouvelles lors du traitement de la semaine 20.

Questions restant en suspens :

- Vérification complémentaire sur les dates lors de l'extraction (pour garantir date la plus ancienne d'apparition, dans le cas où l'on procèderait dans le désordre, et si cela peut avoir une importance).

- Actuellement l'inscription dans la liste (Refer) est automatique, mais peut-être serait-il mieux que cette inscription soit déclenchée par l'utilisateur après vérification de l'extraction hebdomadaire ?

- Il peut y en avoir d'autres, liées aux méthodes de travail...

Bonsoir, Que des bonnes nouvelles : tout s'est bien passé lundi.

Elle fera donc chaque lundi l'extraction de la semaine précédente et cela devrait rouler !

Merci. Bonne soirée.

A + Mary

Bonne continuation.