Exploiter données d'un autre classeur

Bonjour à tous,

Je travaille dans une exploitation qui produit et vend des légumes. Chaque mois, le responsable édite sous Excel une facture pour chaque magasin auquel nous vendons notre production (voir PJ n°1). Il y a un classeur par mois, avec plusieurs feuilles ; sachant qu'un même légume peut figurer plusieurs fois par feuille ou par classeur.

En fin d'année, le responsable éditait un bilan pour récapituler, légume par légume, les quantités vendues chaque mois et les sommes associées - A LA MAIN.

En bon chevalier blanc, je lui ai proposé d'automatiser ce bilan.

Je n'y connais pas grand chose à Excel. J'ai donc référencé tous les légumes que nous produisons, et commencé à générer un tableau récapitulatif, avec une formule SOMME.SI pointant vers le classeur concerné (voir PJ n°2).

Ca fonctionne bien, à un détail près : si je n'ouvre que le bilan, sans ouvrir la facture correspondante, Excel ne récupère pas les données et m'envoie une erreur #VALEUR. Dès que j'ouvre le classeur (de janvier dans mon exemple), la formule s'actualise et le résultat s'affiche sans souci.

Y a-t-il un moyen de forcer Excel à aller chercher les données de chaque facture au moment de l'ouverture du bilan ? Ou faudra-t-il à chaque fois ouvrir manuellement les douze classeurs ? Non pas que ce soit grave, mais quitte à automatiser le bousin, autant aller jusqu'au bout.

Merci d'avance aux éventuels contributeurs !

Salut,

Je te propose une solution. Tu places le fichier ‘Bilan 2017_V1’ ci-joint dans un dossier avec uniquement les fichiers source. La macro placée derrière le bouton ‘Go’ commence par effacer les données en place sur la plage B3:Yxxxxxxx puis passe en revue toutes les feuilles de tous les fichiers dans ce dossier pour en relever le nombre de fruits et légumes inscrits pour en faire le total.

Tes fichiers source doivent être fermés au lancement de la macro. Si tu en as 50 à visiter, suivant la rapidité de ta machine, ma macro peut durer quelques secondes, voire minutes. Fait tes premiers essais avec 3 fichiers source, puis 10, puis 100, etc.

Je suis parti sur la base de tes modèles et ma macro fonctionne pour autant que tes fichiers-source indiquent le mois dans les deux premiers chiffres de leur nom.

Je te fournis tous mes fichiers test afin que tu puisses déjà voir comment tout ça fonctionne.

Ce que l’on pourrait encore prévoir :

a) que tous les fichiers à visiter ne soient pas dans le même dossier que le dossier de base ‘Bilan’,

b) que la macro indique un article des fichiers source qu’elle n’aurait pas trouvé dans la liste du fichier de base,

c) que sais-je de plus ?

Il faut cependant toujours voir si le travail manuel (tel que de déplacer les fichiers sources dans le même dossier que le fichier de base) n’est pas plus simple que de modifier le code (par exemple afin d’aller lire les fichiers source à l’endroit où ils se trouvent).

Mais commençons par le commencement : est-ce que ce départ te convient ?

Salut Yvouille,

Merci infiniment pour ta contribution ! Pour te dire à quel point je suis novice d'Excel : c'est la première fois que je fais fonctionner une macro.

Je n'ai eu le temps que d'un rapide test, mais ta solution semble fonctionner parfaitement et correspondre exactement à mes besoins. Les améliorations que tu proposes ne seront vraisemblablement pas nécessaires. Je vais voir ça avec mon responsable, faire des tests plus en profondeur, et voir si des aménagements sont encore à faire ; mais je te remercie déjà énormément !

C'est exactement ce que je voulais.

Bonsoir.

Tout d'abord pourquoi une macro ?

Votre problème c'est la liaison qui ne se fait pas correctement avec un classeur fermé. La, il suffit de remplacer la fonction =somme.si

par =sommeprod et le tour est joué.

Mais tout d'abord essayer de nommer vos plages. C'est plus commode pour travailler. Disons :

• -- SavP1 pour la colonne des saveurs de l'onglet SaveursP1
• -- QteP1 pour la colonne des quantités de l'onglet SaveurP1

--- Idem pour les autres onglets.

Enfin remplacer votre formule :

=SOMME.SI(SavP1;"artichaud";QteP1)+SOMME.SI(SavP2;"artichaud";QteP2)+SOMME.SI(SavP3;"artichaud";QteP3)+SOMME.SI(SavP4;"artichaud";QteP4)

par:

=SOMMEPROD((SavP1=A3)*(QteP1))+SOMMEPROD((SavP2=A3)*(QteP2))+SOMMEPROD((SavP3=A3)*(QteP3))+SOMMEPROD((SavP4=A3)*(QteP4))

et votre problème est résolu.

Cordialement.

Salut Moncef,

AYARI Moncef a écrit :

Tout d'abord pourquoi une macro ?

Mais je pourrais également te dire :

Ne serait-ce que parce que ça permet d’ajouter ou supprimer autant de feuilles de clients que tu veux dans les fichiers mensuels sans ne rien devoir modifier à la macro ou

ne serait-ce que parce ça n’oblige pas pratiquement à nommer les plages ou

ne serait-ce que parce que ça permet de contrôler si un article n’a pas été saisi correctement dans les factures et serait ainsi oublié par les formules ou

pour toutes sortes d’autres raisons à découvrir à l’usage.

Cordialement.

Bonsoir Yvouille.

La demande était : Ca fonctionne bien, à un détail près : si je n'ouvre que le bilan, sans ouvrir la facture correspondante, Excel ne récupère pas les données et m'envoie une erreur #VALEUR. Dès que j'ouvre le classeur (de janvier dans mon exemple), la formule s'actualise et le résultat s'affiche sans souci.

Y a-t-il un moyen de forcer Excel à aller chercher les données de chaque facture au moment de l'ouverture du bilan ? Ou faudra-t-il à chaque fois ouvrir manuellement les douze classeurs ? Non pas que ce soit grave, mais quitte à automatiser le bousin, autant aller jusqu'au bout.

donc j'ai répondu à la question sans porter de jugement de valeur sur le classeur et son organisation et ....

Maintenant que j'ai regardé votre proposition, je constate que çà améliore énormément le projet de ThirdStone et c'est tant mieux pour lui.

C'est vrai qu'utiliser les macros c'est intéressant mais bien exploiter les possibilités d'excel l'est aussi. L'important c'est de savoir quand il est préférable d'user de macros pour effectuer des travaux possibles autrement.

Très cordialement.

AYARI Moncef a écrit :

L'important c'est de savoir quand il est préférable d'user de macros pour effectuer des travaux possibles autrement.

Je propose que nous arrêtions ici cet échange qui ne concerne pas thirdstone84. J’ai pris en charge la demande de ce membre, il y a énormément de fils restés sans réponse ; à toi de voir où tu serais le plus utile.

Bonne continuation à toi.