VBA - mettre à jour une liste de référence d'un fichier dans d'autres

Bonjour chers experts,

Je me heurte à une situation épineuse et j'aimerai beaucoup pouvoir compter sur votre expertise svp. Voici la situation :

J'ai un fichier que j'extrait d'un système informatique de ma société et qui correspond à un listing des produits. il y a plus de 1000 articles. C'est le fichier de base.

A coté, j'ai plusieurs fichiers qui représentent des fichiers d'analyse de stocks, de ventes, d'achats, etc... Ce sont mes fichiers cibles.

A l'heure actuelle, à chaque nouvelle création de produits, je dois aller dans chaque fichier cible et ajouter manuellement les nouvelle références. Cela peut entrainer des oublis et prend en plus beaucoup de temps. Je souhaiterai une macro qui, quand j'ouvre un des fichiers cibles, recherche dans le fichier de base les références manquantes et les ajoute dans le fichier cible.

Sachant que la référence article est toujours en colonne A (à la fois dans le fichier de base et dans les fichiers cible) si cela peut aider. C'est ma clé de répartition.

Pouvez-vous m'aider svp?

Merci d'avance

Cordialement,

Bonjour

Aujourd'hui cela se fait très simplement via une requête PowerQuery qui récupère automatiquement la base à jour dans chaque fichier où elle est installée, à l'ouverture de celui-ci ...

Mais si tu as la version 2007 datant de 16 ans, tu ne peux l'utiliser : il est disponible en add on sur 2010 et 2013, et intégré à Excel depuis la version 2016

Sinon effectivement il faut coder en VBA

Bonjour et merci pour votre réponse.

En fait j'ai la version office 365 mais je n'ai jamais utilisé PowerQuery. Pourriez-vous m'expliquer comment faire la requête svp?

Cordialement,

Bonjour

Alors remplace 2007 par 365 dans ton profil

Dans chaque fichier cible :

• supprime Feuil2
• Données, Obtenir des données, à partir d'un Fichier, A partir d'un classeur Excel : choisir le classeur recherche-articles.xlsx
• Sélectionner Feuil1
• Charger

Tu obtiens un tableau structuré avec le contenu de la feuil1 de recherche-articles.xlsx

Données, Requêtes et connexions : affiche le volet des requêtes. Clic droit sur la requête, Propriétés, cocher Actualiser les données à l'ouverture du fichier

Si le chemin n'est pas le même pour chacun on peut utiliser une cellule nommée pour indiquer le chemin mais il faut alors faire des modifs sur la requête.

Bonjour,

Merci beaucoup, cela marche super bien pour l'ajout de référence. C'est un gros souci de réglé car plus de risque d'oubli de référence.

Malheureusement, cela m'a créé un autre souci où j'aimerai compter sur votre aide svp.

Pour entrer des objectifs de ventes annuelles manuellement, je classe les articles par fournisseurs, par marques, je fais des filtres, etc.. pour pouvoir les regrouper et prendre des décisions. quand j'enregistre mon fichier et que je l'ouvre de nouveau, les données manuelles saisies partent à l'endroit où devrait être la référence dans le fichier de base mais la mise en page du fichier cible reste comme elle était quand je l'ai enregistré, ce qui fait que mes données sont toutes mélangées et intravaillables.

Peut-être un petit exemple pour être plus clair :

J'ai en colonne A dans mon fichier de base des références article de 1 à 1000 listées en ordre croissant. Dans mon fichier cible, je classe les référence en mode décroissant pour avoir en 1er la référence article 1000. Je met mon objectif (par exemple 10), je sauvegarde et je ferme le fichier. Quand je le réouvre, il fait bien la liaison avec mon fichier de base mais mon objectif de 10 qui doit être sur la référence article 1000 part en tout dernière ligne du fichier cible, soit sur la référence 1 (puisque le fichier cible est toujours classé en ordre décroissant).

