Microsoft 365 Transformation et consolidation en VBA

[Amilo]

Bonjour le forum,

Je sollicite svp votre aide pour une solution en VBA,
Je vous mets en pièces jointes les fichiers exemples et les explications ci-dessous :

- J'ai dans un dossier nommé "Dossier 03_2021" comportant plusieurs fichiers avec une table de structure identique
J'ai mis 2 fichiers exemples ("Fichier A.xlsm" et "Fichier B.xlsm" mais il peut en avoir en réalité une dizaine
Tous les mois j'aurai un nouveau dossier avec à l'intérérieur un certain nombre de fichiers
- Dans chaque fichier se trouve une table de données dans un onglet que j'ai nommé dans cet exemple "FA"

Le but est de retenir et d'exporter uniquement les lignes pour lesquelles :
. il y a un montant dans la colonne E ("MT HT")
. Et une référence sous la forme "P349XXXX" dans la colonne A ("IA")

Toutes les lignes à 0 ne sont pas à retenir quelque soit la forme de la référence en colonne A
Il y a parfois des sous-totaaux entre des plages de données qui ne sont pas à prendre en compte (exemple cellule E30 de l'onglet "FA")
Il peut aussi avoir des montants TTC manquants commme en cellule F35,
Cette ligne 35 remplit les critères même si la cellule TTC est vide car il s'agît d'une omission.
Dans tous les cas, un contrôle visuel est effectué pour corriger ce type d'erreur

Vous trouverez également dans l'onglet "Export FA" davantage de précisions avec le résultat souhaité,
Ce dernier présente une version concue avec un onglet dans chaque fichier où j'irais copier/coller manuellement le réultat de chaque fichier dans un fichier de consolidation.

Une autre version imaginée serait d'avoir tous les résultats dans le fichier de consolidation sans créer un onglet intermédiaire "Export"
Le résultat serait obtenu après sélection de la plage à transformer pour chaque fichier à ouvrir.
Mais je pense que c'est beucoup plus compliqué à mettre en ouevre de cette sorte

Je suis bien sûr preneur de toute proposition

En vous remerciant d'avance de vos réponses et de votre aide

Cordialement

 

[yal]

Bonjour
C'est rare de voir un problème aussi clairement posé. Il manque la source des données de la colonne L de la feuille Export FA.
Dans les fichiers sources n'y a t-il qu'une seule feuille? Et si non comment savoir sur quelle feuille sont les données? Dans le dossier source ("Dossier 03_2021") n'y a t-il que les fichiers à traiter? Si non même question que pour les feuilles. Un ordre de grandeur du nombre de ligne dans les feuilles sources?

Une autre version imaginée serait d'avoir tous les résultats dans le fichier de consolidation sans créer un onlet intermédiaire "Export"
Le résultat serait obtenu après sélection de la plage à transformer pour chaque fichier à ouvrir.
Mais je pense que c'est beucoup plus compliqué à mettre en ouevre de cette sorte

Cette partie là est beaucoup moins claire. De ce que je comprends il y aurait un fichier de consolidation par mois. Y aurait il un onglet par fichier du même type que l'onglet Export FA ou tous les résultats seraient ils compilés sur une seule feuille? De quelle sélection parlez vous?

 

[Amilo]

Bonjour @yal,

Je vous remercie de vous intéresser à mon fil et de votre réponse,
Merci aussi pour votre commentaire sympa sur la "clarté" de mon message.

Mais il y a effectivement quelques éclaircissements à apporter notamment sur la dernière partie.

Je répondrai à l'ensemble des vos questions après le boulot, probablement après 20h

Merci

Cordialement

 

[yal]

Pas de souci, j'ai tout mon temps

 

[yal]

Une première proposition à vérifier. Je n'ai pas créer d'onglets transitoires. Je suis allé directement à ce qui m'a semblé le but final.

 

[Hasco]

Bonsoir,

Une tentative (incomplète et imparfaite certainement) par Power Query.
La cellule A1 (nommée 'Dossier') contient le chemin(par formule) vers le sous dossier 'Dossier 03_2021' du répertoire qui contient "Consolidations.xlsm". S'il n'est pas à jour , recalculer la cellule.

La requête traitera tous les fichiers de ce sous-dossier dont le nom commence par 'Fichier ' (espace finale comprise). Pour chaque fichier elle traitera la première feuille dont le nom commence par "F" (majuscule)

Cordialement

 

[Amilo]

Re @yal , bonsoir @Hasco , le forum,

@ yal, merci pour votre première proposition,
J'ai testé votre version actuelle, c'est presque parfait mais il y a un problème notamment lorsque je créé, dans le fichier Source, une nouvelle feuille de données "inutiles" au tratement
Tant qu'il y a un seul onglet, cela fonctionne très bien.

Problème que vous aviez probablement senti venir d'où vos questions pertinentes reprises ci-dessous avec mes réponses :

- Il manque la source des données de la colonne L de la feuille Export FA.

=>En fait il s'agit d'une "colonne calculée" qui fait la comparaison entre les valeurs de la colonne "MT HT" et "MT TTC",
Si le montant de la colonne "MT TTC" est supérieur au montant de la colonne "MT HT" il faudrait alors le code "TT", si c'est égal alors "RG"
Le montant TTC ne peut jamais être inférieur au montant HT, si c'est le cas il s'agirait alors d'une erreur humaine auquel cas il ne faudrait rien mettre
Les montants seront corrigés manuellement lors d'un contrôle de cohérence

Dans les fichiers sources n'y a t-il qu'une seule feuille? Et si non comment savoir sur quelle feuille sont les données?

=>Non il peut avoir d'autres feuilles qui ne serviront pas au but final
Les données seront toujours dans un seul onglet dont le nom de la feuille commencerait par la lettre "F"
Mais pour éviter tout problème avec d'autres feuilles dont le nom par défaut serait "Feuil1", "Feuil2"…etc, on pourrait indiquer que les données sont à récupérer dans la feulle de chaque fichier avec pour nom commençant par les 3 lettres"FKL " (de préférence avec un espace après la 3 lettre)

Dans le dossier source ("Dossier 03_2021") n'y a t-il que les fichiers à traiter? Si non même question que pour les feuilles.

=>Pour information, les noms des fichiers commencent par la première partie du nom de la feulle à traiter à savoir "FKL"
Il y a sinon d'autres fichiers commençant par "FKL " qui ne sont pas à traiter

Un ordre de grandeur du nombre de ligne dans les feuilles sources ?

=>L'ordre de grandeur varie pour chaque fichier entre 1 ligne et env 400 lignes max mais rarement supérieur à 100 lignes (l'éventuelle feuille source avec 400 lignes serait une plage avec des lignes vides ou inutiles au traitement)

