Utilisation de données d'une BDD

Bonjour,

Je travaille actuellement sur une macro pour automatiser tout un processus.

J'ai développé 80% de la macro (Extraction des données, mise en page, outils annexes (mailing, gestion administrateur, etc…), mais je bloque sur la création d'une macro qui devra surement utiliser un Tablo() en VBA, mais je ne sais pas encore l'utiliser...

Il n'est normalement pas nécessaire pour moi de partager tout le fichier pour expliquer le problème (surtout qu'il est compliqué de retirer les informations confidentielles), mais si besoin, il est possible de me contacter par MP pour en discuter.

J’ai cependant créé un fichier simplifié (avec les données nécessaires à la compréhension) que vous trouverez ci-joint.

Pour les personnes ne souhaitant pas ouvrir un fichier Excel d'internet :

Spoiler

Description de la macro : Le but de la macro est de comparer 2 feuilles de même format et sur le même nombre de colonne. Seul le nombre de ligne peut changer entre les feuilles. Les feuilles correspondent à des extractions qui donnent des informations sur nos produits. Les 2 extractions disponibles sont J et J-1 (et à chaque fois que le fichier est actualisé, alors une nouvelle feuille J est créée. L’ancienne feuille J devient J-1 et l’ancienne J-1 quant à elle est supprimée et ne sera plus utilisée.)

Les informations à récupérer sont à afficher dans la feuille Recap. Je vais essayer de décrire chacune d’entre-elles. Les entêtes rouges des feuilles J et J-1 correspondent aux noms des colonnes utiles pour la macro.

• IN Recap!(A:E) : Permet de surveiller les produits dont le St. (colonne K) change de 2 => 3 ou 5 en J-1 et J
  Les informations à récupérer et afficher à partir de la ligne 3 de la feuille récap sont : Date (MyDate=Date()), EAN, SAP, Nom

• OUT Recap!(G:K) : Permet de surveiller les produits qui sortent de notre liste (qui sont dans J-1, mais pas dans J)

• Changement PRIX Recap!(M:Q) : Permet d’afficher les produits dont le prix change (Colonne G) (Changement du prix entre J-1 et J)

• Changement SAP Recap!(S:V) : Permet d’afficher les produits dont le code SAP change (colonne E)
  (Idem mais SAP)

