XL 2016 Macros sur nom de fichier variable

Bonjour
Ci joint ma solution

A+ François

bsr, à la 1ère lecture de ton tableau Data, je préconise que tu le transforme en Tableau Structuré. Tu en retireras que des bénéfices.
Je présume que c'est un tableau que tu reçois formaté de cette manière. Extraction d'un logiciel de compta ou qq chose du genre.
Solution dans ses grandes lignes :
- je t'invite à te retrancher vers PowerQuery afin d'exploiter ton tableau en entrée (issu de la compta ou autre). En sortie il te produira le tableau structuré. Et dans c'est dans ce fichier résultats (issu de la requête PowerQuery) que tu pourras faire tes calculs de synthèse
- vu l'heure tardive... je n'ai pas creusé plus mais essaie d'orienter ta solution vers un TCD (Tableau croisé dynamique) avant de partir dans le style de Macro comme vu dans le fichier Tréso
- Pour les sélections comme décrites dans ta demandes, l'utilisation des segments me semble plus appropriée
- Enfin, pour conclure la solution finale tournerait avec un mixte de PowerPivot et quelques formules DAX pour obtenir ton sous-total selon tes critères voulus...

- si tu es à l'aise avec PowerQuery, PowerPivot et un peu de VBA tu peux construite une solution pérenne avec le nom de fichier en dynamique. Ca demande néanmoins :
* une analyse plus détaillée des données en entrée et des résultats à produire (le papier et le crayon sont les règles d'or de l'analyse)
* une refonte structurée de la solution

Avec respect tu dois être plus comptable/gestionnaire avec des notions de VBA qu' informaticien ?

Si j'ai un peu de temps... j'essayerai de te donner plus de bribes de solutions en partant de tes tableaux.... pour l'heure c'est un peu tard.
Déjà étape 1 : récupération du tableau de données, l'injectée dans PowerQuery, quelques transformation, et production automatique du tableau structuré. Tout ceci sera paramétrable en cas de changement de nom de fichier. En pwq des requêtes paramétrables... on sait faire.
Etape 2 : Construction des TCD avec les Slicers
Etape 3 : définition des règles de gestion pour les calculs
Etape 4 : finalisation et production du résultat

L'avantage de ce montage.... si on te demande une synthèse sur plusieurs tableaux fusionnés ça sera assez simple. Vu la tête du tableau, c'est ce que je crois comprendre. Autant donc t'y préparer.

ps : le fichier fourni par fanfan --> la macro se déclenche bien...
Attention W10 a revu son système de sécurité concernant les .xlsm importés./downloadés... il faut aller dans les propriétés du fichier (sous windows10) et cocher la case débloquer... je sais c'est casse... pieds (mais ça se résoud via la registry)

Hi, bon voici un début, dis moi si ça correspond à tes difficultés.
Si OUI ça répond à ton besoin, on peut aller plus sur :
- une automatisation complète, sélection de l'extrait des virements, lancement de la RQ PowerQuery qui va charger l'extrait et mettre à jour le modèle de données de PowerPivot.
En effet le TCD est issu de PowerPivot.
Ensuite dans la présentation des résultats on peut orienter la solution vers KPI par banque, nature etc... dénombrement dans un TCD. Pour cela on passe par quelques bonnes formules DAX. Et voiliiii voilààà

La démarche déjà en place
Un tableau de paramètres pour rendre dynamique les noms d'extraits des virements variables.


La requête PWQ
let

// Acquisition des paramètres
PATH=getParameters("TB_PARAMS","DOSSIER_VIREMENTS"),
VIREMENTS=getParameters("TB_PARAMS","FICHIER_VIREMENTS"),
FICHIER=PATH & "\" & VIREMENTS,

