Sommes intelligentes sur plusieurs classeurs

Le 18/07/2012 à 15:02

Bonjour,

Je dois réaliser un tableau récapitulatif de plusieurs tableaux.

Il existe 3 services: Réception - Préparation - Expédition

Dans chacun de ces services, il y a 4 types de produits COSMETIQUE, ULTRA FRAIS, FRUIT&LEGUME et SEC. Il y a un tableau pour chaque service et pour chaque type produit . Ces derniers donnent le nombre de palettes reçues, expédiées et préparés (ça dépend du services). Il est remplie tous les jours et cela, toute l'année.

Je dois faire un tableau qui donne le nombre de palettes par type de produit reçues (respectivement préparées et expédiées) par semaine, par mois et par an...Donc par exemple pour calculer le nombre de palettes SEC reçues pour la semaine 23 je dois sommer 7 cases du classeur Réception SEC + 7 cases du classeur Réception ULTRA FRAIS + 7 cases du classeur Réception F&L (je l'ai fait à la main ) parce qu’exceptionnellement des palettes SEC peuvent arriver en Réception ULTRA FRAIS ou F&L etc...(c'est un bordel ) . Etant donné le nombre de service et le nombre de types de produit et les 52 semaines dans une année , je peux pas continuer à la main.

Y-aurait-il une solution qui soit plus efficace parce que la je suis en panne..?

Merci d'avance,

H.Rap

Le 18/07/2012 à 15:18

Salut,

Oui il existe de méthodes pour simplifier la vieau travail

Par conte si tu peux envoyer des exemples de fichiers afin de mieux affiner notre réponse..

Ton explication même s'il est précise peux avoir plusieurs interprétations.

Question :

1 classeur Excel (fichier) par service ou 1 onglet par service ?

Comment sont agencées les feuille ?

Sinon, en gros, tu peux avoir un fichier excel qui va en ouvrir d'autres afin de se mettre a jour (pas de rproblème pour cela), par contre cela dépend comment sont organisés tes autres fichiers (toujours les mêmes titres, colonnes etc....)

Que souhaite tu comme résultat ?

