XL 2016 Macros sur nom de fichier variable

[Anto35200]

Bonjour,
Dans le fichier Excel « Tréso » le bouton macro Exécution, ouvre le fichier « Extraction 28.07.2023» et filtre à la feuille « 28.07.2023 » les colonnes suivantes pour pouvoir effectuer une somme à la colonne M .

Colonne G : Nom de la société, Filtre sur NISSAN

Colonne H : Enlève la sélection BABE et BABR

Colonne J : sélectionne BNP



Cependant, je rencontre 2 problèmes :

1/ Le nom du fichier Extraction est variable, c’est-à-dire à la fin du nom, il y a une date qui est variable, c’est la date de l’extraction du fichier

2/ le nombre de ligne dans le fichier extraction est aussi variable en fonction des jours.

Ici dans l’exemple le total est affiché à la cellule M2466.



J’espère être assez clair dans mes explications.



Je vous remercie de votre aide.

 

[fanfan38]

Bonjour
Ci joint ma solution

A+ François

 

[oguruma]

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)

 

[oguruma]

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

 

[oguruma]

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.

​

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....

 

[oguruma]

Bien suite à mon dernier échange, voici les trois solutions que j'ai exposé ce matin.... dont une déjà décoffrée dans le post d'hier.

En 1er lieu, j'ai supprimé le résultat de la requête PQ pour n'en conserver que la connexion. Les données se trouvent dans le modèle de données de PowerPivot. Tous les TCD sont construits à partir de ce dernier.

==> Déjà abordé. J'ai appliqué les filtres comme exprimés dans le demande initiale

Concernant le segment nature il faut activer la multi-sélection pour écarter les natures que tu ne souhaites pas.

==> Ici tu n'as que les totaux via la formule

=LIREDONNEESTABCROISDYNAMIQUE("[Measures].[Somme de Montant TR]";SYNTHESE_1)

SYNTHESE_1 : c'est un nom de champ qui pointe sur

==> c'est plus parlant et ça facilite la maintenance.

Après application des filtres on obtient donc ceci : appréciable et présentable pour une diffusion manageriale ou à copier/coller dans un mail.. Bon aurait pu aller plus et l'intégrer automatiquement. en tant que image (déjà fait dans mon dernier projet...).

===> le titre tu verras se calcule automatiquement selon les sélections effectuées dans les Segments.
Au passage tu verras donc comment on capture l'élément du segment sélectionné soit ==>

===> Dans cet onglet figurent les TCD fondés sur le choix effectué sur un segment et on récupère sa valeur en nommant la cellule puis on l'intègre dans une formule. Ca donne ceci :

Puis dans le tableau des paramètres onglet

==> nommé

puis dans

tu colles la formule

Enfin

TCD Réduit construit à partir du modèle de données se trouvant dans PowerPivot

Les 3 solutions sont fonctionnelles sur n'importe quelle banque, nature et société.

Même si cela répond en partie à tes difficultés tu as déjà de bonnes bases pour les résoudre entièrement avec en prime, une RQ PowerQuery dynamique, un modèle PowerPivot, la mise en place de segments paramétrables au travers desquels tu as l'astuce pour récupérer la valeur d'un élément sélectionné.

Tout ceci peut s'automatiser encore plus via une application "End User" avec envois d'un reporting à la volée selon les critères voulus. Et là nous arrivons dans le monde du BI



Bonne utilisation. Dsl, je n'ai pas les comprimés anti-douleur en stock