Source = Excel.Workbook(File.Contents(FICHIER), null, true),
#"28.07.2023_Sheet" = Source{[Item="28.07.2023",Kind="Sheet"]}[Data],
SUPPR_PREM_LIGNE = Table.Skip(#"28.07.2023_Sheet",1),
PROMOTE_HEAD = Table.PromoteHeaders(SUPPR_PREM_LIGNE, [PromoteAllScalars=true]),
SUUPR_COL_SELCTION = Table.RemoveColumns(PROMOTE_HEAD,{"Selection"}),
CLEAN_UP = Table.TransformColumns(Table.TransformColumnTypes(SUUPR_COL_SELCTION, {{"N° Flux", type text}}, "fr-FR"),{{"N° Flux", Text.Clean, type text}}),
SUPPR_SPACES = Table.TransformColumns(CLEAN_UP,{{"N° Flux", Text.Trim, type text}}),
CONVERSION_NUM_MONTANTS = Table.TransformColumnTypes(SUPPR_SPACES,{{"Montant TR", type number}, {"Debit", type number}, {"Credit", type number}})
in
CONVERSION_NUM_MONTANTS

Elle fait appel à la fonction
déjà publiée sur XLD voir mes Post à ce propos sur Pwq.

(pTable as text, pName as text) =>
let
Source = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
RowsParams = Table.SelectRows(Source, each ([PARAMETRE] = pName)),
value = RowsParams{0}[VALEUR]
in
value

Possible d'ajout un peu de code... (voir mes Post) pour piloter le choix du fichier dans l'arborescence Windows, puis un autre bout de code avec un bouton pour actualiser la requête. Non fait pour l'instant. Mais c'est très très simple. Ainsi, c'est du "End User".

Résultat de l'injection du fichier extraits des virements



Tableau de synthèse avec les filtres et la sommes automatique



et le résultat en fin de tableau



Bon attention si évolution End User comme proposée en préambule c'est du €€€ la journée de consulting 😀 😉

Anto35200 à dit:

Bonsoir ogurama,
Je te remercie pour ta proposition.
En fait, de ce que j'ai besoin au quotidien
c'est de connaître à partir d'une extraction quotidienne le total de la banque BNP pour la société NISSAN;
pour celà, je ne dois exclure dans la colonne I (Nature), les BABE et BABR.

​

Cliquez pour agrandir...

hi, as-tu déjà au départ testé le fichier livré ? car déjà avec les sliciers que j'ai fait plus la construction du modèle de données dans powerpivot ça y répond en partie. On peut se passer le chargement de la rq dans la feuille de calculs et ne garder que la connexion en mémoire car le tcd s'appuie sur la table dans powerpivot.
Pour afficher en un seul coup d'oeil le total de ta sléction ou d'une autre il suffit de construire un KPI en DAX dans power et de l'afficher dans un TCD réduit. Est-ce bien cela ta demande ? ou je pose la question autrement : souhaites-tu avoir le total de ta sélection et les lignes détails ou uniquement le total correspondant à ta sélection ? si nous sommes dans le cas n°1 le fichier livré y répond (enfin je pense)....
Je peux t'ajouter la solution 2 si tu le désires....

il y a aussi la fonction LIREDONNEE... qui permet de récupérer une valeur d'un TCD ça nous évite la formule DAX.... la fonction LIREDONNEE peut être dynamique si par hasard tu souhaite avoir n'importe quelle banque etc....
Connais-tu la fonction LIREDONNEE... j'avoue qu'elle n'est pas simple à manipuler mais elle est très puissante. Surtout ne pas récupérer le résultat du TCD par une formule =$Adresse$De$Cellle$ contenant le resultat. ça c'est une grossière erreur que la majorité fait 😉

Par contre la modélisation et importation du fichier par une RQ powerQuery est plus que recommandée. ça va t'enlever un nombre d'épines du pieds conséquent.
C'est en général à faire quand on reçoit des données d'un logiciel externe.... le fichier fourni n'est pas toujours très propre et un nettoyage est nécessaire. PWQ répond à cette problématique cat il est qualifié d'ETL Extract, Transform and Load 🙂
ou plus je te livre un nouveau fichier avec toutes les solutions que je viens d'évoquer et tu pars au marché panier sous le bras et tu prends la solution qui te convient pour la retravailler à ta guise 😉 ? ok ?
donc dis moi....