Ajouter des données dans une BDD à partir d'un TCD

Le 07/10/2015 à 15:29

Bonjour à tous,

je travaille sur excel 2010, beaucoup par formule et très peu par VBA mais la je suis obligé d'y passer.

Je construis actuellement un outil de planification où à partir d'une BDD « DONNEES » (contenant à la fois pour différents CODE ARTICLE différents TYPE de flux, des STOCKS et des VENTES que je viens copier dans cette BDD, et je souhaiterai ajouter des AVANCES), je synthétise ces données (totaux numériques) par un TCD.

Mon TCD se présente donc avec une colonne de STOCK, une colonne de VENTE et une colonne AVANCE. Pour cette colonne AVANCE, je souhaite après avoir sélectionner une cellule du TCD, cliquer sur un bouton de commande AVANCE pour ouvrir un formulaire pour ajouter une valeur à la BDD.

A chaque ajout AVANCE, il faut rajouter donc une ligne dans l’onglet DONNEES. Une macro doit récupérer les informations d’étiquettes de lignes et de colonne et les filtres de rapport (DATE, TYPE, SITE… correspondant à la cellule sélectionnée du TCD) pour les ajouter dans les colonnes correspondantes de l’onglet DONNEES et via le formulaire je saisi une valeur qui vient s’ajouter dans la colonne QUANTITE.

Exemple : si je sélectionne la cellule A11, et que je clique sur le bouton de commande AVANCE et que je saisi 10 dans un formulaire, cela ajoute sur la ligne 16 de l'onglet DONNEES en :

  • colonne B : AVANCE
  • colonne C : 20000
  • colonne E : LB
  • colonne F : 10

En utilisant l'enregristreur et en faisant la formule LIREDONNEESTABCROISDYNAMIQUE, il me renvoie la macro suivante, ça me parait cohérent car je retrouve les infos dont j'ai besoin. mais comment les ranger dans DONNEES

Sheets("DONNEES").Select

Range("N13").Select

ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""AVANCE"",TCD!R7C1,""N° ARTICLE"",20000,""SITE"",""LB"",""JOUR"",""lundi"")"

Cela vous paraît-il faisable car je suis trop limité en VBA pour trouver le code et je n’ai rien trouvé sur les forums.

en pièce jointe un fichier très allégé de ce que je fais, autant en terme de colonnes (environ 30 au total) que de lignes (200000 au total).

Merci beaucoup pour votre aide

21testvbatcd.xlsx (14.13 Ko)
Le 07/10/2015 à 16:42

bonjour

on dirait une gestion de stocks (ou autre système d'entrées/sorties/état)

si c'est le cas, je me demande pourquoi tu as ce besoin jamais vu (et j'en vois des gestions de stocks, sous Excel ou SGBD)

est-ce que tu cherches un système de calcul de réapprovisionnement plus ou moins automatique ?

Le 07/10/2015 à 17:01

Bonjour jmd et merci d'avoir répondu

effectivement cela peut s'apparenter à une gestion de stock.

en fait je dispose d'un autre fichier de prévision. dans ce fichier, j'aurai des champs calculés PROD JOUR =(NB VENTE - NB STOCK), j'analyse la prévision et dans la colonne AVANCE, je planifie un volume à préparer pour J+1, et j'aurai un autre champ calculé PROD TOTAL = PROD JOUR + AVANCE.

J'aimerai donc utiliser cette technique pour aller très vite dans les ajouts de données AVANCE car mon fichier actuel de planification mets déjà 10min de traitement et je ne suis qu'à 30% de ce que je veux faire.

Du coup, si vous me rejoignez dans mon idée, est ce que cela vous paraît faisable ?

merci

Le 07/10/2015 à 17:15

re