Concernant, ma courte explication sur une éventuelle 2ème version :
Celle-ci répondrait justement au problème de savoir quels fichiers sont à traiter

Je pensais aux manipulations suiantes :

- lorsque je clique sur le bouton de commande du fichier "Consolidation", cela m'ouvrirait la boîte de dialogue "Fichier Ouvrir".

- Puis en double cliquant sur le fichier souhaité cela irait copier le résultat de la transformation soit dans le même fichier "Consolidation" soit dans un nouveau fichier comme vous l'avez fait

- Je pensais traiter individuellement chaque fichier au fur et à mesure que les fichiers à traiter sont renseignés par les personnes

- Il esr rare d'avoir les mêmes attentes d'une personne à l'autre : certaines auront fini leur fichier le matin, d'autres l'après midi voire le lendemain

- La finalité serait de traiter les fichiers dès qu'ils ont prêts

- Si j'ai déjà 3 fichiers de prêts, j'effecturais 3 fois la manipulation à savoir "Clic sur le bouton de commande puis double clic sur le fichier à traiter dans la fenêtre "Fichier Ouvrir"=> le 1er résultat est copié dans le fichier "Consolidation"

- Idem pour le 2ème fichier à traiter : le résultat viendrait 1 ligne en dessous du 1er résultat…etc

- Sinon, il n'est pas nécessaire de répéter les titres de colonne à chaque résultat à copier.