J'ai décoché ce matin dans les propriétés des données externe la case "Conserver la disposition/filtre/tri des colonnes" mais cela n'a rien changé au problème.

Auriez-vous une solution svp?

Merci beaucoup

Cordialement,

Bonjour

Il faut indiquer l'ordre de tri voulu dans la requête...

Donc éditer la requête et ajouter un tri en fin...

Ce qui est fait a postériori dans Excel sur le tableau résultant n'est pas mémorisé...

Bonjour,

Le problème c'est que mes tris et mes filtres changent selon le moment où je veux les faire et ce que je souhaite faire. Cela change constamment.

Le fait d'être sûr d'avoir toutes les références de mon fichier de base dans mes fichiers cibles est top mais je dois pouvoir continuer à travailler sur mes fichiers cibles et ajouter des données à la suite manuelle.

Je pensais n'avoir pas trouvé une option de mise en page ou quelque chose dans le genre. Il n'y a vraiment aucune solution pour cela? Car mes données manuelles sont bien en lien avec une référence article, je trouve cela dommage que cela ne la garde pas en mémoire.

Cordialement,

RE

Tu fais ce que tu veux dans ton fichier d'articles sans que cela ait une incidence dans les fichiers cibles : le requête récupère soit dans l'ordre initial soit selon un tri défini dans la requête une fois pour toute.

C'est dans la base de référence que tu peux ajouter des article spas dans une copie sinon chacun aura une base différente... ce qui n'était pas ta demande

Je ne comprends pas ce que tu veux de plus...

Ton exemple fichier-cible est d'ailleurs peu compréhensible avec 2 onglets identiques...

Normalement on a une référence dans un onglet et un fichier de travail utilisant la référence dans un autre onglet...

Bonjour,

Navré, j'ai dû mal exprimer mon problème. Pour illustrer, je vous mets deux nouveaux fichiers (Recherche-article & Fichier cible) avec la requête PowerQuerry configurée.

Je met aussi deux copie d'écran pour illustrer mon problème.

Je n'ai plus de souci d'ajout de référence.

Dans le fichier cible, il reprend le recherche article de la colonne A à la colonne D. Aucun souci de ce côté.