pour un stock, il n'y a que 4 infos à saisir :

  • date (avec ou sans l'heure)
  • réf du produit
  • quantité entrée (ce peut être un achat ou une production ou une rectification d'écart sur stock)
  • quantité sortie (ce petu être une vente ou une consommation interne ou une rectification d'écart sur stock)
  • (et à la rigueur le nom du fournisseur ou de l'atelier ou de la machine concerné)
et une colonne = qté entrée - qté sortie (pour se faciliter les TCD)

avec ça on fait tout

si tu veux entrer une prévision, tu ajoutes une ligne avec une date future. Et le jour venu, s'il y a besoin, tu corriges la valeur

Avec un tableau aussi simple, on fait un TCD et une courbe qui montre l'évolution du stock au fil du temps avec une vision du passé et de l'avenir (par simple ajout d'un cumul dans le TCD)

à la vue de la courbe, tu saisis une ligne de plus dans tes données pour indiquer que tu as besoin d'une entrée de 100 en date du 15/10/2015

essaye

Le 08/10/2015 à 15:22

merci pour ton explication.

je me suis mal exprimé, ce n'est pas une gestion de stock mais un outil de planification, sur lequel plusieurs fois / jour je vais varier la production AVANCE en fonction de mon RESTE (VENTE-STOCK). A savoir que je fonctionne en flux poussé, donc je n'ai pas forcément de VENTE pour consommer des STOCK.

en fait dans ma première version du fichier, je gérais une onglet supplémentaire pour me permettre d'ajouter une production sur prévision : AVANCE. Cela fonctionnait bien mais c'est trop prenant en temps et source d'erreurs.

comme il y a 4 sites, 3 ateliers, 5 types de clients et au minimum 100 articles à gérer cela démultiplie les infos à saisir d'où la solution que je propose mais dont je n'ai pas le code.

Donc je suis passé à une formulaire avec des combobox, ça fonctionne bien également mais je me dis que ça irait bien plus vite en cliquant sur la cellule de la colonne AVANCE pour ajouter directement une valeur en récupérant les étiquettes qui correspondent aux variables à saisir.

le fait d'ajouter une ligne dans l'onglet DONNEES à chaque fois n'est peut-être pas la bonne solution, peut-être vaut il mieux que je créé toute ces lignes (pour les 4 sites, 3 ateliers, 5 types de clients et 100 articles) dans l'onglet DONNEES en mettant une valeur 0 ou 1 et fonctionner par une modification de cette valeur

Qu'en penses-tu ?

je pense qu'il faut que j'utilise la fonction pivotItem mais je ne vois pas comment l'intégrer.

Merci

Le 08/10/2015 à 17:20

re

j'avoue ne pas comprendre le principe de

je vais varier la production AVANCE en fonction de mon RESTE (VENTE-STOCK). A savoir que je fonctionne en flux poussé, donc je n'ai pas forcément de VENTE pour consommer des STOCK

Le 08/10/2015 à 18:05

re

je travaille en agroalimentaire. tous les jours, un nombre fixe d'unités (des carcasses d'animaux) arrive dans mon atelier. Ces unités sont décomposés en sous unités (exemple dans le poulet, des cuisses, des ailes, des filets), c'est une nomenclature inversée. Pour chaque sous unité, je génère donc un stock : 1000 unités, 1000 unités B... Les ventes pour le produit A sera par exemple de 400 et pour le produit B de 600. Il faut donc que j'utilise les unités A et B excédentes soit pour les ventes du lendemain, soit pour en faire des produits C ou D (on peut par exemple faire des émincés, des escalopes). C'est ce principe que j'appelle AVANCE, une production sans commande ou VENTE (en l'occurence il s'agit souvent de produits congelés).

j'espère avoir été plus explicite, sinon c'est vraiment que je n'arrive pas à bien m'exprimer...

Le 21/10/2015 à 18:39

Bonjour,

finalement j'avais sans doute mal formulé ma demande, il fallait que je passe par une somme prod dans la macro. Du coup à défaut d'ajouter, j'ai rentrer toutes les lignes dans la base avec une valeur 0 et je modifie. ça fonctionne bien donc je suis satisfait, le temps de traitement et de mise à jour du TCD sur 50000 lignes est instantané.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    'si pas d'intersection, fin de procédure
    If Intersect(Target, Sheets("D1").PivotTables(1).DataBodyRange) Is Nothing Then Exit Sub
    ' si pas de valeur, pas de procédure
    If Target.Value = "" Then Exit Sub

    'variables
    typeclient = Cells(Target.Row, 1)
    libellebase = Cells(Target.Row, 2)
    jour = Cells(5, Target.Column)
    site = Cells(6, Target.Column)
    typeflux = Cells(7, Target.Column)

    With Sheets("DONNEES")
        valeur = InputBox("Entrez la nouvelle valeur")
        If Not IsNumeric(valeur) Then Exit Sub
        'on efface le contenu de la cellule de la ligne 1 colonne 70 utilisée pour le calcul
        .Cells(1, 70).ClearContents
        'somme prod nous donne le n° de la ligne dans laquelle se trouve la valeur à modifier dans DONNEES
        .Cells(1, 70).Formula = "=sumproduct((S2:S50000=""" & typeclient & """)*(O2:O50000=""" & libellebase & """)*(F2:F50000=""" & jour & """)*(D2:D50000=""" & site & """)*(A2:A50000=""" & typeflux & """)*row(A2:A50000))"
        'la variable lig est égal au n° de ligne
        lig = .Cells(1, 70)

        'IMPORTANT : pour que la SUMPRODUCT fonctionne, il ne faut pas d'#VALEUR dans DONNEES
        'il faut également que la colonne E à calculer ne comporte bien que des données numériques

        .Cells(lig, 5) = CDbl(valeur)
    End With
    ThisWorkbook.RefreshAll
End Sub

A bientôt et merci de m'avoir répondu

Le 21/10/2015 à 19:08

re

merci de ton message

j'avoue ne pas bien suivre la logique de gestion des quantités dans ton entreprise. Tu t'es inspiré d'une GPAO (que je ne connais pas) ?

mais bon, si tu as ta solution c'est l'essentiel

Excel fait des TCD sur des millions de lignes

Rechercher des sujets similaires à "ajouter donnees bdd partir tcd"