Désolé pour ce message long comme un "roman", j'espère que c'est compréhensible et clair,

Merci d'avance

Cordialement

 

[Amilo]

Re @Hasco ,

Merci également à vous pour cette proposition Power query,
En fait, j'adore Power query et je pense être capable de "peaufiner" non sans mal une solution complète avec Power query
Cependant VBA m'intéressait davantage même si j'avoue que Power query ferait aussi l'affaire.

Sinon, j'ai testé votre fichier et effectivement le résultat est incomplet mais merci pour votre proposition.
Si je devais me lancer sous Power query, j'aurais déjà de quoi m'inspirer ou bien je posterais mes questions si besoin.

Merci encore

Bonne soirée

Cordialement

 

[yal]

Nouvelle version avec prise en compte de vos remarques. Dites moi si j'ai bien compris et ce qu'il faudrait changer ou améliorer.

 

[Amilo]

Bonjour @yal , le forum,

Sacré boulot, merci beaucoup pour cette nouvelle version, cela semble parfait,
Vous avez également ajouté des explications et certaines optimisations avec des informations très utiles, c'est effectivement une bonne une idée et je vous en remercie,

J'aurais cependant, une modification que je n'avais pas anticipée et j'en suis désolé :

Dans la colonne "J" du fichier "Consolidation", j'avais souhaité le compte "51200" mais en réalité le numéro à indiquer se trouve dans la colonne "B" de chaque fichier "Source" à traiter.

Par exemple, dans "Fichier A1" apparaît la valeur 460000 en bas de la colonne B,
J'aurais souhaité cette valeur en lieu et place de 51200 dans le fichier "Consolidation"

Pour information, les fichiers ne contiennent pas toujours la même valeur mais cette dernière se trouve toujours en regard du dernier montant de la colonne "E"
Il n'est pas impossible aussi qu'il y ait du texte en dessous de cette valeur de la colonne "B".
Ce texte est sans lien avec le fichier "Consolidation"

Merci encore pour votre soutien

Cordialement

 

[yal]

S'il n'y a rien en dessous du dernier montant de la colonne E Alors voici la version corrigée.

 

[Amilo]

@yal ,

Impeccable, un grand merci pour votre patience et tout ce très bon travail, c'est au delà de mes attentes
Vous êtes plutôt "nouveau" sur ce site (inscription depuis nov 2019) mais semblez avoir beaucoup d'expérience et de maîtrise en VBA,

Merci encore et bravo

Bon WE si on ne croise plus

Cordialement

 

[yal]

Content de vous avoir rendu service.
J'ai c'est vrai une certaine maitrise de VBA et je suis heureux de pouvoir rendre service même si j'aimerais bien pouvoir monétiser un peu mes compétences.
Au plaisir et bon WE

 

[Amilo]

Re, @yal , le forum,

J'ai commencé à tester le gros travail de yal sur mes fichiers réels et 1ère petite déconvennue,
En copiant l'ensemble du dossier sur un autre PC, je reçois le message en capture d'écran ci-dessous
Il m'est signalé une erreur sur la ligne du code suivante :

If Dir(chM & "Consolidations", 16) = ""

Puis en déplaçant tout le dossier plus près de la racine "C:", il n'y a pas de souci et le fichier fonctionne très bien.
Voici un aperçu de la différence entre les 2 chemins :

Emplacement qui fonctionne : "C:\Users\Etude\Test_2022
Emplacement qui pose problème : ""C:\Users\OneDrive - SA\Etude\Test_2022

Je suppose que c'est le tiret après OneDrive qui génère le problème mais je ne sais pas trop à quel endroit corriger.
Autre question svp, le dossier final se trouvera en réalité sur Sharpoint, j'ignore encore si ça posera un problème ou non. Mais je testerai également par la suite

Merci d'avance

Cordialement

 

[yal]

J'ai fais un certain nombre de tests. Il y a effectivement un problème selon l'emplacement mais ça ne semble pas venir du code. Mes tests ne donnent pas strictement les mêmes résultats que chez vous.
Je dois m'absenter. Je m'y remettrai demain.