Le mieux et je pense que tu le comprend est de nous envoyer un set de fichier avec le resultat que tu souhaites obtenir. (anonymise le fichier si c'est confidentiel)

damien

Le 18/07/2012 à 15:34

Merci d'être aussi réactif.

J'ai oublié d'envoyer ça effectivement.

Il y a un fichier Excel (classeur) par type de service (Reception SEC, Reception ULF...) les colonnes et titres sont toujours les mêmes effectivement. C'est beaucoup plus parlant sur le fichier TB Réception SEC.

Merci

H.Rap

Le 18/07/2012 à 15:54

Re,

Dans le fichier Recap tu as équipes de nuit ? Sur quel critère te bases tu pour remplir ce champ ?

Damien

Le 18/07/2012 à 16:54

Re,

En fait je vais sommer toutes les palettes, par exemple la, SEC. Equipe de nuit, Réception SEC et ULF&FL font des réceptions de palettes SEC. Donc pour les remplir je fais une somme, assez horrible soit dit en passant, des TB Réception SEC, ULF&FL Equipe de nuit sur une semaine des palettes SEC reçues.

La somme pour uniquement réception SEC est : =SUM('[TB Réception SEC 2011 Juin à Décembre.xls]TB recep SEC'!C10:I10;'[TB Réception SEC 2011 Juin à Décembre.xls]TB recep SEC'!C15:I15;'[TB Réception SEC 2011 Juin à Décembre.xls]TB recep SEC'!C20:I20)

parce qu'il y a 3 où il peut y avoir de la réception SEC.

H.Rap

Le 19/07/2012 à 14:00

Salut,

Je n'ai pas encore tout compris au problème MAIS,

Fais un test avec le code fournie :

Si tu prends le fichier joint

Tu l'ouvres

Tu fais ALT + F11 pour ouvrir editeur VBA

Tu verras une macro indiqué test dans le Module1

Dans cette macro il faut modifier :

''''''''''''''''''''''''''''''''''''''''''''''' A MODIFIER '''''''''''''''''''''
fichierexcel_Reception = "SEC.xls"
repertoirefichierreception = "C:\Documents and Settings\7001107\Bureau\EXCEL\RAP\"
'''''''''''''''''''''''''''''''''''''''''''''' A MODIFIER ''''''''''''''''''''''

fichierexcel_reception est le nom du fichier excel contenant les données source brut (attention au espace et caractère spéciaux)

repertoirefichierreception est le repertoire contenant le fichier attention au \ a la fin (ne pas oublier)

Positionne toi ensuite sur la feuille Aout, puis lance la macro test (outils -> Macro -> Macro Lecture -> test -> executer)

Automatiquement les formules vons se construire pour le calcul du nb de palette en fonction du numero de semaine.

Cela fonctionnera aussi pour Septembre etc... si tu as la même organisation que la feuille Aout.

Pour le reste c'est possible mais il faut m'envoyer les formules correspondantes..... je ne trouve pas.

Damien

19re-cap.xlsm (30.02 Ko)
Le 19/07/2012 à 15:49

Salut,

Merci bcp d'avoir passé du temps sur ce pb, le calcul est bon!!

Cependant ça calcul uniquement pour le fichier Excel TB Reception, donc la ligne 5 (alors que la, c'est la ligne 4). La ligne 4 serait la somme des 3 en dessous, si tu vois ce que j'veux dire... J'ai 2 questions :

Cmt je dois modifier le code pour qu'il me calcul aussi (sur le même fichier) le nombre de palettes ULF et le nombre de palettes F&L? En gros remplir les lignes 9 et 13.

Je dois effectuer ces calcul avec les tableaux TB Equipe de Nuit et TB Reception F&L et ULF... En gros ça sera exactement pareil pour remplir les lignes suivantes..

Es-ce que je peux modifier le code pour qu'il puisse ouvrir d'autres fichiers en plus (2 en plus) afin d'éffectuer la même chose mais à partir de fichiers différents ou c'est mieux de créer 2 autre MACRO qui fera la même chose mais avec ces fichiers excel supplémentaires?

Si je ne suis pas assez clair hésites pas à me le dire.

Encore merci,

H.Rap

Le 19/07/2012 à 16:26

Re,

Je viens de corriger le fichier en ajoutant les sommes comme tu me l'as décrit.

Si on regarde le code :

        Set ligne = Intersect(Rows("10, 10"), calcul)
        tempstr = "=SUM('[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ","

Ici je construit dans la variable tempstr le début de la formule.

l'instruction Set ligne = intersect(Row("10,10"),calcul) est en fait un calcul qui attribue a la plage ligne les adresses a l'intersection de la ligne 10 et des colonnes correspondantes a la semaine en cours de traitement.

        Set ligne = Intersect(Rows("15,15"), calcul)
        tempstr = tempstr & "'[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ","

Ici je rajoute a tempstr le reste de la formule mais cette fois je regarde la ligne 15

        Set ligne = Intersect(Rows("20,20"), calcul)
        tempstr = tempstr & "'[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ")"

Et on recommence avec la ligne 20 puis on ferme la parenthèse.

recapwks.Range("D1").Offset(2, i).Formula = tempstr

J'insere la formule dans le code.

Donc en te servant de la même syntaxe mais en changeant fichierexcel_reception par fichierexcel_nuit (que tu auras au préalables initialisé avec le nom et/ou chemin.) et en modifiant les lignes set ligne= intersect.......

cela devrait fonctionner.

Si tu n'y arrives pas envoi moi les autres fichiers.

Damien

Bigdams a écrit :

Re,

Je viens de corriger le fichier en ajoutant les sommes comme tu me l'as décrit.

Si on regarde le code :

        Set ligne = Intersect(Rows("10, 10"), calcul)
        tempstr = "=SUM('[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ","

Ici je construit dans la variable tempstr le début de la formule.

l'instruction Set ligne = intersect(Row("10,10"),calcul) est en fait un calcul qui attribue a la plage ligne les adresses a l'intersection de la ligne 10 et des colonnes correspondantes a la semaine en cours de traitement.

        Set ligne = Intersect(Rows("15,15"), calcul)
        tempstr = tempstr & "'[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ","

Ici je rajoute a tempstr le reste de la formule mais cette fois je regarde la ligne 15

        Set ligne = Intersect(Rows("20,20"), calcul)
        tempstr = tempstr & "'[" & fichierexcel_Reception & "]TB recep SEC'!R" & ligne.Row & "C" & ligne.Column & ":R" & ligne.Row & "C" & ligne.Offset(0, 6).Column & ")"

Et on recommence avec la ligne 20 puis on ferme la parenthèse.

recapwks.Range("D1").Offset(2, i).Formula = tempstr

J'insere la formule dans le code.

Donc en te servant de la même syntaxe mais en changeant fichierexcel_reception par fichierexcel_nuit (que tu auras au préalables initialisé avec le nom et/ou chemin.) et en modifiant les lignes set ligne= intersect.......

cela devrait fonctionner.

Si tu n'y arrives pas envoi moi les autres fichiers.

Damien

17re-cap.xlsm (29.78 Ko)
14re-cap.xlsm (24.94 Ko)
Le 20/07/2012 à 11:10

Bonjour,

Merci ça marche bien, j'ai réussi à mettre les calculs sur une autre ligne en changeant les offset;

Cependant j'arrive pas à changer ce qu'il doit sommer dans les petites sommes :

     'Nb de palette SEC déchargées
        tempstr = "=SUM(R2C" & recapwks.Range("D1").Offset(1, i).Column & ",R3C" & recapwks.Range("D1").Offset(1, i).Column & ",R4C" & recapwks.Range("D1").Offset(1, i).Column & ",R5C" & recapwks.Range("D1").Offset(1, i).Column & ")"
        recapwks.Range("D1").Offset(1, i).Formula = tempstr

J'imagine que c'est bien la que la somme est calculé...et la j'ai essayé de lui dire de sommer la ligne 5,6,7,8 à l'image de ce que tu m'avais envoyé mais ça ne marche pas...Le résultat est bien sur la ligne 2 mais il prend tout le temps les ligne 9,10,11 ....

sinon je dois bien créer 3 fois la même macro mais en changeant les noms des fichiers et feuilles etc pour calculer les autres lignes avec les 3 autres fichiers ou je peux les ouvrir sur ce même fichier ?

Si je fais ça, par exemple pour ajouter les données du fichier TB equipe de nuit :

'initialisation
''''''''''''''''''''''''''''''''''''''''''''''' A MODIFIER '''''''''''''''''''''
fichierexcel_Reception = "TB Réception SEC 2011 Juin à Décembre"
fichierexcel_nuit = "TB Equipe de nuit 2011 Juin à Décembre"
repertoirefichierreception = "\\PROFILE\HDIR$\h.rapenomanjato.BIOCOOP\TB Juin à Décembre 2011\"
'''''''''''''''''''''''''''''''''''''''''''''' A MODIFIER ''''''''''''''''''''''
i = 0
Set recap = ActiveWorkbook
Set reception = Workbooks.Open(repertoirefichierreception & fichierexcel_Reception, , ReadOnly)
Set reception = Workbooks.Open(repertoirefichierreception & fichierexcel_nuit, ReadOnky)
Set receptionWks = reception.Worksheets("TB recep SEC")
Set receptionWks2 = reception.Worksheets("TB nuit")
Set recapwks = recap.ActiveSheet 'je pense que la il y aura un pb...'

ça marcherait??

H.Rap

Le 20/07/2012 à 13:49

Bonjour,

Mes réponse :

J'imagine que c'est bien la que la somme est calculé...et la j'ai essayé de lui dire de sommer la ligne 5,6,7,8 à l'image de ce que tu m'avais envoyé mais ça ne marche pas...Le résultat est bien sur la ligne 2 mais il prend tout le temps les ligne 9,10,11 ....

C'est de ma faute.... Si tu regardes bien le code qui suit tu remarqueras que pour le bloc : 'Nb de palettes ULF déchargées tu as le code ci dessous :

        'Nb de palettes ULF déchargées
        tempstr = "=SUM(R9C" & recapwks.Range("D1").Offset(1, i).Column & ",R10C" & recapwks.Range("D1").Offset(1, i).Column & ",R11C" & recapwks.Range("D1").Offset(1, i).Column & ")"
        recapwks.Range("D1").Offset(1, i).Formula = tempstr
        recapwks.Range("D1").Offset(5, i).Formula = tempstr

Le problème c'est la ligne : recapwks.Range("D1").Offset(1, i).Formula = tempstr

Tu écrases l'ancienne formule par celle ci qui ne correspond pas, donc supprime cette ligne et cela ira mieux.....

Sinon, pour l'autre question, je pense que :

  • tu peux ouvrir les autres fichiers dans la même macro,
  • sinon dans une autre. Tu peux même lancer une macro qui lancera les 3 a la suite.....
Pas d'importance au niveau technique.

Mais si tu veux le faire dans la même macro ton code ne fonctionnera pas :

Set reception = Workbooks.Open(repertoirefichierreception & fichierexcel_Reception, , ReadOnly)
Set reception = Workbooks.Open(repertoirefichierreception & fichierexcel_nuit, ReadOnky)

il faut écrire :

Set reception = Workbooks.Open(repertoirefichierreception & fichierexcel_Reception, , ReadOnly)
Set reception_nuit = Workbooks.Open(repertoirefichierreception & fichierexcel_nuit, ReadOnky)

Et ne pas oublier la déclaration en plus, evidemment :

Dim reception As Workbook

dim reception_nuit as Workbook

Reception et reception_nuit sont des objets de type workbook, tu ne peux attribuer qu'un seul fichier par objet.

En esperant avoir répondu à tes questions.

Je pars en congé pour 3 semaines, donc pas de réponses sur le forum pendant ce temps.

++

Damien

Le 20/07/2012 à 15:48

Encore merci, j'pense que j'vais arriver à me dépatouiller tout seul ^^'

bonne vacs

H.Rap

Rechercher des sujets similaires à "sommes intelligentes classeurs"