• ERREUR Recap!(X:AA) : Permet de répertorier toutes les erreurs du fichier (Cellules qui affichent #N/A ou TBC)
  Afficher toutes les erreurs de J (Pas de comparaison entre J-1 et J nécessaire)

Je pense que la macro sera presque identique pour les différentes colonnes et qu'il ne faudra que l'adapter. Dans ce cas, je serais capable de le faire et j'ai juste besoin d'aide pour faire la première.

En espérant être assez clair, je reste à disposition si besoin,

EDIT : Je souhaite utiliser Tablo() pour réduire le temps de calcul d'Excel (la macro générale est déjà assez longue) en évitant de C/C chaque ligne mais de stocker les donner dans un tableau et inserer les données à la fin de la macro

Merci

Bonjour,

C'est un problème récurrent :

Malheureusement dans ton fichier joint il manque une chose : Quel est l'élément de base de la comparaison ? Il faut au moins une colonne de références qui permette de dire sur quelle référence on se base pour comparer deux lignes ? Sans doute EAN ?

Pour t'en sortir ne complique pas trop les choses avec des dispositions compliquées... (dans la recap)

Tu pars de 2 tableaux et tu veux arriver à un tableau identique qui te met en évidence les différences.

Pour la facilité de l'explication on va rebaptiser tes Array

J-1 sera "ArrS" (Array Source)

J sera "ArrC" (Array Cible)

Tu vas donc charger ton ArrS avec

Dim ArrS

ArrS = Worksheets("J-1").Range("A2:N26").Value

et charger ton ArrC avec

ArrC = Worksheets("J").Range("A2:P26").Value

ArrC comporte 2 colonnes de plus que nécessaire pour permettre de noter les différences....

Et maintenant on va comparer nos Tablo

Pour chaque ligne de ArrC, (de 1 à Ubound(ArrC) on va parcourir ArrS de la première à la dernière ligne sur la colonne de référence,

Si ça ne check pas, c'est que la référence est nouvelle dans ce cas on note dans

ArrC (NoIndiceEnCours, 15) = "I" '(pour nouveau (ou IN)

Si ça check, bingo ! on va tester successivement tes conditions et on notera dans la colonne 15 de ton Array I, P ou S pour un changement Prix ou SAP et dans la colonne 16 la valeur ancienne...

Pour la simplicité de la démo, on va admettre qu'il ne peut y avoir qu'un seul changement par référence Si les changements peuvent affecter plusieurs critères c'est plus compliqué, mais on y arrive quand même... (...)

Ayant fait TOUSSA on à parcouru toutes les lignes de ArrC et noté toutes les différences.

YAPUKA coller ArrC dans ta feuille cible

Worksheets("Synthèse").Range("A2:P26")= ArrC

Ayant fait cela tu auras remarqué qu'on n'a pas checké les produits disparus. (puisqu'ils n'existent pas dans ArrC)

Il faudra donc faire le parcours inverse :

Pour chaque ligne de ArrS on va parcourir ArrC si on ne trouve pas la référence c'est qu'elle a disparu

Dans ce cas soit on Redim ArrC pour ajouter la référence OUT et la marquer "O" (colonne 15 de l'ArrC)

Ou par tout autre moyen... (ça dépend du nombre de création prévisible / jour) s'il n'y en a qu'une ou 2 par semaine on peut éventuelement envisager une inscription directe...

Ayant collé TOUSSA dans ta feuille de synthèse, tu peux ensuite t'amuser à la filtrer sur la colonne O pour disposer tes différents critères autrement dans la Recap....

Hum... C'est clair ou je recommence ?

A+

Hello,

Avant tout merci pour la réponse: C'est vrai que pour moi c'est évident puisque je suis tous les jours dessus, mais effectivement, l'EAN est l’identifiant unique du produit.

Le problème est effectivement le nombre de changement pouvant arriver dans la semaine (création, mais aussi modification des anciens produits, et de ce fait notre fichier étant très long à mettre à jour, d'où le souhait de l'automatiser au max.

Je vais essayer de commencer quelque chose avec tes remarques et reviens vers toi.

Merci

Il peut y avoir simultanément

Changement de prix et Changement SAP ?

Oui, un produit peut avoir plusieurs changement en même temps.

Autre info : Le fichier final sera composé d'environ 10k-15k lignes.

Dans quelles colonnes peut-il y avoir des #N/A ?

J'ai également un problème avec les changement de St et de Type

Il faut développer un peu l'utilisation de ces colonnes car pour l'instant c'est nébuleux.

Idéalement il faudrait que dans ton fichier exemple la Recap soit solutionnée avec tous les cas possibles.

A+

Normalement les #N/A peuvent apparaître dans les colonnes A, B, C, E, G

En essayant de travailler sur un macro je me demandais s'il n'était pas plus simple de find et replace tous les #N/A par TBC pour ensuite travailler sur tous les TBC. A savoir qu'aucune cellule ne contient de formule puisque je fais .value = .value sur toutes mes cellules pour éviter des formules trop longues à charger.

Je ne suis pas un formuleux donc ça me viendrait même pas à l'idée... d'ailleurs il me semble que dans ce cas tu aurais des matricielles interminables sur 10k ou 15k lignes.

Les Array me semble une bonne idée mais assez complexe à programmer dans ce contexte.

J'ai également un problème avec les changement de St et de Type

Il faut développer un peu l'utilisation de ces colonnes car pour l'instant c'est nébuleux.

Idéalement il faudrait que dans ton fichier exemple la Recap soit solutionnée avec tous les cas possibles car je ne peux pas programmer ce que je ne vois/comprend pas...

A+

Je m'occupe de créer un exemple concret. En attendant voilà quelques précisions :

- St. : Permet de connaitre le statut du produit : 2 = Produit fermé (n'est pas en vente) / 3 et 5 = Produit ouvert (3 = Ouvert il y a moins de 2 mois, 5 = le reste. Mais ici la distinction n'est pas importante)

- Type : Le type correspond à la catégorie du produit (vendu seul, en colis, etc...). Cette information permet de savoir où chercher le produit par la suite (le Récap est envoyé par mail en fin de macro)

- J'utilise les formules sur 3 colonnes afin de faire des RechercheV et consolider toutes les données sur un document. Une fois les données trouvés, j'utilise .Value = .Value pour supprimer les liens et éviter les ralentissements sur le fichier.

Re,

J'ai essayé de rajouter des couleurs pour faciliter la compréhension de l'exemple.

Si ce n'est pas clair, merci de me dire

Dois-je m'occuper des colonnes H L J L et N ou puis-je les écraser dans mon Array (gain de temps)

Si NON :

Dois-je les intégrer dans les contrôles d'erreurs ?

Bonjour,

Ces informations doivent apparaître dans les onglets J et J-1 mais ne sont pas dans Récap (elles n'ont pas l'obligation d'être vérifiées).

Merci encore pour ton aide

bonjour

salut galopin01

n'est-il pas temps de passer à Power BI ?

avec extraction et concaténation/comparaison des données avec Power Query

puis présentations sous forme de graphiques et/ou de tableaux interactifs

le mieux pour diffuser sera le cloud.

[jmd]Bonjour,

Je te laisse ce plaisir ! Je ne n'exige pas l'exclusivité quand je répond à un topic...

Tu vas d'ailleurs pouvoir t'y atteler dare-dare car visiblement mon affaire tourne difficilement (En fait je ne sais pas trop passer le cap des erreurs multiples et ignorer le reste de la référence...)

Je cherche mais ça me fait visiblement une usine à gaz à la Franquin et ça me gonfle un peu.

Je serais d'ailleurs curieux de constater à cette occasion si ça vaut vraiment la peine que je me penche sur la question...

A+

arf !

je te laisse volontiers tester PBI

car tu connais bien la problématique maintenant

Bonjour @jmd, Re @galopin01,

Je ne connais pas PBI mais j'ai regardé quelques liens sur le net. Quel langage le logiciel utilise-t-il ? (VBA ?)

Peut-on l'utiliser en plus d'Excel (complémentaire ?) pour garder les options d'Excel (et les macros ?) ?

Merci

re

PBI est un monde qui exploite des données issues de trèèèès nombreuses bases (dont des bases Excel)

pour débuter, nul besoin de langage. Un peu comme pour un TCD dans Excel. Plus tard tu apprendras un peu de DAX (langage de formules) et M (langage de requêtes, mais le GUI peut suffire)

concernant VBA, je déconseille, dans Excel ou dans Word ou PowerPoint. Trop peu stable pour des applications pros tournant sur divers PC hétérogènes.

il n'y a pas de VBA dans PBI.

pour l'analyse de données, essayer PBI c'est l'adopter !

Bon j'ai pas mal avancé, je te donne le fruit de ma réflexion...

Je pense que tous les cas de figure sont solutionnés... sauf le OUT !

Pour le OUT c'est encore un peu nébuleux ton exemple était mal ficelé (je trouve) puisque on ne retrouve pas l'EAN dans le J

Quelques d'exemples incluant exclusivement cette situation seraient pas de refus pour que je puisse tester la condition OUT.

Pour l'instant OUT est ignoré.

Si tu es capable tu modifies et tu insères les conditions de validité dans la macro traitement, sinon tu m'expliques un peu plus (avec exemples à l'appui le cas échéant) et je fignolerai...

La macro se lance à partir du bouton dans la feuille Recap.

A+

[EDIT]

Finalement j'ai fait un OUT comme je le sentais... A tester longuement.

Me dire ce que ça donne sur un vrai fichier... Temps de travail, Erreur rencontrées...

Ton intérêt fait plaisir à voir !

Bonjour,

Je voulais m'excuser pour le non suivi de mon sujet. Un problème de FAI m'a coupé du monde internet pendant 1 semaine (et au passage, le site ne s'affiche pas correctement sur Chrome / Samsung S4 si un dev passe par là).

J'ai eu cependant le temps de coder une macro, moche et très basique, mais elle faisait le travail. Les variables ne sont pas toutes déclarées dans le module puisque pour certaines je le fais en public pour les utiliser plusieurs fois.

Sub comparmono()
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim rngLook As Range: Set rngLook = wsMONO2.Range("D:N")
Dim cellNum As Range

Dim i As Integer
Dim j As Integer
Dim EAN As Variant
Dim EAN2 As Variant
Dim SAP As Variant
Dim SAP2 As Variant
Dim PRIX As Variant
Dim PRIX2 As Variant
Dim STAT As Variant
Dim STAT2 As Variant
Dim rows1 As Integer
Dim rows2 As Integer

Dim lrSAP As Integer
Dim lrPRIX As Integer
Dim lrOUT As Integer
Dim lrIN As Integer
Dim lrERROR As Integer

  rows1 = wsMono.Cells(Rows.Count, "D").End(xlUp).Row
  rows2 = wsMONO2.Cells(Rows.Count, "D").End(xlUp).Row

With wsRecap
    For i = 1 To rows1
    lrSAP = .Cells(Rows.Count, "S").End(xlUp).Row
    lrPRIX = .Cells(Rows.Count, "M").End(xlUp).Row
    lrOUT = .Cells(Rows.Count, "G").End(xlUp).Row
    lrIN = .Cells(Rows.Count, "A").End(xlUp).Row
    lrERROR = .Cells(Rows.Count, "X").End(xlUp).Row

        With wsMono
            EAN = .Cells(i, "D").Value
            SAP = .Cells(i, "E").Value
            PRIX = .Cells(i, "G").Value
            STAT = .Cells(i, "K").Value
        End With
        On Error Resume Next
        'EAN2 = wsFunc.VLookup(EAN,rngLook,
        SAP2 = wsFunc.VLookup(EAN, rngLook, 2, False)
        PRIX2 = wsFunc.VLookup(EAN, rngLook, 4, False)
        STAT2 = wsFunc.VLookup(EAN, rngLook, 8, False)

        'If SI ERREUR RECHERCHEV ALORS ERRORHANDLER (SI BLANC OU OUT) INSCRIRE LIGNE OUT

        If SAP <> SAP2 Then
            .Cells(lrSAP + 1, 19).Formula = "=Today()"
            .Cells(lrSAP + 1, 20).Value = EAN
            .Cells(lrSAP + 1, 21).Value = SAP
            .Cells(lrSAP + 1, 22).Value = SAP2
        End If
        If PRIX <> PRIX2 Then
            .Cells(lrPRIX + 1, 13).Formula = "=Today()"
            .Cells(lrPRIX + 1, 14).Value = EAN
            .Cells(lrPRIX + 1, 15).Value = SAP
            .Cells(lrPRIX + 1, 16).Value = PRIX
            .Cells(lrPRIX + 1, 17).Value = PRIX2
        End If
        If STAT <> 2 And STAT2 = 2 Then
            .Cells(lrIN + 1, 1).Formula = "=Today()"
            .Cells(lrIN + 1, 2).Value = EAN
            .Cells(lrIN + 1, 3).Value = SAP
            .Cells(lrIN + 1, 4).Value = "NOM"
            .Cells(lrIN + 1, 5).Value = "TYPE"
        End If

    Next i
End With
End Sub

J'ai commencé à réflechir à un moyen de jouer avec les out, pour le moment je suis sur l'idée de faire un Errorhandler sur ma fonction recherchev et ensuite voir si c'est un TBC ou #N/A.

Je vais prendre le temps de regarder la macro que tu as proposé pour essayer de la comprendre (notamment le fonctionnement des tableaux) puisque pas mal des choses que tu as proposé sont nouvelles pour moi.

Merci