Pour pouvoir mettre mes objectifs annuels, je mets dans mon fichier cible une colonne E "Objectifs annuels" où je vais rentrer manuellement par référence les données. Je fais un tri par fournisseur pour pouvoir avoir une vue plus globale par fournisseur (ceci est pour l'exemple, pour certaines références je vais faire un tri par marque, ou par d'autres éléments qui ne sont pas présents dans l'exemple tels que les ventes N-1, la catégorie ou autre..). Dans cet exemple, je met en colonne E l'objectif de 50 pour la référence article 10. J'enregistre et je ferme mon fichier.

Quand je réouvre le fichier cible, après actualisation de la requête, mon objectif de 50 se retrouve décalée sur la ligne de la référence article 2

Je ne comprends pas pourquoi.

J'espère que cela sera plus clair désormais et que vous pourrez m'aider.

Merci beaucoup

Bonjour

En général on fait ce type de lien pour établir des liens avec la base de référence par des RECHERCHEV ou EQUIV

A aucun moment tu n'as indiqué vouloir ajouter des infos à la base liée.

En l'état il n'y a aucun lien entre ta colonne E et la BDD source notamment l'ID unique référence article...

Si on utilise en cible un seul tableau et qu'on ajoute qu'une ou deux colonnes, les mêmes dans chaque fichier cible, c'est gérable par un self referencing

Il faudrait être plus précis sur le process...

Bonjour,

Mes RECHERCHEV fonctionnent très bien en effet.

J'ai bien précisé que les fichiers cibles étaient de natures variés ("j'ai plusieurs fichiers qui représentent des fichiers d'analyse de stocks, de ventes, d'achats, etc... Ce sont mes fichiers cibles"). Néanmoins, je n'ai pas précisé que certaines de ses données seraient manuelle en effet, je ne pensais pas cela important par rapport à ma problématique initiale.

Le problème venant du tri des données, j'ai utilisé une VBA pour classer en ordre croissant à l'ouverture du fichier cible les références article. Cela semble marcher convenablement pour l'ouverture des fichiers.

Private Sub Workbook_Open()
Dim Derligne As Long
Dim Wsh As Worksheet
For Each Wsh In Worksheets(Array("1", "2"))
With Wsh
Derligne = .Range("A65536").End(xlUp).Row
.Range("A1:E" & Derligne).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Next Wsh
End Sub

Cela ne marche pas par contre dès que j'actualise les données. J'ai décoché l'actualisation de la requête en arrière-plan et en appuyant sur le bouton "actualiser tout" et cela règle le souci. C'est un peu du bricolage donc si vous avez une meilleure solution je suis preneur.

Cordialement,

Bonjour

Pas sûr que ce tri suffise

la bonne pratique serait de saisir les codes article, ajouter des colonnes (RECHERCHEV ou saisie), et croiser ce tableau avec le résultat de la requête.

Mais comme tu simplifies l'exemple difficile de se faire une idée.

A noter que sur un tableau structuré on n'utilise pas les vieilles méthodes VBA des plages

On travaille avec le listobject dont excel connait la position, la taille,... et qu'il sait donc filtrer plus simplement...

Bonjour,

Merci pour ta réponse.

Cela a l'air de marcher du moment que les nouvelles références sont mises à la fin dans le fichier Recherche Article et qu'il n'y a pas de tri dans ce fichier. Cela reprend bien l'ordre des données manuelles dans mes fichiers cibles. Il faut également que les nouvelles références articles aient une référence supérieure à la dernière de la liste. C'est un peu contraignant donc mais gérable.

Tu veux dire par là de faire un tableau à part pour mes données saisies manuellement? C'est malheureusement impossible car j'aurais alors 12 tableaux (1 par mois) et on reviendrait à ma problématique initiale qui est d'ajouter les nouvelles références du fichier Recherche article automatiquement pour éviter les doublons et oublis.

Je vais essayer comme tu suggères listobject. Je ne sais pas encore comment je vais le faire mais je vais étudier un peu comment cela fonctionne. Je reviens pour vous tenir au courant.

Cordialement,

Rebonjour,

J'ai cherché comment utiliser listobject mais j'ai du mal à comprendre. J'ai trouvé sur Internet ce code qui me parait cohérent mais je ne sais pas comment l'appliquer pour tous les tableaux du classeur et a l'ouverture du fichier:

Sub TriSimpleDuTableau()

Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields.Clear
Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields.Add _
    Key:=Range("Tableau1[[#All],[Ventes]]"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Pouvez-vous m'aider svp?

Bonjour,

Après une multitude de tests, malheureusement chez moi (office 375) la solution marche mais chez mon collegue qui utilise aussi le fichier (Office 2021), les données manuelles continuent de se décaler.

J'ai utiliser la macro suivante :

Private Sub Workbook_Open()

ActiveWorkbook.Worksheets("Stats Vtes Forecast").ListObjects("FORECAST").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Stats Vtes Forecast").ListObjects("FORECAST").Sort.SortFields. _
        Add Key:=Range("FORECAST[[#All],[REF ARTICLES]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending
        ActiveWorkbook.Worksheets("Stats Vtes Forecast").ListObjects("FORECAST").Sort.Apply

End Sub

Est-ce un problème de compatibilité EXCEL ?

Est-ce qu'une autre solution est envisageable que Power Query et revenir à ma problématique initiale qui est "Quand j'ouvre un FICHIER CIBLE, on recherche si toutes les références du fichier RECHERCHES ARTICLES sont présent dans les différents onglets. Si non, on les ajoute"?

Cordialement,